In a previous post, I have provided links to multiple articles that demonstrate how a SELECT statement involving a key lookup running under READ COMMITTED isolation level can deadlock with another session running an UPDATE statement.
In this post we will discuss about another case where a SELECT statement running under READ COMMITTED isolation level can deadlock with another session due to the locking behavior.
Setup
Run the below script to create the test table and populate it.
IF DB_ID(‘DeadLockTest’) IS NULL
CREATE Database DeadLockTest
GO
USE DeadLockTest
GO
create table dbo.rcdl (id int identity(1,1) primary key clustered, id2 int, filler char(1000) default ‘abc’)
GO
set nocount on
declare @i int
set @i = 0
while @i < 1000
begin
insert dbo.rcdl(id2) values ( @i)
set @i = @i + 1
end
Go
create index ix_rcdl on dbo.rcdl(id2)
go
Now start another session (Session 1) and run the below command.
BEGIN TRAN
UPDATE dbo.rcdl
SET filler = ‘xyz’,id2=id
WHERE id = 24
WAITFOR DELAY ’00:00:05′
UPDATE dbo.rcdl
SET filler = ‘xyz’,id2=id
WHERE id = 1
ROLLBACK TRAN
Start one more session (session 2) and run the below query
SELECT filler FROM dbo.rcdl WHERE id2 < 25
In five seconds, the SELECT statement running in session 2 will fail with the below error.
Msg 1205, Level 13, State 51, Line 1
Transaction (Process ID 74) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Analyze
Let’s go ahead and populate the deadlock analysis table from the deadlock trace.
Process | Resource | Mode | State | Command |
process593048 | KEY: 26:72057594042056704 (1edefaa29392) | S | WAIT | SELECT |
process593048 | KEY: 26:72057594042056704 (bb0d06c12baa) | S | GRANT | SELECT |
process13ee08 | KEY: 26:72057594042056704 (bb0d06c12baa) | X | GRANT | UPDATE |
process13ee08 | KEY: 26:72057594042056704 (1edefaa29392) | X | WAIT | UPDATE |
You can see that the update command is holding an X lock on one row and waiting for another X lock. The SELECT statement is also holding a shared (S) lock and waiting for another shared lock. But why the SELECT statement is holding a shared lock under READ COMMITTED isolation level? As stated in a previous post, SELECT statements should only hold the shared lock for the duration of processing the row. For a visual representation of the cocept of lock duration under different isolation levels, see Micheal J Swart’s (b |t) post “Visualizing Transaction Isolations For SQL Server”
But there are few exceptions to this rule. In certain cases, SQL server will keep the shared locks until the end of the transaction to ensure consistency. One of them involves “UNORDERED PREFETCH”, which happens to be the case here. If you look at the execution plan xml of the SELECT statement, you can see that our plan uses a Nested Loop Join with UNORDERED PREFETCH. Craig Freedman blogged about the scenarios where the shared locks are held until the end of the transaction here. He also wrote about UNORDERED PREFETCH in detail here.
Fix
You can use any of the below methods to resolve this issue.
- Combine the UPDATE statements to a single UPDATE statement.
- Cover the index by including the filler column
- Acquire non-compatible locks using a locking hint.
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