Deadlock Victim in SQL Server

Deadlock Victim in SQL Server

In this article, I am going to discuss how to choose Deadlock Victim in SQL Server with an example. Please read our previous article before proceeding to this article where we discussed when a deadlock occurs in SQL Server with an example. As part of this article, we are going to discuss the following pointers in details

  1. How deadlocks are detected by SQL Server?
  2. What happens when a deadlock is detected?
  3. What is DEADLOCK_PRIORITY?
  4. What are the criteria that SQL Server uses to choose a transaction as a deadlock victim when there is a deadlock occurred?
How deadlocks are detected by SQL Server?

The Lock Monitor thread of SQL Server by default runs in every 5 seconds to detect if there are any deadlocks occurred in the database. If the Lock Monitor thread finds any deadlocks in the database, then the deadlock detection interval will be a drop from 5 seconds to as low as 100 milliseconds depending on the frequency of the deadlocks. If the Lock Monitor thread stops finding deadlocks, then the Database Engine increases the intervals to 5 seconds. 

What happens when a deadlock is detected?

When a deadlock is detected, then the Database Engine ends the deadlock by choosing one of the threads (process or transaction) as the deadlock victim. The transaction which is chosen as the deadlock victim transaction is then rolled back and returns a 1205 error to the application. Rolling back the deadlock victim transaction will release all the locks that are held by the transaction which allows the other transactions to become unblocked and move forward. 

What is DEADLOCK_PRIORITY?

As we already discussed when a deadlock occurs, then by default, SQL Server chooses one of the transactions as the deadlock victim and it will choose that transaction which is least expensive to roll back. However, as a user, we can specify the priority of the transactions in a deadlock situation using the SET DEADLOCK_PRIORITY statement. Once you set the deadlock priority, then the transaction with lowest deadlock priority is chosen as the deadlock victim. 

Example: 

SET DEADLOCK_PRIORITY NORMAL 

DEADLOCK_PRIORITY: 

  1. The default priority is Normal
  2. It can be set to LOW, NORMAL, or HIGH
  3. It can also be set to an integer value in the range of -10 to 10. (LOW: -5, NORMAL: 0,  and HIGH: 5 
What are the deadlock victim selection criteria?

The SQL Server uses the following criteria to select the deadlock victim transaction

  1. If the DEADLOCK_PRIORITY is different, then the transaction with the lowest priority will be selected as the deadlock victim.
  2. If both the transaction having the same priority, then the transaction that is least expensive to rollback is selected as the deadlock victim transaction.
  3. If both the transactions having the same deadlock priority as well as same cost, then SQL Server choose one of the transaction as victim randomly. 
Let us understand this with an example.

We are going to use the following two tables

SQL Server Deadlock Victim Selection

Please use the below SQL Scripts to create and populate the required tables with test data.

-- Create table TableA
CREATE TABLE TableA
(
    ID INT,
    Name NVARCHAR(50)
)
Go

-- Insert some test data
INSERT INTO TableA values (101, 'Anurag')
INSERT INTO TableA values (102, 'Mohanty')
INSERT INTO TableA values (103, 'Pranaya')
INSERT INTO TableA values (104, 'Rout')
INSERT INTO TableA values (105, 'Sambit')
Go

-- Create table TableB
CREATE TABLE TableB
(
    ID INT,
    Name NVARCHAR(50)
)
Go

-- Insert some test data
INSERT INTO TableB values (1001, 'Priyanka')
INSERT INTO TableB values (1002, 'Dewagan')
INSERT INTO TableB values (1003, 'Preety')
Go

First, open 2 instances of SQL Server Management Studio. From the first instance execute the Transaction 1 code and from the second instance execute the Transaction 2 code and here we have not explicitly set the DEADLOCK_PRIORITY, so both the transactions have the default DEADLOCK_PRIORITY which is NORMAL. So in our example, the SQL Server is going to choose Transaction 2 as the deadlock victim transaction as it is the least expensive to rollback. 

Transaction1 Code:

-- Transaction 1
-- Step1 start
BEGIN TRANSACTION
UPDATE TableA Set Name = Name + ' From Transaction 1' 
WHERE Id IN (101, 102, 103, 104, 105)
-- Step2 end

-- From Transaction 2 execute the step 2 Section

-- Step3 Start
UPDATE TableB Set Name = Name + ' From Transaction 1' 
WHERE Id IN (1001, 1002)
-- Step3 End

-- From Transaction 2 execute the Step4 Section

Commit Transaction

Transaction2 Code:

-- Transaction 2
-- Step2 Start
BEGIN TRANSACTION
UPDATE TableB Set Name = Name + ' From Transaction 2' 
WHERE Id IN (1001, 1002)
-- Step2 end

-- From Transaction 1 execute the Step3 Section

-- Step4 Start
UPDATE TableA Set Name = Name + ' From Transaction 2' 
WHERE Id IN (101, 102, 103, 104, 105)
-- Step4 End

-- After a few seconds you will see that this transaction will be chosen as the deadlock
-- victim as it is least expensive to rollback than the transaction 1
Commit Transaction

Here you will notice that the Transaction 2 will be chosen as the deadlock victim and rollback by giving the following error.

SQL Server Deadlock Victim Selection

Let’s back the data to its original state by using the following query.
Truncate table TableB
Truncate table TableA

INSERT INTO TableA values (101, 'Anurag')
INSERT INTO TableA values (102, 'Mohanty')
INSERT INTO TableA values (103, 'Pranaya')
INSERT INTO TableA values (104, 'Rout')
INSERT INTO TableA values (105, 'Sambit')

INSERT INTO TableB values (1001, 'Priyanka')
INSERT INTO TableB values (1002, 'Dewagan')
INSERT INTO TableB values (1003, 'Preety')

Let’s rewrite the same example to use the SET DEADLOCK_PRIORITY of Transaction 2 to HIGH, so that the Transaction 1 will be chosen as the deadlock victim. This is because the Transaction 1 is having the default DEADLOCK_PRIORITY which is Normal and it is lower than the DEADLOCK_PRIORITY of Transaction 2.

-- Transaction 1
-- Step1 start
BEGIN TRANSACTION
UPDATE TableA Set Name = Name + ' From Transaction 1' 
WHERE Id IN (101, 102, 103, 104, 105)
-- Step2 end

-- From Transaction 2 execute the step 2 Section

-- Step3 Start
UPDATE TableB Set Name = Name + ' From Transaction 1' 
WHERE Id IN (1001, 1002)
-- Step3 End

-- From Transaction 2 execute the Step4 Section

Commit Transaction

-- Transaction 2
-- Step2 Start
SET DEADLOCK_PRIORITY HIGH
GO
BEGIN TRANSACTION
UPDATE TableB Set Name = Name + ' From Transaction 2' 
WHERE Id IN (1001, 1002)
-- Step2 end

-- From Transaction 1 execute the Step3 Section

-- Step4 Start
UPDATE TableA Set Name = Name + ' From Transaction 2' 
WHERE Id IN (101, 102, 103, 104, 105)
-- Step4 End

Commit Transaction

Here you will notice that the Transaction 1 will be chosen as the deadlock victim. In the next article, we will discuss Logging deadlocks in SQL Server Error Log.

SUMMARY

In this article, I try to explain the SQL Server Deadlock Victim Selection with examples. I hope this article will help you with your need. I would like to have your feedback. Please post your feedback, question, or comments about this article.

Leave a Reply

Your email address will not be published. Required fields are marked *