December 17, 2008

Basic SQL Server Deadlock Debugging

This past week I investigated deadlocking  issues in our product.  It was a frustrating experience at times, but I  learned a ton and wanted to pass on some of the stuff I’ve learned so that your experiences with deadlock troubleshooting won’t be as painful.  Deadlocks are a huge topic, so I just want to provide some simple steps for gathering more information about a deadlock and then suggest some possible next-steps for solving the problem.

Note: These steps are for debugging in SQL Server 2005 and SQL Server 2008.

You can look in SQL Server’s error log for more information about a deadlock, but since I could reproduce my deadlocking issue, I used SQL Server Profiler to capture information about the deadlock when it occurred. 

After opening SQL Server Profiler:

  1. Create a new trace, by selecting File->New Trace or hitting Ctrl+N.  Connect to the database server you want to profile with.
  2. The Trace Properties window will appear.  Under the General tab, choose ‘Blank’ for the ‘Use the template’ field.
  3. The Events Selection tab of the Trace Properties window offers a plethora of database events that you can monitor, including several lock and deadlock events.  I only select the Deadlock graph event (in the Locks section) to trace, though, as it provides me plenty of data about the deadlock without overwhelming me with too much information.
  4. After checking the Deadlock graph event, a new tab will appear titled Events Extraction Settings.  In this tab, you can optionally click the ‘Save Deadlock XML events separately’ checkbox.
  5. Press the Run button.

Your trace is now running and will log any deadlocks that occur on the database server you are monitoring.  When a deadlock occurs, a new row will appear in the profiler, as well as a graphical representation of the deadlock at the bottom of the screen.  I think the XML from the Text Data column is more helpful than the deadlock diagram, so let’s check out this data.

  1. Right click on the deadlock row in Profiler and choose ‘Extract Event Data…’ to save the XML to a file.
  2. Open the file you saved with some kind of text editor so that you can look at the raw XML. 
  3. There’s a couple of nodes in the XML that are particularly useful.  Within the <process-list> node there will be <process> nodes that show the processes involved in the deadlock.  There is also a <resource-list> node that has nodes (such as <keylock> and <objectlock>) showing the items that the processes were deadlocked on. 
  4. Extract the following information from each <process> node:
    • The ‘id’ attribute on the <process> node
    • The ‘procname’ attribute on the <process><executionStack><frame> node, which has the name of the stored procedure the process was running during the deadlock
    • The SQL statement(s) found in the <process><executionStack><frame> node, which is the SQL executed during the deadlock
  5. Extract the following information from each lock node (such as <keylock> and <objectlock>) in the <resource-list> node:
    • The ‘objectname’ attribute (and ‘indexname’, if available) on the <keylock> node.  This is the name of the item being locked (and the name of the index being used, if applicable).
    • The ‘id' and ‘mode’ attributes on the <keylock><owner-list><owner> node.  The <owner> node identifies the process that currently has the lock.  The ID references one of the process IDs listed in the <process> nodes, while the mode indicates the type of lock.
    • The ‘id’ and ‘mode’ attributes on the <keylock><waiter-list><waiter> node.  The <waiter> node indentifies the process waiting for a lock.  The ID references one of the process IDs listed in the <process> nodes, while the mode indicates the type of lock.
  6. With the information from steps 4 and 5, you can describe the deadlock situation.  I prefer to draw a simple diagram like the following, which allows me to quickly convey the situation to others:

DeadlockDiagram

In this diagram, stored procedure A has an eXclusive lock on SomeTable table’s SomeTableIndex1 index and is waiting for a Shared lock on AnotherTable table’s AnotherTableIndex2 index, whereas stored procedure B has an eXclusive lock on AnotherTable table’s AnotherTableIndex2 index and is waiting for a Shared lock on SomeTable table’s SomeTableIndex1 index.

With this information you should be able to determine the cause of the deadlock.  Fixing deadlocks, though, can sometimes be difficult.  However, before attempting to implement some sort of fix, try running each of the queries involved in the deadlock through SQL Server’s Database Engine Tuning Advisor, as the Tuning Advisor might recommend some new indexes for the queries.  A lot of deadlocks can be prevented by adding indexes, as the indexes will make queries faster and reduce the amount of time a resource is locked.

To tune a query:

  1. Open SQL Server Management Studio and paste one of the deadlocked queries into a new query window. 
  2. Analyze the query in Database Engine Tuning Advisor by right-clicking and selecting ‘Analyze Query in Database Engine Tuning Advisor’. 
  3. For the ‘Database for workload analysis’ field, select the database that you want to tune with, and select the same database in the ‘Select databases and tables to tune’ section.
  4. Press the Start Analysis button in the top toolbar.
  5. If the Tuning Advisor suggests any indexes, they will appear in the ‘Recommendations’ tab in the ‘Index Recommendations’ section.  To create the indexes, you can either select Actions->Apply Recommendations… to create the indexes immediately, or you can select Actions->Save Recommendations (or hit Ctrl+S) to script the indexes to a file that you can run later.

Adding an index recommended by the Tuning Advisor fixed one of our deadlocking issues.  The other deadlock will require more work, but using the steps I described above gave me the information I needed to determine how to fix it.  For more help, check out Bart Duncan’s excellent three-post series on deadlock troubleshooting.  My Inside Microsoft SQL Server 2000 book also gave me a better understanding of SQL Server locking.

What are some other ways to debug deadlocks?

4 comments:

  1. thanks Tom! this helped me out alot. - Mat (detroit)

    ReplyDelete
  2. This is awesome. Thank you!!!

    -Mar (Clemson)

    ReplyDelete
  3. This is very informative and useful to troubleshoot deadlocks. Thank you!

    - Venkatesh

    ReplyDelete
  4. Very usefull and not to much blabla. Thanks!

    ReplyDelete