Understanding SQL Server Deadlocks: A Beginner’s Guide

In my consulting engagements Comprehensive Database Performance Health Check, I often encounter clients seeking assistance in resolving deadlocks. Understanding the frustration and impact of deadlocks on their systems, I want to empower individuals with the knowledge to tackle this issue on their own. In this blog post, I will guide you through the steps to resolve deadlocks yourself. However, if you find yourself needing further support or have any questions after reading this, don’t hesitate to reach out to me. I’m here to help you overcome deadlocks and optimize your system’s performance.

Introduction to Deadlock

A deadlock occurs when two or more tasks permanently block each other by each task having a lock on a resource that the other tasks are trying to lock. This situation creates a cyclic dependency between the tasks, and unless resolved by an external process, both transactions will wait indefinitely. SQL Server Database Engine uses a deadlock monitor to detect and resolve deadlocks by choosing a victim and terminating its transaction with an error, allowing the other transaction to complete.

Deadlocks are often confused with normal blocking, which occurs when a transaction requests a lock on a resource held by another transaction. While deadlocks are resolved quickly, blocking can persist indefinitely.

Deadlock in Real-World Scenarios

Let’s illustrate a simple deadlock scenario using a ProductSold and UpdatedInventory example. Imagine two transactions occurring simultaneously: one for selling a product and another for updating the inventory.

• Transaction A sells a product and acquires a shared lock on the ProductSold table.
• Transaction B updates the inventory and acquires a shared lock on the UpdatedInventory table.
• Transaction A now requests an exclusive lock on the UpdatedInventory table but is blocked until Transaction B releases its shared lock.
• Transaction B requests an exclusive lock on the ProductSold table but is blocked until Transaction A releases its shared lock.

In this scenario, Transaction A cannot complete until Transaction B completes, and vice versa. This cyclic dependency results in a deadlock.

Translating the Scenario into T-SQL

Let’s create two tables representing the ProductSold and UpdatedInventory tables with some sample data:

CREATE TABLE ProductSold
(
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(50),
    QuantitySold INT
);
--
INSERT INTO ProductSold (ProductID, ProductName, QuantitySold)
VALUES (1, 'Product A', 10),
       (2, 'Product B', 20),
       (3, 'Product C', 30);
--
CREATE TABLE UpdatedInventory
(
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(50),
    QuantityInStock INT
);
--
INSERT INTO UpdatedInventory (ProductID, ProductName, QuantityInStock)
VALUES (1, 'Product A', 100),
       (2, 'Product B', 200),
       (3, 'Product C', 300);

Next, we can create a deadlock by running one statement each from Session 1 and Session 2. Here is the T-SQL for the same.

Session 1: This represents Transaction A, which updates the ProductSold table and then attempts to update the UpdatedInventory table:

-- Session 1
BEGIN TRAN;
--
-- Transaction A acquires a shared lock
UPDATE ProductSold
SET QuantitySold = QuantitySold + 1
WHERE ProductID = 1;
--
-- Transaction A requests an exclusive lock
UPDATE UpdatedInventory
SET QuantityInStock = QuantityInStock - 1
WHERE ProductID = 1;

Session 2: This represents Transaction B, which updates the UpdatedInventory table and then attempts to update the ProductSold table:

-- Session 2
BEGIN TRAN;
--
-- Transaction B acquires a shared lock on
UPDATE UpdatedInventory
SET QuantityInStock = QuantityInStock - 10
WHERE ProductID = 1;
--
-- Transaction B requests an exclusive lock
UPDATE ProductSold
SET QuantitySold = QuantitySold + 10
WHERE ProductID = 1;

Here’s the illustration of the deadlock scenario using the two sessions:

Session 1                   | Session 2
===========================================================
BEGIN TRAN;                 | BEGIN TRAN;
===========================================================
-- Transaction A acquires a shared lock
UPDATE ProductSold
SET QuantitySold = QuantitySold + 1
WHERE ProductID = 1;
===========================================================
                             | -- Transaction B acquires a shared lock
                             | UPDATE UpdatedInventory
                             | SET QuantityInStock = QuantityInStock - 10
                             | WHERE ProductID = 2;
===========================================================
-- Transaction A requests an exclusive lock on
UPDATE UpdatedInventory
SET QuantityInStock = QuantityInStock - 1
WHERE ProductID = 1;
===========================================================
                             | -- Transaction B requests an exclusive lock on
                             | UPDATE ProductSold
                             | SET QuantitySold = QuantitySold + 10
                             | WHERE ProductID = 2;
===========================================================
                             | BLOCKED
===========================================================

In this example, Transaction A (Session 1) acquires a shared lock on the ProductSold table and then requests an exclusive lock on the UpdatedInventory table, becoming blocked. Meanwhile, Transaction B (Session 2) acquires a shared lock on the UpdatedInventory table and then requests an exclusive lock on the ProductSold table, also becoming blocked. This situation creates a deadlock, as both transactions are waiting for the other to release their respective locks.

Deadlock Detection Algorithm

The SQL Server Database Engine has a clever way of handling deadlocks, which are situations where multiple transactions are waiting for each other and unable to proceed.

The SQL Server Database Engine uses a special thread called the lock monitor to detect deadlocks. It regularly checks all the tasks in the database to see if any deadlocks have occurred. Here are the key things you should know about this process:

• By default, the lock monitor checks for deadlocks every 5 seconds.
• If it detects deadlocks, the time between checks is reduced to as little as 100 milliseconds, depending on how frequently deadlocks are happening.
• Once deadlocks stop occurring, the interval between checks gradually goes back to the default 5 seconds.
• After a deadlock is detected, the system assumes that subsequent lock waits could be part of a new deadlock. So, the first few locks waiting after a deadlock are immediately checked instead of waiting for the next interval. This helps in detecting deadlocks faster.

When a deadlock is confirmed, the SQL Server Database Engine needs to decide which transaction should be terminated to resolve the deadlock. This is called the deadlock victim. Here’s how it handles that:

• The engine selects the transaction with the least impact to be the deadlock victim. It rolls back that transaction, releasing all the locks it held.
• The application receives a 1205 error message to indicate that a deadlock has occurred.
• This approach allows other transactions to continue without being affected by the deadlock.

By default, the SQL Server Database Engine determines the deadlock victim based on the cost of the transaction. However, users can influence this decision by setting a priority for each session. The priority can be set to LOW, NORMAL, or HIGH, or any integer value between -10 and 10. Here’s how the selection works:

• The session with the lowest priority is usually chosen as the deadlock victim.
• If multiple sessions have the same priority, the one with the least costly transaction is selected.
• If multiple sessions have the same cost, a random victim is chosen.
• This flexibility allows users to have more control over which transactions are more likely to be chosen as the deadlock victim when a deadlock occurs.

I hope this explanation makes the deadlock detection and resolution process clearer way.

Identifying Deadlocks Using Extended Events

Since the release of SQL Server 2012, I have been using Extended Event for detecting the deadlocks so we will be only including that method in the blog post. SQL Server 2012 and later versions come with the system_health session enabled by default. This session captures all xml_deadlock_report xEvents containing deadlock graphs whenever deadlocks occur. You don’t need to set up a separate xEvent session to collect this information. It’s already being captured for you.

The captured deadlock graph consists of the following main components:

1. Nodes and Edges: Think of the graph as a visual representation of the deadlock scenario. The circles represent processes, and the rectangles represent resources. The arrows show the relationships between them. If an arrow points from a process to a resource, it means the process is waiting for that resource. If it points from a resource to a process, it means the process already holds that resource.
2. Victim-list: This part of the graph highlights the process that has been chosen as the deadlock victim. It’s the process that will be terminated to resolve the deadlock. Its transaction will be rolled back, allowing other processes to proceed.
3. Process-list: The process-list provides detailed information about each process involved in the deadlock. It includes the Process ID (SPID), Hostname, Login name, Transaction isolation level, Lock mode requested, and Wait time. Examining this information helps you identify the specific queries and sessions that are part of the deadlock.
4. Resource-list: The resource-list gives information about the resources involved in the deadlock. It specifies the Resource type (e.g., KEY, PAGE, RID), Database ID, Object ID, and the mode in which the resource is held or requested. Analyzing these details helps pinpoint the cause of the deadlock and suggests potential solutions.

When you open the system_health session file or view the ring buffer that contains the xml_deadlock_report xEvent, you can see a graphical representation of the tasks and resources involved in the deadlock. This visualization can be helpful in understanding the deadlock scenario.

To view all the deadlock events captured by the system_health session ring buffer, you can execute the following query:

SELECT xdr.value('@timestamp', 'datetime') AS [Date],
    xdr.query('.') AS [Event_Data]
FROM (SELECT CAST([target_data] AS XML) AS Target_Data
            FROM sys.dm_xe_session_targets AS xt
            INNER JOIN sys.dm_xe_sessions AS xs ON xs.address = xt.event_session_address
            WHERE xs.name = N'system_health'
              AND xt.target_name = N'ring_buffer'
    ) AS XML_Data
CROSS APPLY Target_Data.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') 
AS XEventData(xdr)
ORDER BY [Date] DESC;

Executing this query will provide you with the result that includes the timestamp and the event data for each captured deadlock event. You can review this information to gain insights into the deadlocks.

By leveraging Extended Events and the system_health session, you can easily detect and analyze SQL Server deadlocks without additional configurations. Simply query the captured xEvent data and visualize the deadlock graphs to understand the deadlock scenarios better.

Interpret the Deadlock Graph

Every deadlock graph is different, but let us note down a few generic topics about how we can interpret the deadlock graph.

To interpret a deadlock graph effectively, follow these steps:

1. Identify the processes involved by looking at the process-list.
2. Determine the resources involved by examining the resource-list.
3. Analyze the relationships between the processes and resources shown by the arrows in the graph.
4. Identify the deadlock victim from the victim-list to understand which process was terminated.
5. Use the collected information to determine the root cause of the deadlock and explore possible solutions. This might involve modifying query patterns, adding appropriate indexes, or adjusting transaction isolation levels.

Tips to Minimize Deadlocks

To minimize deadlocks, follow these coding conventions to reduce the chances of encountering deadlocks and improve system performance:

1. Access objects in the same order: When multiple transactions access objects in the same order, deadlocks are less likely to occur. Using stored procedures for data modifications can help standardize the order of accessing objects.
2. Avoid user interaction in transactions: Transactions involving user interaction can slow down the process and delay completion, impacting system throughput. Minimize user input requirements during transactions to prevent unnecessary delays and potential deadlocks.
3. Keep transactions short and in one batch: Long-running transactions increase the likelihood of deadlocks. Keeping transactions short and combining multiple operations into a single batch reduces lock duration and minimizes the possibility of deadlocks.
4. Use a lower isolation level: Consider using a lower isolation level, such as read committed, which reduces locking contention by holding shared locks for a shorter duration compared to higher isolation levels like serializable. Evaluate if a lower isolation level is appropriate for your transaction requirements.
5. Use a row versioning-based isolation level: Enabling the READ_COMMITTED_SNAPSHOT database option or using snapshot isolation leverages row versioning instead of shared locks during read operations. This reduces contention between read and write operations, further minimizing deadlocks.

Remember, in a deadlock scenario, the victim transaction is automatically stopped and rolled back, so there is no need to manually stop a transaction in response to a deadlock. By following these conventions, you can minimize the occurrence of deadlocks, enhance transaction throughput, and optimize system performance.

Well, that’s it for today. There are lots of things we can still discuss about deadlock, but that we will do in some other blog post. If, after following everything, you still face issues with deadlocks or SQL Server performance tuning, you can always engage in Comprehensive Database Performance Health Check and reach out to me on Twitter.

Reference: Pinal Dave (http://blog.SQLAuthority.com)