Deadlocks due to different Access Order

A deadlock can occur when two sessions try to access the same index in a different order in non-compatible lock modes. The following script demonstrates this behavior.

Setup

Run the below scripts to create the necessary tables.

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

USE DeadLockTest
GO

CREATE TABLE dbo.aodl(id int identity(1,1) primary key clustered, filler char(100) default ‘abc’)
GO

SET NOCOUNT ON

DECLARE @i int
SET @i = 1
WHILE @i < 10000
BEGIN
INSERT INTO dbo.aodl DEFAULT VALUES
SET @i = @i+1
END

Now open a new session (session 1) and run the below script. Make sure to change the WAITFOR TIME value according to the time you are running. Its is used to ensure that both the queries will be starting at the same time.

WAITFOR TIME ’18:02′
BEGIN TRAN
SELECT * from dbo.aodl WITH(UPDLOCK)
WHERE Id > 4000
ORDER BY ID ASC
ROLLBACK TRAN

Now open another session (Session 2) and run the below script after changing the start time according to the first script.

WAITFOR TIME ’18:02′
BEGIN TRAN
SELECT * from dbo.aodl WITH(UPDLOCK)
WHERE Id < 6000
ORDER BY ID DESC
ROLLBACK TRAN

One of the session will fail with the deadlock error.

Analyze

From the script itself, it is obvious why the deadlock is happening. Lets confirm this by preparing the deadlock table.

Process Resource Mode State Command Object
process6dd708 KEY: 26:72057594042318848 (c904e608358d) U WAIT SELECT indexname=PK__spdl1
process6dd708 KEY: 26:72057594042318848 (30488d9fd081) U GRANT SELECT indexname=PK__spdl2
process6c34c8 KEY: 26:72057594042318848 (30488d9fd081) U WAIT SELECT indexname=PK__spdl2
process6c34c8 KEY: 26:72057594042318848 (c904e608358d) U GRANT SELECT indexname=PK__spdl1

 

Lets also find out what are the rows involved in deadlock using the below query.

SELECT %%lockres%% as LockResource, ID
FROM dbo.aodl
WHERE %%lockres%% IN(‘(c904e608358d)’, ‘(30488d9fd081)’)

On my machine it yields the below result.

LockResource ID
(c904e608358d) 5006
(30488d9fd081) 5007

You can see that both the processes were scanning the table in the opposite order and collided in the middle.

Fix

Change the ORDER BY clause to ensure that both the processes were accessing the table in the same order. One process will be blocked until the other finishes, but it won’t fail with a deadlock error.

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

Leave a 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: