Cyclic deadlock

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

Advertisements
Leave a comment

1 Comment

  1. Ankit Kumar

     /  May 21, 2014

    Hi

    We are also facing deadlocks while updating the same table on different rows across multiple threads(connections).

    We have set the table to be on Disable lock escalation and also set PageLocking off and RowLocking On but somehow now it goes in a deadlock with U and X locks on the index. We even tried to change the index to be a clustered index and still we get the same deadlock pattern.

    We have also set the database to be in

    Would really appreciate your help.

    Regards
    Ankit

    Reply

Leave a Reply to Ankit Kumar Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: