THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

The Rambling DBA: Jonathan Kehayias

The random ramblings and rantings of frazzled SQL Server DBA

Clarification on SQL Server Deadlocks being Unavoidable

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.

Published Thursday, April 30, 2009 6:43 AM by Jonathan Kehayias

Comments

 

Paul White said:

Good post.  Yes - it is one of those statements which provokes emotional respponses!

Sadly, deadlock handling in the real world (just my personal experience) seems to come down to:

1.  The software developers never having the time or inclination to handle 'database' errors such as 1205.  They blame the DBAs for poor database design :sigh:

2.  Fixing the problem with NOLOCK for reads and ROWLOCK for writes.  Double sigh.  

When an organisation is in crisis because of deadlocking and related problems, the senior guys are surprisingly unwilling to embark on a lengthy technical investigation.  The priority is to 'fix it now'.  And quite right too!

So NOLOCK goes in, the deadlocking reduces, and the problem never gets looked at again because "we don't have a problem with deadlocking any more - don't you have any work to do?"

It's tough enough trying to sell them a row-level versioning isolation level.  (Code changes will be required - to remove all the hard coded NOLOCKs!)

:c)

Paul

April 30, 2009 7:24 AM
Anonymous comments are disabled

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement