A common question of the forums is "How do I stop these deadlocks?" I wrote a small reference article on the SQL Examples Site for the forums for how to trap deadlocks using trace flags and Madhu Nair wrote an article for how to trap them with SQL Profiler:
Troubleshoot Deadlocking in SQL Server.
Troubleshoot Deadlocking in SQL Server 2005 using Profiler.
Most people seem to understand a UPDATE/INSERT, UPDATE/DELETE, INSERT/INSERT, DELETE/DELETE deadlocks pretty well, and the books online cover them pretty good. However, commonly I see where deadlocks occur with a INSERT/SELECT, UPDATE/SELECT, DELETE/SELECT process that is a bit more complex. The following image should provide a bit more explanation as to what is happening with this kind of deadlock.
This is a specific kind of deadlock and is caused by a nonclustered index that forces a key lookup to the clustered index. To demonstrate this kind of structure consider the following:
use tempdb
go
create table KeyLookupDeadlock
(rowid int identity primary key,
firstname varchar(30),
lastname varchar(30),
dateofbirth datetime,
favoritecolor varchar(10))
go
create nonclustered index ix_KeyLookupDeadlock_firstname
on KeyLookupDeadLock (firstname)
go
insert into keylookupdeadlock select 'jon', 'kehayias', '07/29/2008', 'blue'
insert into keylookupdeadlock select 'mickey', 'mouse', '07/28/2008', 'green'
insert into keylookupdeadlock select 'minnie', 'mouse', '07/24/2008', 'yellow'
insert into keylookupdeadlock select 'donald', 'duck', '07/23/2008', 'fusia'
go 150000
insert into keylookupdeadlock select 'jim', 'fisher', '07/28/2008', 'red'
go
alter index all on keylookupdeadlock rebuild
go
This will create a table that has enough rows on most systems to cause a key lookup for the following query:
select firstname, lastname, dateofbirth, favoritecolor
from keylookupdeadlock
where firstname = 'jim'
Keeping in mind that this is an overly simplistic example, what will happen to cause the deadlock is that two SPIDS will start within microseconds of each other, one issuing the SELECT, and the other attempting to INSERT a new Row. The SELECT immediately takes a shared lock on the NonClustered Index and the INSERT immediately takes a Exclusive Lock on the Clustered Index. For the SELECT to complete, it will need to take a shared lock on the clustered index to get the additional columns in the SELECT list, but it will be blocked. At the same time, to complete the INSERT operation will require an Exclusive lock on the nonclustered indexes on the table, which is incompatible with the existing shared lock held by the SELECT operation, so both processes will be blocked waiting on the other to release its lock. This is where the deadlock occurs.
To resolve this, the nonclustered index can be dropped and replaced with a covering index that uses the INCLUDE option to cover the query completely:
create nonclustered index ix_KeyLookupDeadlock_firstname_included
on KeyLookupDeadLock (firstname)
include (lastname, dateofbirth, favoritecolor)
The resulting execution plan shows that the Key Lookup no longer happens, which will revent the deadlock from occuring:
For further information on Lock compatibilities see: