The Accidental DBA (Day 29 of 30): Troubleshooting Deadlocks

This month the SQLskills team is presenting a series of blog posts aimed at helping Accidental/Junior DBAs ‘keep the SQL Server lights on’. It’s a little taster to let you know what we cover in our Immersion Event for The Accidental/Junior DBA, which we present several times each year. You can find all the other posts in this series at http://www.SQLskills.com/help/AccidentalDBA. Enjoy!

Deadlocks occur in SQL Server whenever two processes attempt to acquire locks on the same resources in reverse order creating a persistently blocked condition, where neither of the sessions can continue to execute until the other session releases its locks.  In SQL Server, the Lock Monitor background task is responsible for detecting and resolving deadlocks whenever they occur (basically terminating the session that has performed the least amount of work so far), and the resulting 1205 error can be a sign of problems that require further evaluation.  In a third-party vendor application, it may not be possible to make the changes necessary to eliminate deadlocks, but you can still collect information about the deadlocks to assist the third-party vendor in analysis and possibly identifying a solution to the problem.

Collecting Information

Prior to SQL Server 2008, collecting deadlock information from SQL Server required enabling a trace flag, configuring a SQL Trace, Event Notifications, or using a WMI Alert.  Trace Flags 1222, 1205, or 1204 write the deadlock information as text into the ERRORLOG.  SQL Trace, Event Notifications and  WMI Alerts allow collection of the deadlock information as XML. Since the introduction of Extended Events and the new system_health event session in SQL Server 2008, deadlock information has been captured by default in SQL Server and no longer requires enabling additional data collection for analysis.

Analysis

The definitive source for understanding the output from trace flag 1222 is a series of blog posts written by Bart Duncan. His three-part series uses the output from trace flag 1222 to demonstrate how to read the XML deadlock graph information, starting with Deadlock Troubleshooting, Part 1. The same method of analysis applies to deadlock graphs collected by SQL Trace, Event Notifications, WMI, and even Extended Events.  The format of the deadlock graph defines the deadlock victim(s), each of the processes involved in the deadlock (within the process-list node), and the resources contributing to the deadlock (within the resource-list node).  The processes each have an assigned processid that is used to uniquely identify each of the processes and the resources being locked or requested by the process in the graph.  Within each of the process’ information, the execution tsql_stack will show the most recently deadlocked statement backwards to the start of the execution call stack.

One of the key areas of focus for deadlock analysis is the resource-list portion of the graph, which contains all the information about the resources involved and the lock types being held and requested by each of the processes.  This will also contain the index and object names, or the allocation unit associated with the object, which can be used to determine the name of the object and index.  Understanding the locking order between the processes is essential for deadlock troubleshooting.

In addition to viewing the raw XML or text information for the deadlock, it is also possible to view the information graphically as explained in my blog post Graphically Viewing Extended Events Deadlock Graphs.  The graphical view in Management Studio will not show all of the same details as the XML or text, but can be a fast start for understanding the type of deadlock and locking order.  It may be necessary to look at the text or XML for further information in some scenarios, or you can also open the graph graphically in SQL Sentry’s excellent Plan Explorer Pro to get the full output parsed as a table as well.

Possible solutions

There are a lot of potential solutions to prevent deadlocks occurring, and the correct one will depend on the specific deadlock condition that is occurring. In some deadlock scenarios an index change to cover one of the queries executing may be all that is necessary to prevent the deadlock condition from being possible.  In other scenarios, it may be necessary to change isolation levels, or use locking hints to force a blocking lock that is incompatible with other locks to prevent the deadlock condition from being encountered.  Proper analysis of the deadlock graph will help with determining the appropriate solution to the problem, but in most cases simple error handling logic in Transact-SQL or .NET application code to handle the 1205 error and attempt to resubmit the victim transaction can prevent end users from being negatively affected by deadlocks occurring.

Summary

Troubleshooting deadlocks in SQL Server starts off with first collecting the deadlock graph information using one of the available methods.  Extended Events in SQL Server 2008 collect the information by default and eliminate the need to enable further collection and then waiting for the deadlocks to reoccur to gather the information.  Full details of how to configure deadlock graph collection and analysis of specific scenarios can be found in my SimpleTalk article Handling Deadlocks in SQL Server and in my Pluralsight online training course SQL Server: Deadlock Analysis and Prevention.

Graphically Viewing Extended Events Deadlock Graphs

I’ve previously blogged about the changes to the xml_deadlock_report in Extended Events to support multi-victim deadlock analysis.  One of the side effects of this change was that the XML document for the deadlock report had to be modified to be able to accommodate multiple victim processes in the graph.  As a result of this, the deadlock graphs that are provided by Extended Events in SQL Server 2008 cannot be saved and opened graphically inside of SQL Server Management Studio.  If you attempt to open an Extended Events deadlock graph you will get an error similar to the following:

image

Failed to initialize deadlock control.
        There is an error in XML document (1, 2).
                <deadlock xmlns=""> was not expected

Until recently there wasn’t anything you could do about this, but today there are two options for viewing the deadlock graph in graphical format; SQL Sentry Plan Explorer Pro and SQL Server 2012 Management Studio.

Plan Explorer Pro

One of the new Pro features that was added in SQL Sentry Plan Explorer at the beginning of November 2012 was the ability to open deadlock graph .xdl files in Plan Explorer.  The display is exactly the same display that you would get from Performance Advisor for deadlocks proactively collected during it’s monitoring, which really simplifies the analysis of the deadlock graph information.  While I was beta testing the changes ahead of the public release, I mentioned that the XML from the deadlock graphs generated by Extended Events had a different output to support multi-victim deadlock analysis, and Brooke (the developer for Plan Explorer at SQL Sentry) asked for an example.  A few hours later I had another beta build that handled the multi-victim deadlock graph format provided by Extended Events which is shown below.

image

SQL Server 2012 Management Studio

I didn’t actually realize this until I was recording the next Insider Demo Video for our SQLskills Insiders Newsletter, but SQL Server 2012 Management Studio has also been updated to handle the XML format output by Extended Events, and it also understands the multi-victim deadlock information.

image

However, if you compare the relative amount of information provided here, even if you use the hover tips on the individual processes, there is still a significant amount of information that is missing.  For example, you don’t get the isolation levels of the processes in the graphical display at all, which can be important for troubleshooting specific types of deadlocks.

If you haven’t given SQL Sentry Plan Explorer Pro a spin, I’d really recommend it.  I had the opportunity to take the next Beta release of Plan Explorer Pro for a spin last night, and there are some really awesome new features coming that will make plan analysis faster and easier than you’ve ever experienced.

SQL Rally Presentation – Deadlocking for Mere Mortals

The first SQL Rally was held last week in Orlando, FL, and I had the honor of being selected for one of the spotlight sessions by the community in the DBA track.  SQL Rally was a different experience from the regular PASS Summit; it wasn’t anywhere as big as the normal summit, but it was larger than most of the SQL Saturday events that I have attended.  If I had to make a comparison, I would say that SQL Rally was more on par with the experience I had attending SQL Bits 7 in York, UK last October, which seems to be right about where PASS wanted the experience to be.  I always enjoy attending events, large or small, where people are passionate about SQL Server. 

Since the event I have had a number of requests for the presentation materials and demos I used in my Deadlocking for Mere Mortals presentation on Friday afternoon.  Below is the session abstract, and attached to this blog post is a copy of the slides and demo’s for the presentation. 

Title:  Deadlocking for Mere Mortals
Speaker: 
Jonathan Kehayias
Category:  Summit Spotlight
Level: 200

Abstract:
While troubleshooting deadlocking in SQL Server has gotten easier in SQL Server 2005 and 2008, it continues to be a constant source of questions in the forums online. This session will look at the most common deadlocks asked about on the forums, and how to troubleshoot them using the various methods available to DBA’s in SQL Server 2005 and 2008; including Trace Flags, SQL Trace, Event Notifications, and Extended Events.

Session Goals

  • Understand why deadlocks occur in SQL Server
  • Understand how to capture deadlock graphs in various SQL Server versions.
  • Understand how to read the deadlock graph to determine the specific cause and how to mitigate against the deadlock.

SQL Rally 2010 – DBA200 – Deadlocking for Mere Mortals.zip (964.07 kb)