How to Detect & Resolve SQL Server Deadlocks

Prerequisites

To follow along with this tutorial, you’ll need:

A solid understanding of SQL.
Access to a compatible SQL Server instance suitable for development.
The WideWorldImportersDW database downloaded. You’ll use this to simulate a deadlock.

Exploring deadlocks

To avoid and resolve SQL Server deadlocks, you need to know the different types of deadlocks and how they happen. The following section reviews several deadlocks you may encounter and walks you through detecting them in SQL Server.

Order of operations deadlock

An order of operation deadlock is when two processes hold a lock that the other requires.

Say you have a Customer table, an Orders table, and processes A and B. Process A has an exclusive lock on the Customer table and requests a lock on the Orders table. Process B has an exclusive lock on the Orders table and requests an exclusive lock on the Customer table.

Neither can continue until both processes release a lock before completing. The SQL Server must terminate one of them.

Consider the following example that uses the WideWorldImportersDW database to simulate an order of operations deadlock.

In SQL Server Management Studio (SSMS), create a new query comprising two update statements with a 20-second delay between them:

BEGIN TRANSACTION 
UPDATE WideWorldImporters.Sales.Customers 
SET CustomerName = 'Jane' 
WHERE CustomerID = 1 
WAITFOR DELAY '00:00:20' 
UPDATE WideWorldImporters.Purchasing.Suppliers 
SET FaxNumber = N'555-12123' 
WHERE SupplierID = 1 
COMMIT TRANSACTION

The query first updates the Customers table, waits 20 seconds, then tries to update the Suppliers table.

Now, create a second query in a different window. This query has two update statements with a 20-second delay between them:

BEGIN TRANSACTION 
UPDATE WideWorldImporters.Purchasing.Suppliers 
SET FaxNumber = N'555-1212' 
WHERE SupplierID = 1 
WAITFOR DELAY '00:00:20' 
UPDATE WideWorldImporters.Sales.Customers 
SET CustomerName = 'Mary' 
WHERE CustomerID = 1 
COMMIT TRANSACTION

The second query updates the Suppliers table first, waits for 20 seconds, then updates the Customers table. The difference between the first transaction and the second is the order of execution.

To create the deadlock, execute both queries in quick succession.

When you run the first query, the transaction acquires a lock on the Customers table and then starts waiting. When you run the second query, that transaction acquires a lock on the Suppliers table.

When the 20-second delay from the first query elapses, the first transaction requests a lock on the Suppliers table, which the second transaction already has. Likewise, when the second query’s delay elapses, it requests a lock on the Customers table, which the first transaction already has.

The two transactions wait for each other until the SQL Server finally terminates one.

Lookup deadlock

A lookup deadlock occurs when a process needs to look up a value on a row that another process has a lock on. Usually, the deadlock occurs between a SELECT statement and an UPDATE, INSERT, or DELETE statement. The SQL server usually terminates the SELECT statement as it uses fewer resources.

Consider these two queries:

Query 1

USE WideWorldImporters; 
declare @quantity int; 
set @quantity = (select Quantity from sales.OrderLines 
where OrderLineID = 231390); 
while 1=1 
  begin 
    UPDATE WideWorldImporters.Sales.OrderLines 
    SET [Quantity] = @quantity 
    WHERE OrderLineID = 231390; 
    set @quantity = @quantity + 1; 
end

Query 2

USE WideWorldImporters; 
while 1=1 
  begin 
    SELECT OrderLineID, Quantity 
    FROM Sales.OrderLines 
    WHERE UnitPrice = 0; 
end

The OrderLines table has an OrderLineID clustered index and an OrderLineID non-clustered index.

The SELECT statement takes a shared lock on the OrderLineID non-clustered index. Then, it requests a shared lock on the clustered index to look up the row.

At the same time, the UPDATE statement takes an exclusive lock on the clustered index. Since it also has to update the non-clustered index, it requests an exclusive lock.

The deadlock occurs because the UPDATE locks the clustered index while the SELECT locks the non-clustered index, and they each need the other's lock to complete.

Parallelism deadlock

Parallelism deadlocks occur when query plans run in parallel mode. When a single process runs in multiple threads, those threads may block each other, leading to a deadlock.

Consider the following query running against a large database:

SELECT * customer_id, customer.name 
INTO CustDetails 
FROM Customer;

The SQL Server may execute it in parallel mode to speed up the process, which means the query runs on multiple threads. After some time, these threads may start blocking each other and cause the query to deadlock itself.

Parallelism deadlocks are an SQL Server issue and are difficult to predict. However, you may be able to solve them by setting the query’s MAXDOP to 1. This forces the SQL Server to execute the query in a serial plan, preventing a parallel deadlock.

Alternatively, you can use OPTION (FORCE ORDER), which forces the query optimizer to use your specified order of joins.

How to detect SQL Server deadlocks

As deadlocks negatively affect database performance, it’s important to monitor the database regularly. Here are some of the methods for detecting deadlocks in an SQL Server.

system_health

system_health is an Extended Events session that records system data. It’s included in SSMS by default, so you don’t need to configure or start it to use it.

The system data it collects includes information on deadlocks and the deadlock graph. You can use the Extended Events user interface or Transact-SQL (TSQL) to view the data collected by system_health.

To view data collected by system_health using TSQL, use this query:

SELECT XEvent.query('(event/data/value/deadlock)[1]') AS DeadlockGraph 
FROM ( 
  SELECT XEvent.query('.') AS XEvent 
  FROM ( 
   SELECT CAST(target_data AS XML) AS TargetData 
   FROM sys.dm_xe_session_targets st 
    INNER JOIN sys.dm_xe_sessions s 
ON s.address = st.event_session_address 
 WHERE s.NAME = 'system_health' 
 AND st.target_name = 'ring_buffer' 
  ) AS Data 
CROSS APPLY TargetData.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData(XEvent)

Third-party monitoring tools

There are also third-party monitoring tools for detecting deadlocks. These include the deadlock victims, the SQL Server resources involved, the processes that had or tried to obtain a lock on these resources, and the lock types. All this information helps you identify and troubleshoot the query statements that the SQL Server was executing when the deadlock occurred.

Detecting deadlocks

Let’s return to the order of operations deadlock example with a Customers and Suppliers table and two queries representing two sessions. How would you detect this deadlock?

Let’s monitor deadlocks in SSMS using system_health. As mentioned, you can also use the Extended Events user interface or TSQL.

To view the data using the Extended Events user interface, click on the Extended Events > Sessions node in the left navigation panel.

Fig. 1: Expand the Management node to access system_health

Next, right-click on package0.event_file
to view the data.

Fig. 2: A table of the deadlock reports

Click on the process you want to investigate — it will show up in the Details section.

Fig. 3: The deadlock report in the details section

Double-click on the process to open the XML report.

Fig. 4: XML Deadlock Report

You can also click the deadlock tab in the Details section to view the deadlock graph.

Fig. 5: A deadlock graph

From this graph, you can deduce what caused the deadlock. In this example, there are two processes: server process ID 74 (SPID74) and server process ID 79 (SPID79). The blue X shows that the SQL Server chose the SID74 process as the deadlock victim.

SPID74 had an exclusive lock (X) on the Suppliers table but requested an update lock (U) on the Customers table. Simultaneously, SPID79 had an exclusive lock on the Customers table but requested an exclusive lock on the Suppliers table. Each process ends up waiting for the other one.

To prevent this deadlock, use an UPDATE lock. For an update to occur, an operation must first read a row and then write it. During the reading phase, the UPDATE lock is converted to a SHARED lock, and during the writing phase, it’s converted to an EXCLUSIVE lock. Other transactions can still request a SHARED lock on a resource with an UPDATE lock. However, they must wait until this transaction completes to request an EXCLUSIVE lock.

Modify the first query in the example to use the UPDLOCK hint as follows:

BEGIN TRANSACTION 
SELECT SupplierID 
FROM WideWorldImporters.Purchasing.Suppliers WITH (UPDLOCK) 
WHERE SupplierID=1 

UPDATE WideWorldImporters.Sales.Customers 
SET CustomerName = 'Jane' 
WHERE CustomerID = 1 
WAITFOR DELAY '00:00:20' 

UPDATE WideWorldImporters.Purchasing.Suppliers 
SET FaxNumber = N'555-12123' 
WHERE SupplierID = 1 
COMMIT TRANSACTION

Now, add an UPDLOCK hint to the Suppliers table. This locks the row with SupplierID equal to 1 for the future update statement — the second query in this transaction. When you execute the second transaction, it’ll wait until the first transaction finishes updating the row before it requests a lock.

Best practices for preventing deadlocks

There are several best practices you can use to prevent deadlocks, including those outlined below.

Keep transactions short

Short transactions prevent processes from holding locks on resources for a long time. This minimizes deadlocks since other processes get access to locks sooner.

Use try…catch blocks to handle deadlocks

Try…catch blocks let you catch deadlocks when they occur. When SQL Server chooses a Try…catch block as the deadlock victim, you can simply rerun the transaction.

DEADLOCK_PRIORITY

Set deadlock priority using the DEADLOCK_PRIORITY command. This allows you to select which transaction should be the victim in case of a deadlock. You can set the DEADLOCK_PRIORITY to LOW, NORMAL, HIGH, or a numeric value between -10 and 10.

DEADLOCK_PRIORITY is helpful, but when you have many deadlocks in the database, it’s better to solve the underlying problem. If multiple transactions share the same deadlock priority, the SQL Server decides which transaction to roll back anyway.

Avoid user interaction in transactions

Retrieving user input during query processing reduces the number of simultaneous activities handled by the database. This allows the SQL Server to use most of the resources in query processing, which may prevent deadlocks.

Enable READ_COMMITTED_SNAPSHOT isolation

Switching to READ_COMMITTED_SNAPSHOT isolation prevents reader transactions from blocking or deadlocking modification transactions.

To turn on READ_COMMITTED_SNAPSHOT, follow these steps:

Launch the SQL Server Management System.
Select New Query on the standard bar and add this query:
ALTER DATABASE DatabaseName SET READ_COMMITTED_SNAPSHOT ON
Select Execute from the top menu to run the statement.

For this to work, ensure there are no other open connections to the database.

Finetune MAXDOP settings

Max degree of parallelism (MAXDOP) is a SQL Server configuration option that determines the number of processors used to run a query. By fine-tuning MAXDOP, you can define which queries qualify for parallel execution mode. This can help you avoid parallelism deadlocks.

To configure the MAXDOP value of a database, follow the steps below:

Launch the SQL Server Management System.
Select New Query on the standard bar and add this query:
USE DatabaseName
GO
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = MAXDOP_VALUE;
GO
Select Execute from the top menu to run the statement.

This should set the MAXDOP of the database to the specified value.

Conclusion

Deadlocks prevent two transactions from modifying the same resource concurrently. Therefore, the SQL Server uses deadlocks to maintain the database’s integrity.

You can detect deadlocks using the system_health tool — a built-in tool that uses extended events — or third-party monitoring tools, including SQL Sentry.

When the SQL Server detects deadlocks, it rolls back the least expensive transaction to allow the other transaction to complete. You can also specify which resource to kill by setting the DEADLOCK_PRIORITY. Be careful, as you can end up with multiple transactions sharing the same deadlock priority. Instead, minimize deadlocks by keeping transactions short, using error handling techniques such as try…catch blocks, restricting user interaction in transactions, and switching to READ_COMMITTED_SNAPSHOT isolation.

How to find and fix SQL Server deadlocks