Goto blog home | Visit my NEW BLOG dedicated to Embedded Programming & IOT
Many of my friends and well wishers have asked me to start writing again. So here is the first one after a long gap of four long years of fire fighting with complex and critical problems for many of my global clients.
I have been pulled into many database deadlock situations in the past and today I decided to write about a generic approach using which one can easily identify and resolve deadlocks in SQL Server databases.
So, what would you do if your customer complaints about severe deadlock issues originating from the SQL server database? First, you will have to setup the SQL Profiler tool in order to capture deadlock event traces (Deadlock Graphs) followed by analyzing the Deadlock Graphs and then implementing an appropriate solution. Remember, the rule of thumb is to minimize the number of table rows being locked exclusively and for the minimum amount of time. The sequence of steps to be taken in order to resolved deadlocks in SQL Server database are listed below:
- Identify deadlock traces (aka. Deadlock Graphs) using SQL Profiler
- Analyze the Deadlock Graphs to identify root causes
- Devise and implement an appropriate solution
Step-1: How to identify the deadlock traces?
Follow the steps listed below in order to setup SQL Server Profiler to listen for each deadlock occurence and dump the details of the contending processes in an xml file. By analyzing the XML file contents you should be able to determine the root cause and fix the deadlocks.
- Launch your SQL Server Management Studio
- Then navigate to the Tools > SQL Server Profiler
- In the SQL Server Profiler screen, click File > New Trace
- You will be prompted to login to a SQL Server Instance
- After logging in to the database to be profiled, in the Trace Properties screen, goto the Events Selection tab.
- In the Events Selection tab, select the "Deadlock Graph", "Lock:Deadlock" and "Lock:Deadlock Chain".
- Then goto the Events Extraction Settings tab and select the option to "Save Deadlock XML events separately".
- That's it. Now start the Profiling session.
- Then start running the problematic area of the application that is prone to generating deadlocks.
- And then check the output folder where the deadlock graphs (.xdl files) would get generated by the profiler.
Step-2: Analyzing the deadlock graphs (.XDL files)
Each deadlock graph, the .xdl files can be double clicked to open them in a SQL Server Management window. Once I got used to it, I found it very comfortable opening them with Internet Explorer and simply perusing through the XML content without the visual representation. However, to begin with let us now see how the deadlock graph looks like and how to interpret the contents. A sample deadlock graph is displayed below.
 |
| Figure 1 - A sample deadlock graph file |
The visual graph above shows two contenders (a victim process and a winner process). The process marked with a blue cross is the victim process that was killed by SQL Server to end the deadlock situation. Whereas the other process is the winner process as it was not killed by SQL Server. Think of each process as a SQL statement that tries to perform some DML on a table row or set of rows. For example, when both processes try to update the same set of rows, usually a deadlock situation arises.
As a next step closely inspect the SQL statements in the victim and the winning processes. Most often the answer is very obvious. Either a missing index needs to be created or the DML statements have to be re-written based on defensive programming principles.
Step-3: Creating an appropriate solution
Some of the time tested techniques of reducing the deadlocks are listed below.
- Apply Missing Indexes
- Decommission Unnecessary Transactions and Highly Restrictive Isolation Levels
- Implement DML Statements with Minimal Locking using defensive programming
Where to applying missing indexes?
Let us consider the below scenario as an example. There are two SQL statements trying to update the Employee table records.
 |
| Figure 2 - Contending DML Statements |
The first update statement tries to update all employee records belonging to DeptId=1 and whose JoiningDate='02-Aug-2015'. Whereas, the second update statement tries to update all employee records belonging to DeptId=2 and whose JoiningDate='03-Aug-2015'.
Let us say there are a total of 1000 records in the Employee table out of which 200 employees have DeptId=1 and 300 employees have DeptId=2.
For the purpose of this explanation, let us assume that the column 'DeptId' does not have an index defined on it. Therefore when the above SQL statements execute sinultaneously, both of them will try to acquire locks on all the 1000 employee records (irrespective of DeptId is 1 or 2). Well that is bad, as this situation may eventually lead to a deadlock between the two DML statements.
In order to reduce the scope of locking and resolve the deadlock the rule of thumb is to remember that row locking uses the indexes of the columns in the WHERE clause, we must add an Index on the "DeptId" column. Once the index is added, all the 1000 records will no longer get locked, instead only subsets of the rows based on their DeptId will get locked