Deadlock Detection and Analysis

Deadlock detection and analysis in SQL Server is important for the overall health of affected applications. This post shows how to setup an Extended Events session to capture deadlock details. It also provides a stored procedure to capture details from the Extended Events session. The stored procedure enables simplified permanent storage of those deadlock detection events. Next, we’ll set up a SQL Server Agent Job to run the stored procedure on an ongoing basis. Finally, we’ll see several examples of how to query the captured events. These queries support making the necessary changes to both the application and database design.

Deadlocks in SQL Server aren't quite this bad.

Deadlocks in SQL Server aren’t quite this bad. Allegory of death by Juan de Valdés Leal

Lets define what a SQL Server Deadlock is. We’ll also see why it’s important to mitigate their causes.

Background

So, what is a deadlock? A deadlock happens when two processes are competing for the same resources in such a way that neither session can ever complete. SQL Server’s sophisticated deadlock detection algorithm will quickly choose one of the deadlocked sessions for termination. This allows SQL Server to resolve the deadlock, allowing the other session to complete. The terminated session is rolled-back, and the following error is returned to the client:

Msg 1205, Level 13
Transaction (Process ID) was deadlocked on resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

The “cheapest to rollback” process involved in the deadlock is chosen as the deadlock victim. “Cheapest” is based on the number of log bytes written, but can be overridden with the SET DEADLOCK_PRIORITY statement.

Client software that is not well-written may not properly handle deadlock errors. For example, perhaps it doesn’t retry the deadlocked transaction, and either returns an error to the user, or just silently continues. This can leave the data in a state that is not consistent with business objectives. Clearly the far better option would be to eliminate the causes of deadlocks.

Required Configuration Change

Detecting when deadlocks occur is the first step towards mitigation. Once detected, we’d want to provide those detection details to Database Administrators for remediation. The first step is to enable the “blocked process threshold” system configuration option. This code performs that change:

Creating the Extended Event session

Next, we create the Extended Events session which gathers XML data from SQL Server whenever a deadlock is detected:

Creating the dba_admin Database

This code will create a dba_admin database to store the captured deadlock events. You may want to modify the CREATE DATABASE statement to include path details for the data and log files. That would allow you to specify the maximum file sizes and file growth increments.

Creating the Stored Procedure

Next we’ll create the dbo.GatherDeadlockEvents stored procedure. This stored procedure captures deadlock detection events from the Extended Events session. It saves the events in the dba_admin database. It uses the Extended Events timestamp and the killed process ID to only capture deadlock events from the ring buffer that haven’t previously been captured. There is a very small potential to miss events if they occur extremely quickly, and have the same process ID.

This code runs the stored procedure once in order to create the dbo.deadlock_xml_events table.

Creating a SQL Server Agent Job

Here, we’re creating a SQL Server Agent Job to run the dbo.GatherDeadlockEvents stored procedure once every 5 minutes.

Sample Analysis Code

The following sample code surfaces details about the actions involved in deadlock events.

The sample analysis code above shows the names of procedures and code involved in deadlock events saved in the dbo.deadlock_xml_events table. For an example deadlock, and sample output for the analysis queries above, see example deadlock code.

Check out this article on Microsoft Docs about deadlocks. Although the document is targeted at systems running Microsoft Dynamics AX, the details included can be invaluable in understanding and mitigating the causes of deadlocks.

Take a look at the rest of our articles about troubleshooting SQL Server here. You may also be interested in some of our other tools.

Ads by Google, Paying the Rent: