Thanks for the Help with Deadlocks

This is a copy of a blog post I put on my SQLServerCentral blog site “Adventures with Data”. ┬áReally have to figure out that syndication thing someday.

For a time there a few weeks ago (been meaning to post this for a while now), I saw more deadlocks than I’ve seen in my entire career prior to those odd weeks. Every day we had a few of them, and being unfamiliar with dealing with them, I had to do some research on diagnosing them. I found some great info out there that I wanted to summarize here. Many thanks to the SQL Server community for taking the time to put this stuff out there.

As I started Googling deadlocks, one of the first articles I latched onto was “The Difficulty with Deadlocks” by Jeremiah Peschka. As I find that the Brent Ozar Unlimited site is always a good place to find clear and concise articles, I started there. This led me to the referenced article by Jonathan Kehayias “Retrieving Deadlock Graphs with SQL Server 2008 Extended Events”, containing some very useful code to extract out the deadlock graph in XML format for all the deadlocks that had happened over the last few days.

Now, having the XML representation of the deadlock graph is one thing, interpreting it is another. Bart Duncan’s series on deadlocks (referenced by Jonathan’s article) was very helpful in this regard, and things started to make sense. But what was this Sch-M lock we were seeing? Turns out the Sch-M is a “schema modification lock”, and I have to agree with Michael J. Swart’s article, schema modification locks are indeed evil. They are essentially incompatible with every other lock mode, and as this additionally useful article (and code) by Dmitri Korotkevitch indicates, “deadlock possibilities are endless” with them in the mix.

We were getting these schema modification locks while our foreign key constraints were being set to “not trusted” by our bulk insert code (SqlBulkCopy), and while we were trying to rectify this by reapplying the constraints WITH CHECK. Full circle to the Brent Ozar Unlimited site, here’s a nice article on how easy it is to have your constraints get untrusted, and why you want to address this.

So, after mostly backing out some code changes we had made recently, we got our deadlocks under control. For the record, the situation was a bit more complicated than I’ve indicated here, and included a connection auto-promoted to the serializable isolation level, some recently applied ON DELETE CASCADE constraints, and some manipulation of the indexes. All of which, though, we could diagnose after being able to obtain and decipher (to a fashion, they still aren’t obvious!) the deadlock graphs through extended events.

Many thanks to you all for time and effort you put in to provide the useful information in all your posts. Below is a summary of the links I found useful, in case you find yourself suddenly in need to get up to speed on deadlocks like I did. I additionally want to recommend Kalen Delaney’s excellent eBook “SQL Server Concurrency”, available for free off the Red Gate site, for learning more about locking, blocking, and deadlocks.

eBooks:
Kalen Delaney’s book on SQL Server Concurrency
http://www.red-gate.com/products/dba/sql-monitor/entrypage/sql-server-concurrency-ebook

Links:
http://www.brentozar.com/archive/2011/07/difficulty-deadlocks
(led to the below article)
http://www.sqlservercentral.com/articles/deadlock/65658/
(led to the below article)
http://blogs.msdn.com/b/bartd/archive/2006/09/09/deadlock-troubleshooting_2c00_-part-1.aspx
(saw Sch-M locks and found)
http://michaeljswart.com/2013/04/the-sch-m-lock-is-evil/
http://aboutsqlserver.com/2012/04/05/locking-in-microsoft-sql-server-part-13-schema-locks/

MSDN Lock Modes Descriptions
http://msdn.microsoft.com/en-us/library/ms175519(v=sql.105).aspx

This entry was posted in SQL Server. Bookmark the permalink.