We will start with a classic deadlock scenario. The cyclic deadlocks are probably the most common and easily fixable deadlock.

Setup

This deadlock requires a minimum two resources and two process. For simplicity we will use two different tables.

Run the below script to setup the tables.

IF DB_ID(‘DeadLockTest’) IS NULL

CREATE Database DeadLockTest

GO

USE DeadLockTest

GO

CREATE TABLE Cdl1(id int identity(1,1), col1 char(10) default ‘abc’)

CREATE TABLE Cdl2(id int identity(1,1), col1 char(10) default ‘abc’)

GO

INSERT INTO Cdl1 DEFAULT VALUES

INSERT INTO Cdl2 DEFAULT VALUES

 

Now the run the below script in a new session (Session 1)

BEGIN TRAN

–update the first table

UPDATE Cdl1 SET col1= ‘xyz’

–Wait for a second for the other session to catchup

WAITFOR DELAY ’00:00:5′

–update the second table

UPDATE Cdl2 SET col1= ‘xyz’

ROLLBACK TRAN

And the below script in session 2

BEGIN TRAN

–update the first table

UPDATE Cdl2 SET col1= ‘xyz’

–Waitfor a second for the other session to catchup

WAITFOR DELAY ’00:00:5′

–update the second table

UPDATE Cdl1 SET col1= ‘xyz’

ROLLBACK TRAN

One of the session will fail with the error
Msg 1205, Level 13, State 45, Line 13

Transaction (Process ID 60) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Analyze

By analyzing the trace using your favorite method (I had enabled trace flag 1222), you will be able to arrive at the following table (The actual value of the process and resource will vary)

Process	Resource	Mode	State	Command	Object
process579288	RID: 26:1:2671:0	U	WAIT	UPDATE	DeadLockTest.dbo.Cdl1
process579288	RID: 26:1:2673:0	X	GRANT	UPDATE	DeadLockTest.dbo.Cdl1
process13fdc8	RID: 26:1:2673:0	U	WAIT	UPDATE	DeadLockTest.dbo.Cdl1
process13fdc8	RID: 26:1:2671:0	X	GRANT	UPDATE	DeadLockTest.dbo.Cdl1

 

As you can see, both the processes are holding and waiting for incompatible locks. If you analyze the batch you can see that the tables are accessed in a different order.

Fix

This one is an easy fix. If you change the script to access the tables in the same order the deadlock will go away.

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