Title: Pagelock on same delete query
Tags: sql;sql-server;deadlock
Question: I get a deadlock sometimes when trying to execute a delete statement. The deadlock graph shows the same delete statement in victim and "winner" list (the process list shows same query with 2 different process ids). The resource list shows same objectname but different pageid.
Can anyone explains what is getting wrong here and why this deadlock is occurring? How is it possible for both processes to obtain UIX on same object? Is it something to do with table indexes? If so, I've a clustered index on composite primary key of the table and a non clustered index additionally...
<deadlock&gt;
<victim-list&gt;
<victimProcess id="process7b8d0784e8" /&gt;
</victim-list&gt;
<process-list&gt;
<process id="process7b8d0784e8" taskpriority="0" logused="1048" waitresource="PAGE: 103:10:395854 " waittime="1007" ownerId="11148456875" lockMode="U" status="suspended" isolationlevel="read committed (2)" currentdb="103"&gt;
<inputbuf&gt;
(@0 nvarchar(128),@1 nvarchar(128))DELETE [dbo].[MyDummyTable]
WHERE (([ConId] = @0) AND ([Key] = @1))
</inputbuf&gt;
</process&gt;
<process id="process59bbdd2108" taskpriority="0" logused="2508" waitresource="PAGE: 103:11:334598 " waittime="921" ownerId="11148454338" lockMode="U" status="suspended" isolationlevel="read committed (2)" currentdb="103"&gt;
<inputbuf&gt;
(@0 nvarchar(128),@1 nvarchar(128))DELETE [dbo].[MyDummyTable]
WHERE (([ConId] = @0) AND ([Key] = @1))   </inputbuf&gt;
</process&gt;
</process-list&gt;
<resource-list&gt;
<pagelock fileid="10" pageid="395854" dbid="103" subresource="FULL" objectname="7C91.dbo.MyDummyTable" id="lock3c5c753480" mode="UIX" associatedObjectId="498496"&gt;
<owner-list&gt;
<owner id="process59bbdd2108" mode="UIX" /&gt;
</owner-list&gt;
<waiter-list&gt;
<waiter id="process7b8d0784e8" mode="U" requestType="wait" /&gt;
</waiter-list&gt;
</pagelock&gt;
<pagelock fileid="11" pageid="334598" dbid="103" subresource="FULL" objectname="7C91.dbo.MyDummyTable" id="lock46e37fdc80" mode="UIX" associatedObjectId="498496"&gt;
<owner-list&gt;
<owner id="process7b8d0784e8" mode="UIX" /&gt;
</owner-list&gt;
<waiter-list&gt;
<waiter id="process59bbdd2108" mode="U" requestType="wait" /&gt;
</waiter-list&gt;
</pagelock&gt;
</resource-list&gt;
</deadlock&gt;
Excuse me, if the question is too basic but I'm no pro in deadlocks.


Here is another answer: If your delete queries should return a unique individual row, then add an index to help find those rows (index on 'ConId' and 'Key' in your example).
As it stands the queries are locking pages, and on occasion you will have two rows within the same page, causing the deadlock you are experiencing

{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 1, 'CommentCount': 0, 'ContentLicense': 'CC BY-SA 3.0', 'CreationDate': '2014:00:07/21/14', 'FavoriteCount': None, 'PostTypeId': 1, 'Tags': '<sql><sql-server><deadlock>', 'Title': 'Pagelock on same delete query', 'ViewCount': 392, 'id': 24870240, 'user_AboutMe': '', 'user_AccountId': 1698561.0, 'user_DisplayName': 'Flair', 'user_Reputation': 2819.0, 'user_Views': 64.0, 'user_id': 1559311.0}}