This post discusses another ‘classic’ deadlock.

Setup

This deadlock involves just one resource and two processes, each holding a compatible lock and trying for a non-compatible lock. We will use a more restrictive REPEATABLE READ ISOLATION level for this demo. You can see that unlike the cyclic deadlock example, both the sessions are running the same script here.

Run the below script to create the table and test data.

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

USE DeadLockTest
GO

CREATE TABLE dbo.codl(id int identity(1,1) primary key clustered, col1 char(10) default ‘abc’)

INSERT INTO dbo.codl DEFAULT VALUES

Now run the below script simultaneously in two sessions.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN
SELECT * FROM dbo.codl WHERE ID = 1
WAITFOR DELAY ’00:00:05′
UPDATE dbo.codl
SET col1 = ‘xyz’ WHERE id = 1
ROLLBACK TRAN

One of the sessions will fail with the deadlock error.

Analyze

If you prepare the deadlock analysis table from the trace data, it will look like the following.

You can see that only a single resource is involved(specifically a single key) and both the sessions are holding a compatible Shared lock on it and both are trying to convert it into an exclusive lock.

Fix

You won’t see this deadlock happening in READ COMMITTED isolation level since the lock duration of a shared lock in READ COMMITTED is only during the time the row is being processed, with some exceptions where the lock is held until the statement is completed. Obviously in REPEATABE READ isolation level, the locks are held until the end of the transaction. To fix this deadlock in REPEATABLE READ isolation level you will need to acquire a non-compatible lock in the beginning by using a UPDLOCK hint in the SELECT statement.

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