SQL SERVER – Setting Deadlock Priority to Control the Transaction that is Rolled Back

Deadlocks in SQL Server can be a significant challenge for database administrators and developers. When two or more transactions compete for resources, it can lead to a deadlock situation where the system has to choose which transaction to roll back. In some cases, it is desirable to control this behavior and prioritize specific transactions over others. In this article, we will explore Setting Deadlock Priority to Control the Rolled Back Transaction.

Here are my previous blog posts on this topic; I recommend you read them before you start on the example mentioned in this blog post.

• Understanding SQL Server Deadlocks: A Beginner’s Guide
• Detecting Deadlocks in SQL Server Using Trace Flags 1204 and 1222
• SQL SERVER – Resolving Deadlock by Accessing Objects in the Same Order
• SQL SERVER – Locking, Blocking, and Deadlocking: Differences, Similarities, and Best Practices
• SQL Server Deadlocks – Interview Q and A

Understanding Deadlock Scenarios

To illustrate the concept, let’s consider a scenario where we have two tables: ProductSold and UpdatedInventory. Both tables have a common column ProductID and represent different aspects of a product management system. The sample data and transactions are as follows:

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);

Session 1: (run one statement from this session and one from session 2)

-- 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: (run one statement from this session and one from session 1)

-- 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;

In this scenario, both sessions try to update the same row in different tables but in reverse order. This leads to a deadlock situation where each session is waiting for the other to release the locks. The SQL Server engine will detect the deadlock and choose one session as the victim to be rolled back.

Controlling Deadlock Priority

To control which transaction gets rolled back in the event of a deadlock, we can set the deadlock priority for each transaction. The transaction with the higher priority will have a better chance of surviving the deadlock. Let’s explore how to set the deadlock priority in this example.

Setting Deadlock Priority

In Session 1, we can set the deadlock priority for Transaction A to LOW, indicating that it is less important compared to Transaction B:

SET DEADLOCK_PRIORITY LOW;

You can just put the above statement before the BEGIN TRAN command.

In Session 2, we can set the deadlock priority for Transaction B to HIGH, indicating that it is more important compared to Transaction A:

SET DEADLOCK_PRIORITY HIGH;

You can just put the above statement before the BEGIN TRAN command.

By setting different deadlock priorities, we can influence the outcome of the deadlock resolution process.

Analyzing Deadlock Resolution

Now, if we rerun the transactions, we will observe the impact of setting the deadlock priorities.

In the initial scenario without deadlock priorities, the SQL Server engine would choose one of the sessions as the victim, resulting in the rollback of one transaction and the successful execution of the other.

By setting Transaction A’s deadlock priority to LOW and Transaction B’s deadlock priority to HIGH, we are instructing the SQL Server engine to favor Transaction B over Transaction A in the event of a deadlock.

You can run the following command to see the deadlock priority of the current session:

SELECT deadlock_priority
FROM sys.dm_exec_sessions
WHERE session_id = @@SPID

Conclusion

Controlling deadlock priorities in SQL Server provides a mechanism to prioritize transactions during deadlock resolution. By setting different deadlock priorities for transactions, you can influence which transaction gets rolled back and which one successfully completes. However, it’s important to note that deadlock priorities are relative and can be overridden by other factors, such as locks held by other sessions with higher priorities.

Managing deadlocks is crucial for maintaining database performance and avoiding disruptions. Understanding deadlock resolution mechanisms and utilizing deadlock priorities can help you optimize your SQL Server applications and ensure the smooth execution of critical transactions.

You can always reach out to me with the following questions, and if you want me to build a video for SQL in the Sixty Seconds Series, do let me know.

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