There are quite a few ways to capture and troubleshoot deadlock errors. Personally I use the trace flag 1222 to capture deadlocks and manually analyze the trace results. The ultimate resource on deciphering the 1222 output is Bart Duncan’s blog. Please read this four part series. This will provide a lot of insights in troubleshooting deadlocks.

With SQL Server 2008, you can collect deadlock information using the system_health default event session in Extended Events. The most interesting thing about this is that you can collect the deadlock details after the fact. Jonathan Kehayias, probably the best expert you can find in the topic of extended events and a champion in deadlock troubleshooting wrote about using extended events and few other methods to capture and troubleshoot deadlock errors in his book Troubleshooting SQL server – A Guide for the Accidental DBA. He also discusses about some common deadlocks and how to troubleshoot them. You can read the chapter on deadlocks here.

Also see Amit Banerjee’s post on the retrieving deadlock data from the System Health event session  here. He also blogged about how you can use Powershell and TSQL to create individual deadlock graphs from System health event here.

Regardless of the method you sue to capture and analyze the deadlocks, you will need to arrive at something like below to understand the resources and processes involved in the deadlock to fix the deadlock issue.

The above table will give you an idea about the locks that are in granted and waiting state and the resources associated with it. The traces will only provide you the statements that are in the waiting state. You will need to analyze the batch to see what statement actually acquired the locks. In many cases you will also need to look at the execution plan to understand why the locks are being held.

We will follow this method to analyze the deadlocks we are going to discuss.

This post is part of the series SQL Server deadlocks and live locks – Common Patterns. If you have questions, please leave a comment here or reach me at @sqlindian