Deadlock due to Different Lock granularity

A deadlock can occur when a table is accessed with different lock granularity  in the same transaction. The following script demonstrates the concept.

Setup

Run the following script to create the necessary table.

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

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

INSERT INTO lgdl DEFAULT VALUES
GO
INSERT INTO lgdl DEFAULT VALUES
GO

Now open a new session (session 1) and run the below script.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN

SELECT * FROM lgdl WITH(ROWLOCK) WHERE id = 1

WAITFOR DELAY ’00:00:05′

UPDATE l
SET filler = ‘xyz’
FROM lgdl L WITH(PAGLOCK)
WHERE id =1

ROLLBACK TRAN

Open another session (session 2)  and run the below script.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN

SELECT * FROM lgdl WITH(ROWLOCK) WHERE id = 2

WAITFOR DELAY ’00:00:05′

UPDATE l
SET filler = ‘xyz’
FROM lgdl L  WITH(PAGLOCK)
WHERE id =2

ROLLBACK TRAN

Analyze

Lets build the deadlock analysis table from the trace results.

Process Resource Mode State Command Object
process7ed948 PAGE: 26:1:2898 U WAIT UPDATE dbo.lgdl
process7ed948 PAGE: 26:1:2898 IS GRANT UPDATE dbo.lgdl
process44f708 PAGE: 26:1:2898 X WAIT UPDATE dbo.lgdl
process44f708 PAGE: 26:1:2898 U GRANT UPDATE dbo.lgdl

So what is happening here is, both the processes will start a S lock on the individual keys and an IS lock on the page. Since we explicitly specified  PAGLOCK in the update statement both transactions will need to acquire locks at the page level for the UPDATE statement. SQL server acquires locks for updates in two phases. First it will acquire a U lock for the read phase of the Update and then an X lock for the Update. So when the first transaction tries to acquire a U lock on the page, it will succeed because a U lock is compatible with an IS lock. However, when it tries to convert the U lock to an X lock, it will be blocked since an X lock is not compatible with an IS lock.

Meanwhile when the second the second transaction tries to convert the IS lock to a U lock, it will be blocked because a U lock is not compatible with another U lock. And when two transactions block each other the result is a deadlock.

For details on SQL server lock compatibility, please refer to this entry on MSDN.

Fix

Make sure a table is accessed using the same lock granularity inside a transaction especillly when using lock granularity hints.

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: