Do you think a simple query like the below one can deadlock with another instance running the same query?

SELECT @id = id FROM dbo.midl WITH(UPDLOCK)
WHERE id2 = @id

Read on to find out.

Setup

Since this post is about deadlock due to parallelism, make sure that parallelism is enabled on your server.

Run the below script to create the table.

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

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

SET NOCOUNT ON

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

UPDATE dbo.midl SET id2 = id+1

Now run the following query in multiple sessions or use a tool like SQLQueryStress developed by Adam Machanic (b | t) which can be used for executing a query in multiple sessions.

SET NOCOUNT ON

DECLARE @id int
WHILE 1=1
BEGIN
SET @id = (RAND() * 1000) + 1
SELECT @id = id FROM dbo.midl WITH(UPDLOCK)
WHERE id2 = @id
END

In my tests with five concurrent sessions, three of them deadlocked under a minute.

The queries failed with the error

Msg 1205, Level 13, State 51, Line 6

Transaction (Process ID 60) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Analyze

If you had paid close attention to the error message, it says the deadlock happened on lock | communication buffer resources, which is a hint about the actual issue here. If you pay close attention to the resource list in the deadlock trace you can see the exchange operators appearing in the waiter list.

exchangeEvent id=Pipe8ac26500 WaitType=e_waitPipeGetRow nodeId=1

Deadlocks involving parallelism always involve thread or lock resources. So we can proceed in our conventional route and prepare our deadlock table.

I had simplified the below table to include only two process instead of all four participants in the deadlock.

This only tells us that the contention is on the PK. We need to see what keys are being actually involved. We can get this using the undocumented %%lockres%% function.

By running the below query,

SELECT id, %%lockres%% as LockResource
FROM dbo.midl
WHERE %%lockres%% IN(‘(8194443284a0)’,'(1524a5133a9d)’)

Now we can see that, process81e714c8 is holding a lock on row with id 1 and is waiting for a lock on id 12709. This is OK since it is a normal blocking scenario. But process579948 is holding a lock on row with id 12709 and waiting for a lock on id 1. If we look at the execution plan, we can see why.

When a task is executed using a parallel operator, the input is divided into a number of parallel buckets and each parallel thread works on its own subset of data.

Fix

This is again an easy fix. You have probably noticed that the id2 column does not have an index. If you create a non-clustered index on the id2 column, the deadlock will disappear.

PS: If you did not get the deadlock error, try adding few more thousands of rows and/or few more concurrent processes running the same query.

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