Understanding Deadlock SQL Server and How to Fix It?

A standoff between two locked processes is known as a deadlock in Microsoft SQL Server. All server action halts at this point as each process waits for the other to release its lock. One of the processes must be stopped as the only remedy.

This practical essay explores the causes, varieties, and remedies of deadlocks in SQL Server.

Exploring deadlocks

You must understand the many forms of deadlocks and how they occur in order to prevent and resolve SQL Server deadlocks. The next part discusses various deadlocks you could experience and shows you how to spot them in SQL Server.

Deadlock in the order of operations

When two processes possess a lock that the other needs, it results in an order of operation deadlock.

Let’s say you have processes A and B, a Customer table, an Orders table, and so on. Process A demands a lock on the Orders table and currently holds an exclusive lock on the Customer table. In addition to requesting an exclusive lock on the Customer table, Process B currently has an exclusive lock on the Orders table.

Until both processes release a lock before finishing, neither can proceed. One of them must be ended by the SQL Server.

Take a look at the sample that follows, which simulates an order of operations deadlock using the WideWorldImportersDW database.

Create a new query in SQL Server Management Studio (SSMS) that consists of two update statements separated by 20 seconds:

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 attempts to update the Suppliers table after updating the Customers table first and waiting 20 seconds.

Create a new query in a new window right now. There are two update statements for this query, separated by 20 seconds:

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 first updates the Suppliers table, then updates the Customers table when 20 seconds have passed. The sequence of execution is what separates the first transaction from the second.

Run both queries quickly one after the other to induce the impasse.

The transaction obtains a lock on the Customers table when the first query is executed, and it then begins waiting. When you execute the second query, the Suppliers table is locked for that operation.

The first transaction demands a lock on the Suppliers table, which the second transaction already owns, after a 20-second delay from the first query. Similar to the first query, the second one asks a lock on the Customers table after its delay expires, even though the first transaction already has one.

When the SQL Server ultimately closes one of the transactions, the other two transactions wait for it.

Lookup deadlock

When a process has to look up a value on a row that another process has a lock on, a lookup deadlock occurs. A SELECT statement and a UPDATE, INSERT, or DELETE statement typically lock the database. The SELECT statement is typically terminated by the SQL server because it consumes less resources.

Think about these two questions:

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

An OrderLineID clustered index and an OrderLineID non-clustered index are both present in the OrderLines table.

The OrderLineID non-clustered index is shared locked by the SELECT statement. Then, in order to search up the record, it makes a shared lock request on the clustered index.

The clustered index is locked only by the UPDATE statement concurrently. It asks for an exclusive lock because it has to update the non-clustered index as well.

Parallelism deadlock

When query plans execute in parallel mode, deadlocks in parallelism happen. A deadlock may result from numerous threads of a single process blocking one another.

Take into account the following query executed against a sizable database:

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

To speed up the process, the SQL Server could run the query in parallel mode, which causes the query to run on many threads. These threads might begin to block one another after a while, which would lead to the query deadlocking.

Deadlocks caused by parallelism are a problem with SQL Server and are unpredictable. However, by setting the query’s MAXDOP to 1, you might be able to resolve them. This prevents a concurrent deadlock by compelling the SQL Server to run the query in a serial plan.

A different option is OPTION (FORCE ORDER), which forces the query optimizer to use the joins in the order you specify.

How to Recognize Deadlocks in SQL Server

Deadlocks have a negative impact on database performance, thus regular database monitoring is crucial. These techniques for finding deadlocks in a SQL Server are listed below.

system_health

System data is logged via the Extended Events session system_health. You don’t need to start it or configure it in order to use it because it comes with SSMS by default.

It gathers information about deadlocks and the deadlock graph as part of the system data. To see the data gathered by system_health, utilize the Extended Events user interface or Transact-SQL (TSQL).

Use this command to view the data system_health has gathered using TSQL:

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

For spotting deadlocks, there are additional third-party monitoring tools available. These consist of the processes that had locks on these resources or attempted to get them, the deadlock victims, the SQL Server resources involved, and the lock types. All of this data aids in the identification and troubleshooting of the query statements that the SQL Server was running at the time of the jam.

Detecting deadlocks

Returning to the example of an order of operations stalemate, we will use a table for customers and suppliers along with two queries to simulate two sessions. How would you recognize this impasse?

Let’s use system_health to monitor deadlocks in the SSMS. As previously noted, you can also employ TSQL or the Extended Events user interface.

Click on the Extended Events > Sessions node in the left navigation panel to examine the data using the Extended Events user interface.

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

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

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

To view the deadlock graph, click the deadlock tab in the Details section.

You can determine the reason for the stalemate by looking at this graph. Server process ID 74 (SPID74) and server process ID 79 (SPID79) are the two processes in this scenario. The SID74 process was selected by the SQL Server as the deadlock victim, as indicated by the blue X.

SPID74 sought an update lock (U) on the Customers table despite having an exclusive lock (X) on the Suppliers table. However, SPID79 asked for an exclusive lock on the Suppliers database while simultaneously holding an exclusive lock on the Customers table. The result is that each process waits for the other one.

Use a UPDATE lock to avoid this stalemate. An operation must read a row before writing it in order for an update to take place. The UPDATE lock is changed from a UPDATE lock to a SHARED lock during the reading phase and from a SHARED lock to an EXCLUSIVE lock during the writing phase. On a resource that has a UPDATE lock, other transactions may still ask for a SHARED lock. But before they may ask for an EXCLUSIVE lock, this transaction must be finished.

Use the UPDLOCK hint to modify the example’s first query 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 update the Suppliers table with a UPDLOCK hint. For the upcoming update statement, the second query in this transaction, which locks the row with SupplierID equal to 1, click the lock button. When you run the second transaction, it will hold off on asking for a lock until the first transaction has finished updating the row.

Best Method to Prevent Deadlock SQL Server

You can avoid deadlocks by employing a number of recommended practices, such as those listed below.

1. Keep transactions short

Short transactions stop processes from keeping long-term locks on resources. Deadlocks are reduced as a result since other processes can access locks earlier.

2. Use try…catch blocks to handle deadlocks

You can break deadlocks with try...catch blocks. You can simply retry the transaction if a Try...catch block is selected by SQL Server as the deadlock victim.

3. DEADLOCK_PRIORITY

The DEADLOCK_PRIORITY command can be used to set the deadlock priority. This enables you to decide which transaction, in the event of a deadlock, should suffer. DEADLOCK_PRIORITY can be set to LOW, NORMAL, HIGH, or a number between -10 and 10.

Although DEADLOCK_PRIORITY is useful, it is preferable to address the root cause of the deadlocks when there are numerous of them in the database. The SQL Server still chooses which transaction to roll back when multiple transactions have the same priority due to a deadlock.

4. Avoid user interaction in transactions

Retrieving user input during query processing limits the amount of concurrent tasks the database must handle. As a result, the SQL Server may process queries while using the majority of its resources, potentially avoiding deadlocks.

5. Enable READ_COMMITTED_SNAPSHOT isolation

When the isolation is changed to READ_COMMITTED_SNAPSHOT, reader transactions cannot stall or obstruct modification transactions.

Use these steps to enable READ_COMMITTED_SNAPSHOT:

• 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.

6. Finetune MAXDOP settings

A SQL Server configuration option called Max Degree of Parallelism (MAXDOP) controls how many processors are used to execute a query. You can specify which queries are eligible for parallel execution mode by adjusting MAXDOP. You can prevent parallelism deadlocks by doing this.

The following steps should be followed to configure a database’s MAXDOP value:

• 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

Two transactions cannot edit the same resource at the same time due to deadlocks. In order to preserve the integrity of the database, the SQL Server employs deadlocks.

The system_health tool, a built-in tool that uses extended events, or third-party monitoring tools, such as SQL Sentry, can be used to find deadlocks.

When the SQL Server notices deadlocks, it rolls back the transaction that was the least expensive so that the other transaction can finish. By specifying the DEADLOCK_PRIORITY, you may additionally specify the resource that should be destroyed. Be cautious because it’s possible for many transactions to share the same stalemate priority. Instead, limit user interaction in transactions, utilize error handling strategies like try...catch blocks, make transactions brief, and switch to READ_COMMITTED_SNAPSHOT isolation to reduce deadlocks.