Deadlock in SQL Server

Back to: SQL Server Tutorial For Beginners and Professionals

Deadlock in SQL Server with Examples

In this article, I am going to discuss Deadlock in SQL Server with examples. Please read our previous article where we discussed Concurrency Problems in SQL Server with examples. Before going to understand what is Deadlock in SQL Server, let’s first discuss when a deadlock occurs in SQL Server.

When a deadlock occurs in SQL Server?

A deadlock occurs in a database when two or more processes have already a resource locked, and then each process wants to acquire a lock on the resource that the other process has already locked. In such cases, neither of the processes can move forward, as each process is waiting for the other process to release the lock resulting in a deadlock in SQL Server. If you are confused, then just have a look at the following diagram which explains the above points i.e. when a deadlock occurs in a database.

As shown in the above diagram, we have two processes (process means transactions) such as Process A and Process B. We have two tables such as Table A and Table B in the database. Process A started and it acquires a lock on table A and in the meantime, Process B started and it acquired a lock on Table B. Now to complete its execution, process A needs the resource Table B which is already locked by Process B. At the same time, Process B needs the resource Table A which is already locked by Process A. In such cases, neither of the transactions (processes) can move forward resulting in a deadlock.

When deadlocks occur in SQL Server, then SQL Server chooses one of the processes (transactions) as the deadlock victim and then rolls back that process. As a result, other processes can move forward. The process that is chosen as the deadlock victim will give the following error.

Understanding Deadlock in SQL Server with Examples.

Let us understand the Deadlock in SQL Server with Examples. We are going to use the following two tables to understand deadlock.

Please use the below SQL Script to create and populate the tables with the sample 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

Deadlock Example in SQL Server:

The following 2 transactions will result in a deadlock situation. Open 2 instances of SQL Server Management Studio. From the first instance execute Transaction 1 code and from the second instance execute Transaction 2 code.

Transaction 1 Code:

As you can see in the below code, here we have written the transaction with two update statements. In between the first and second update statements, we have delayed the execution for 15 seconds. The transaction will start its execution and will execute the first update statement i.e. it acquired a lock on the TableA. Then intentionally we delayed the transaction execution for 15 seconds by using WAITFOR DELAY ’00:00:15′ statement and then we try to execute the second update statement i.e. we try to acquire a lock on TableB.

-- Transaction 1
BEGIN TRANSACTION
UPDATE TableA SET Name = 'Anurag From Transaction1' WHERE Id = 101

WAITFOR DELAY '00:00:15'

UPDATE TableB SET Name = 'Priyanka From Transaction1' WHERE Id = 1001
COMMIT TRANSACTION

Transaction 2 Code:

As you can see in the below code, here we have also written the transaction with two update statements. In between the first and second update statements, we have delayed the transaction execution for 15 seconds. The transaction will start its execution and will execute the first update statement i.e. it acquired a lock on TableB. Then intentionally we delayed the transaction execution for 15 seconds by using WAITFOR DELAY ’00:00:15′ statement and then we try to execute the second update statement i.e. we try to acquire a lock on TableA.

-- Transaction 2
BEGIN TRANSACTION
UPDATE TableB SET Name = 'Priyanka From Transaction2' WHERE Id = 1001

WAITFOR DELAY '00:00:15'

UPDATE TableA SET Name = 'Anurag From Transaction2' WHERE Id = 101
Commit Transaction

Now, first, execute the Transaction 1 code and then immediately execute the Transaction 2 code. Once Transaction 1 starts its execution, it acquires a lock on TableA and then waits for 15 seconds, At the same time, Transaction 2 starts its execution, it acquires a lock on TableB and then waits for 15 seconds. After 15 seconds, Transaction 1 wants to acquire a lock on TableB which is already acquired by Transaction 2 and at the same time, Transaction 2 wants to acquire a lock on TableA which is already acquired by Transaction 1. In this situation, neither of the transactions can move forward. So, after some time, You will notice that one of the transactions was completed successfully while the other transaction is chosen as the deadlock victim by giving the following error.

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 in SQL Server?

When a deadlock is detected in SQL Server, 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 in SQL Server?

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 the transaction that is least expensive to roll back by default. However, as a user, we can also specify the priority of the transactions in a deadlock situation using the SET DEADLOCK_PRIORITY statement. Once we set the deadlock priority, then the transaction with the lowest deadlock priority will be chosen as the deadlock victim. 

Example: SET DEADLOCK_PRIORITY NORMAL 

DEADLOCK_PRIORITIES in SQL Server: 

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: -10, NORMAL: 0,  and HIGH: 10 

What are the Deadlock Victim Selection Criteria in SQL Server?

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. When 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 the same cost, then SQL Server chooses one of the transactions as victim randomly. 

Example to understand Deadlock Selection Criteria in SQL Server

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. In our example, the SQL Server is going to choose Transaction 2 as the deadlock victim transaction as it is the least expensive to roll back. 

Transaction1 Code:

As you can see in the below code, here we have written the transaction with two update statements. In between the first and second update statements, we have delayed the execution for 15 seconds. The transaction will start its execution and will execute the first update statement i.e. it acquired a lock on the TableA and further if you notice here we are updating 5 records. Then we delayed the transaction execution for 15 seconds by using WAITFOR DELAY ’00:00:15′ statement and then we try to execute the second update statement i.e. we try to acquire a lock on TableB.

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

WAITFOR DELAY '00:00:15'

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

COMMIT TRANSACTION

Transaction2 Code:

In the below code, we have also written the transaction with two update statements. In between the first and second update statements, we have delayed the transaction execution for 15 seconds. The transaction will start its execution and will execute the first update statement i.e. it acquired a lock on TableB and if you further notice it updates 2 records. Then we delayed the transaction execution for 15 seconds by using WAITFOR DELAY ’00:00:15′ statement and then we try to execute the second update statement i.e. we try to acquire a lock on TableA.

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

WAITFOR DELAY '00:00:15'

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

COMMIT TRANSACTION

Now, first, execute the Transaction 1 code and then immediately execute the Transaction 2 code. Once Transaction 1 starts its execution, it acquires a lock on TableA and then waits for 15 seconds, At the same time, Transaction 2 starts its execution, it acquires a lock on TableB and then waits for 15 seconds. After 15 seconds, Transaction 1 wants to acquire a lock on TableB which is already acquired by Transaction 2 and at the same time, Transaction 2 wants to acquire a lock on TableA which is already acquired by Transaction 1. In this situation, neither of the transactions can move forward. So, after some time, You will notice that one of the transactions was completed successfully while the other transaction is chosen as the deadlock victim by giving the following error. After a few seconds, you will see that Transaction 2 will be chosen as the deadlock victim as it is the least expensive to roll back than transaction 1.

Setting DEADLOCK_PRIORITY in SQL Server

Let us understand how to set Deadlock Priority in SQL Server with an example. Before that,  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 previous example using SET DEADLOCK_PRIORITY and set Transaction 2 to HIGH so that Transaction 1 will be chosen as the deadlock victim. This is because Transaction 1 is having the default DEADLOCK_PRIORITY which is Normal and it is lower than the DEADLOCK_PRIORITY of Transaction 2.

Transaction1 Code:

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

WAITFOR DELAY '00:00:15'

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

COMMIT TRANSACTION

Transaction2 Code:

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

WAITFOR DELAY '00:00:15'

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

COMMIT TRANSACTION

Now, run Transaction 1 first and then Transaction 2 and after some time you will see that it will choose Transaction 1 as the deadlock victim.

In the next article, we will discuss Logging Deadlocks in SQL Server Error Log. Here, in this article, I try to explain the Deadlock in SQL Server with Examples. I would like to have your feedback. Please post your feedback, question, or comments about this Deadlock in SQL Server with Examples article.