Conversion Deadlock

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.

Process Resource Mode State Command
process592e08 KEY: 26:72057594040745984 (8194443284a0) X WAIT UPDATE
process592e08 KEY: 26:72057594040745984 (8194443284a0) S GRANT SELECT
process579048 KEY: 26:72057594040745984 (8194443284a0) X WAIT UPDATE
process579048 KEY: 26:72057594040745984 (8194443284a0) S GRANT SELECT

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

Advertisements
Previous Post
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: