In some cases, to resolve a deadlock condition, the deadlock monitor may need to identify and rollback multiple transactions. The following demo shows one of such cases.

Setup

Run the below script to create the necessary tables

IF DB_ID(‘DeadLockTest’) IS NULL
CREATE Database DeadLockTest
GO

USE DeadLockTest
GO
CREATE TABLE TabA(id int IDENTITY(1,1), col1 char(100) DEFAULT ‘abc’)
CREATE TABLE TabB(id int IDENTITY(1,1), col1 char(100) DEFAULT ‘abc’)
CREATE TABLE TabC(id int IDENTITY(1,1), col1 char(100) DEFAULT ‘abc’)

INSERT INTO TabA DEFAULT VALUES
INSERT INTO TabB DEFAULT VALUES
INSERT INTO TabC DEFAULT VALUES

Open a new session(Session 1) and run the below script

SET DEADLOCK_PRIORITY HIGH
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN
SELECT * FROM TabA A
INNER JOIN TabB B ON A.id = B.Id
INNER JOIN TabC C ON C.id = B.Id

Open another session(Session 2) and run the below script

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN
SELECT * FROM TabA
UPDATE TabA SET Col1 = ‘xyz’

Open another session(Session 3) and run the below script

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN
SELECT * FROM TabB
UPDATE TabB SET Col1 = ‘xyz’

Open one final session(Session 4) and run the below script

SET DEADLOCK_PRIORITY NORMAL
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN
SELECT * FROM TabC
UPDATE TabC SET Col1 = ‘xyz’

Session 2,3, and 4 will be blocked behind session 1 for the Exclusive lock. Now go back to Session 1 and execute the below script to force a deadlock condition.

SELECT * FROM TabA  A WITH(XLOCK)
INNER JOIN TabB B WITH(XLOCK)   ON A.id = B.Id
INNER JOIN TabC C WITH(XLOCK)  ON C.id = B.Id

ROLLBACK TRAN

In few seconds Sessions 2,3, and 4 will fail with the deadlock error.

Analyze

We are running the first session with a higher deadlock priority compared to other sessions. A deadlock victim is selected based the priority, log used, and  its ability to resolve all deadlock conditions. If we set the DEADLOCK PRIORITY to NORMAL for the first session, SQL server will choose it as the deadlock victim since choosing a single victim will resolve all deadlock conditions.

This post is part of the series SQL Server deadlocks and live locks – Common Patterns. If you have questions, please post a comment here or reach me at @sqlindian