It's time to start blogging again. And for my first post in a very long time, I mainly want to thank the SQL Server community for putting so much valuable information out there. Recently we had a rare deadlock appear, and it reminded me of a previous encounter I had with recurring deadlocks, and the research I did to diagnose them. Below is the blog entry I wrote at the time to summarize my research, but never posted. I guess I'm less shy than I was before, so here is the post -- hope you find it helpful.
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 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)
MSDN Lock Modes Descriptions
http://msdn.microsoft.com/en-us/library/ms175519(v=sql.105).aspx
