SQL Deadlock Priority to Control Transaction Roll Backs

By: Sergey Gigoyan | Updated: 2022-11-30 | Comments | Related: > Locking and Blocking

Problem

SQL Server Deadlocks happen when two sessions are waiting on each other to lock resources. In other words, sessions are mutually blocked by each other in the SQL Server database engine resulting in performance issues. SQL Server automatically detects deadlocks, chooses one of these sessions as a victim, and rolls back the transaction, allowing the other session to proceed. SQL Server determines the victim session based on its estimations and picks up the less costly transaction to roll back. However, sometimes DBAs and Developers have their priorities and want a specific transaction to proceed in case a deadlock arises.

In SQL Server, it is possible to manage this by setting the deadlock priority. This article will discuss how to prioritize transactions by setting the deadlock priority. To get more information about deadlocks, it is recommended to read the following tip before continuing: Monitor Deadlocks in SQL Server with system_health Extended Events.

Solution

The SQL Server SET DEADLOCK_PRIORITY statement defines the deadlock priority for the current session. If a deadlock occurs and other sessions in the deadlock chain have lower priorities, the current session will not be chosen as a deadlock victim and will proceed. If the sessions have equal priorities, SQL Server chooses the victim based on its estimations.

The DEADLOCK_PRIORITY can be set to LOW, NORMAL, and HIGH. For more flexibility, it is possible to assign numeric values as well. The numeric values can be from -10 to 10, which supports 21 levels of the deadlock priority. The greater the number, the higher the priority. The LOW priority matches the numeric value -5, NORMAL 0, and HIGH 5.

Now, it's time to practice and see how it works. Let's create a test environment and leave the query window open in SQL Server Management Studio (SSMS) to keep the global temporary tables during the whole practice session:

--Two global temp tables with sample data
CREATE TABLE ##TableA (
    ID INT IDENTITY,
    Val CHAR(1)
)
GO
 
INSERT INTO ##TableA (Val)
VALUES ('A'), ('B')
GO
 
CREATE TABLE ##TableB(
        ID INT IDENTITY,
        Val CHAR(1)
)
GO
 
INSERT INTO ##TableB (Val)
VALUES ('C'), ('D')
GO

Then, open two query windows and paste the T-SQL queries below into the corresponding windows. It is recommended to make these windows vertical for better visibility. Paste the first set of code into the first query window:

-- code for query window 1
BEGIN TRANSACTION

SELECT @@SPID AS ProcessID

--1
UPDATE ##TableA
SET Val = 'E'
WHERE ID = 1
------------------------------------
WAITFOR DELAY '00:00:07'

--3
UPDATE ##TableB
SET Val= N'G'
WHERE ID = 1
-------------------------------------------------------------

COMMIT

SELECT Val, GETDATE() AS CompletionTime FROM ##TableA WHERE ID=1
SELECT Val, GETDATE() AS CompletionTime FROM ##TableB WHERE ID=1

Paste the second set of code into the second query window:

-- code for query window 2
BEGIN TRANSACTION
 
SELECT @@SPID AS ProcessID
 
--2
UPDATE ##TableB
SET Val = N'F'
WHERE ID = 1
--------------------------------------
WAITFOR DELAY '00:00:07'
 
--4
UPDATE ##TableA
SET Val = N'H'
WHERE ID = 1
 
COMMIT
 
SELECT Val, GETDATE() AS CompletionTime FROM ##TableA WHERE ID=1
SELECT Val, GETDATE() AS CompletionTime FROM ##TableB WHERE ID=1

Run the first query and then run the second query immediately after that.

Deadlock after running 1st and 2nd queries

As we can see in the screenshot above, a deadlock occurs as both transactions try to update the same resource in the opposite order. The first transaction is chosen as a deadlock victim and rolled back in this case. The second one succeeded. As we haven't set the deadlock priority, SQL Server decides which transaction to roll back.

If we continue the process more than one time, it is possible that we can see the opposite situation. For example, in our next run, the first transaction succeeded, and the second one failed:

Setting DEADLOCK_PRIORITY

What if we want the second transaction to succeed any time when it is involved in a deadlock? To do that, we can set its deadlock priority to HIGH using the SET DEADLOCK_PRIORITY HIGH command.

We will set the deadlock priority for the first transaction to LOW (we could even leave it as is as the default priority is NORMAL, which is lower than HIGH). Now, let's modify the first query and add the deadlock priority command.

Paste the first set of T-SQL code into the first query window:

SET DEADLOCK_PRIORITY LOW
GO
 
-- code for query window 1
BEGIN TRANSACTION
 
SELECT @@SPID AS ProcessID
 
--1
UPDATE ##TableA
SET Val = 'E'
WHERE ID = 1
------------------------------------
WAITFOR DELAY '00:00:07'
 
--3
UPDATE ##TableB
SET Val= N'G'
WHERE ID = 1
-------------------------------------------------------------
 
COMMIT
 
SELECT Val, GETDATE() AS CompletionTime FROM ##TableA WHERE ID=1
SELECT Val, GETDATE() AS CompletionTime FROM ##TableB WHERE ID=1

We will modify the second query as well and set the deadlock priority to HIGH.

Paste the second set of T-SQL code into the second query window:

SET DEADLOCK_PRIORITY HIGH
GO
 
-- code for query window 2
BEGIN TRANSACTION
 
SELECT @@SPID AS ProcessID
 
--2
UPDATE ##TableB
SET Val = N'F'
WHERE ID = 1
--------------------------------------
WAITFOR DELAY '00:00:07'
 
--4
UPDATE ##TableA
SET Val = N'H'
WHERE ID = 1
 
COMMIT
 
SELECT Val, GETDATE() AS CompletionTime FROM ##TableA WHERE ID=1
SELECT Val, GETDATE() AS CompletionTime FROM ##TableB WHERE ID=1

If we repeat the test we did before, we can see that the first transaction is rolled back and the second one succeeds:

We can repeat the test as many times as we want, and we can see that the second one always succeeds and the first one is rolled back.

Note: This configuration will guarantee that the second session will not be chosen as a victim if involved in a deadlock with the first session. However, if it is involved in a deadlock with other sessions with HIGH priority, it may be chosen as a victim. If it encounters a deadlock with a session with a priority higher than HIGH (the priority number is greater than 5), it will be rolled back.

Conclusion

To sum up, it is possible to control the deadlock priority in SQL Server by setting the DEADLOCK_PRIORITY. This will be useful if some sessions are considered more important, where developers prefer these sessions to proceed in case of deadlocks rather than leaving SQL Server to choose the victim session.

Next Steps

For additional information, please follow the links below:

About the author

Sergey Gigoyan is a database professional with more than 10 years of experience, with a focus on database design, development, performance tuning, optimization, high availability, BI and DW design.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2022-11-30

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