Clarification on SQL Server Deadlocks being Unavoidable

jmkehayias — Thu, 30 Apr 2009 09:43:36 GMT

While running through some of my personal emails tonight, I happened across two different questions regarding some statements I have made on forums post in the past, as well as in one of my articles on SQL Server Central:

Even with "proper design" it is still possible to have deadlocking occur which is why it is crucial for applications to properly handle 1205 errors generated by the database engine. If a deadlock occurs, a properly built application should log the occurrence, but also resubmit the deadlocked transaction as a part of handling the error.

For whatever reason, this statement seems to stop being read at:

Even with "proper design" it is still possible to have deadlocking occur

and it is being taken to mean that deadlocks in SQL Server are unavoidable. This is not what this statement is intending to say by far, and I am a firm believer that deadlocks are generally caused by a fundamental design problem that can be fixed. The real problem is that sometimes, fixing the design problem is very complex and could mean restructuring the database, refactoring code, and/or other tasks that are not necessarily feasible to do at the moment. If you find yourself in one of these major redesign scenarios, solving the deadlock immediately may not actually be possible.

Many thanks go out to Louis Davidson who taught me this, or at least made me think about it when I reply/write about deadlocks online, but properly designed applications should have exception handling built into their data access layer which brings us to the remainder of the quote from the article that seems to never get read:

which is why it is crucial for applications to properly handle 1205 errors generated by the database engine. If a deadlock occurs, a properly built application should log the occurrence, but also resubmit the deadlocked transaction as a part of handling the error.

Handling exceptions and errors seems to be second place in development these days, at least based on the questions that get asked on the forums at times. Having written my own applications and tools in the past year, I am not exempt from this problem either. I have only recently begun to put exception management at the front of my application development, and in a hurry, I often still don't do it until something blows up the first time, and I have to go back and correct my shortcoming. The entire point of these two statements is that the application should properly handle the deadlock exception, and attempt to resubmit the transaction a second time before raising a hard error back to the user, and it should then log the error in some manner for follow up or tracking.

Deadlocking seems to be becoming more prevalent based on the trends I see in questions on the forums, which may be partially related to the increasing speed and performance of server hardware, but most often, it is the result of bad design.

The Anatomy of a Deadlock

jmkehayias — Wed, 30 Jul 2008 16:50:00 GMT

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:

Search results matching tags 'Database Administration' and 'Deadlock'

Clarification on SQL Server Deadlocks being Unavoidable

The Anatomy of a Deadlock

Lock Compatibility (Database Engine)