<resource-list>
<keylock hobtid="72057616768958464" dbid="16" objectname="Orders"
indexname="PK_Orders" id="lock223c94b6980" mode="X"
associatedObjectId="72057616768958464">
<owner-list>
<owner id="process218b9814ca8" mode="X" />
</owner-list>
<waiter-list>
<waiter id="process218bb8dcca8" mode="S" requestType="wait" />
</waiter-list>
</keylock>
<keylock hobtid="72057616768958464" dbid="16" objectname="Orders"
indexname="PK_Orders" id="lock223c94b6980" mode="X"
associatedObjectId="72057616768958464">
<owner-list>
<owner id="process218bb8dcca8" mode="S" requestType="wait" />
</owner-list>
<waiter-list>
<waiter id="process218bb829468" mode="S" requestType="wait" />
</waiter-list>
</keylock>
<keylock hobtid="72057616768958464" dbid="16" objectname="Orders"
indexname="PK_Orders" id="lock2240f4bb680" mode="X"
associatedObjectId="72057616768958464">
<owner-list>
<owner id="process218bb829468" mode="X" />
</owner-list>
<waiter-list>
<waiter id="process218b9814ca8" mode="S" requestType="wait" />
</waiter-list>
</keylock>
</resource-list>
I have a deadlock involving 3 processes, why are there two entries for the same lock (same id) in resource-list? Also what's the difference between the "mode" property on the "keylock", "waiter" and "owner" elements?
This deadlock is happening in a stored procedure where I first update a table and then do a select on the same table. Both update and select are filtering on the primary key (unique for each process), so I would expect row level locking in each process and not block each other. This deadlock is happening in production and I am not able to reproduce it in dev environment.
Any help would be greatly appreciated.
For my own sanity I did the following find and replaces
To give the resource list below with simpler process names
<resource-list>
<keylock hobtid="72057616768958464" dbid="16" objectname="Orders"
indexname="PK_Orders" id="lock223c94b6980" mode="X"
associatedObjectId="72057616768958464">
<owner-list>
<owner id="process1" mode="X" />
</owner-list>
<waiter-list>
<waiter id="process2" mode="S" requestType="wait" />
</waiter-list>
</keylock>
<keylock hobtid="72057616768958464" dbid="16" objectname="Orders"
indexname="PK_Orders" id="lock223c94b6980" mode="X"
associatedObjectId="72057616768958464">
<owner-list>
<owner id="process2" mode="S" requestType="wait" />
</owner-list>
<waiter-list>
<waiter id="process3" mode="S" requestType="wait" />
</waiter-list>
</keylock>
<keylock hobtid="72057616768958464" dbid="16" objectname="Orders"
indexname="PK_Orders" id="lock2240f4bb680" mode="X"
associatedObjectId="72057616768958464">
<owner-list>
<owner id="process3" mode="X" />
</owner-list>
<waiter-list>
<waiter id="process1" mode="S" requestType="wait" />
</waiter-list>
</keylock>
</resource-list>
For lock223c94b6980 process1 has an X lock on it, process2 is waiting for an S lock on it, process3 is also waiting for an S lock on it but that is showing as blocked by process2 not by the root blocker process1 for the reason below.
meanwhile for lock2240f4bb680 process3 holds an X lock on it and process1 is waiting for an S lock on it.
The circular wait really just involves process1 and process3 anyway as they both own an X lock on one key and are waiting on the S lock on the other key.
"the reason below"
The S lock request by process2 is compatible with the S lock request from process3 but this is just how the lock blocking information is maintained. If a waiter is not the first waiter with state WAIT, it is always considered blocked by the first waiter with state WAIT
There are only two lock modes in play in this graph. The S lock (shared) and X lock (exclusive). S locks are compatible with other S locks but incompatible with X locks.
An X lock cannot be granted until any S locks on the resource have been released. And an S lock can't be granted until any X lock on the resource has been released. Processes requesting an incompatible lock are blocked and wait for the lock to be released (or lock timeout to be reached if set).
On the keylock element this is the mode that the lock is currently granted in, on the owner element this is the mode that the blocking session holds or is waiting for (when the session considered as blocker is itself waiting on a lock on the resource and not the root blocker), on the waiter element this is the mode that the waiter is awaiting.
You will also encounter other lock modes more generally. See this table for compatibility details between all of them.
Reproduction Scenario
The below code reproduces the same blocking pattern - paste each into a new query window and run in order listed.
Unfortunately Session 2 is invariably (in my testing) picked as the first deadlock victim despite being unable to resolve the deadlock and the other two sessions run for another few seconds until the deadlock monitor kicks in again and kills one of the true causes. If session 2 has SET DEADLOCK_PRIORITY HIGH added then one of the true causes will be killed first and two sessions can successfully run to completion.
Session 1
DROP TABLE IF EXISTS T1, SharedMessages
CREATE TABLE T1(Id INT PRIMARY KEY, Data CHAR(8000))
INSERT T1 VALUES (1, 'A'), (2, 'B')
CREATE TABLE SharedMessages(session_id INT, Message VARCHAR(8000))
SET XACT_ABORT, NOCOUNT ON
BEGIN TRAN
UPDATE T1 SET DATA = 'AA' WHERE ID = 1
INSERT SharedMessages VALUES (@@SPID, 'T1: AA Update done')
WHILE NOT EXISTS (SELECT * FROM SharedMessages WITH (NOLOCK) WHERE Message = 'T3: About to block on ID = 1')
WAITFOR DELAY '00:00:01'
INSERT SharedMessages VALUES (@@SPID, 'T1: About to deadlock')
SELECT * FROM T1 WITH(ROWLOCK) WHERE ID = 2
ROLLBACK
Session 2
SET XACT_ABORT, NOCOUNT ON
BEGIN TRAN
WHILE NOT EXISTS (SELECT * FROM SharedMessages WITH (NOLOCK) WHERE Message = 'T1: AA Update done')
WAITFOR DELAY '00:00:01'
INSERT SharedMessages VALUES (@@SPID, 'T2: About to block on ID = 1')
SELECT * FROM T1 WITH(ROWLOCK) WHERE ID = 1
WAITFOR DELAY '00:05:00'
/*This session will very likely be picked as the first rollback victim as it has written less to the transaction log than the others*/
ROLLBACK;
Session 3
SET XACT_ABORT, NOCOUNT ON
BEGIN TRAN
UPDATE T1 SET DATA = 'BB' WHERE ID = 2
WHILE NOT EXISTS (SELECT * FROM SharedMessages WITH (NOLOCK) WHERE Message = 'T2: About to block on ID = 1')
WAITFOR DELAY '00:00:03'
INSERT SharedMessages VALUES (@@SPID, 'T3: About to block on ID = 1')
SELECT * FROM T1 WITH(ROWLOCK) WHERE ID = 1
ROLLBACK
Session 4 (Monitor)
WHILE NOT EXISTS (SELECT * FROM SharedMessages WITH (NOLOCK) WHERE Message = 'T1: About to deadlock')
WAITFOR DELAY '00:00:00.5'
SELECT *
FROM SharedMessages WITH (NOLOCK)
SELECT *
FROM sys.dm_os_waiting_tasks
WHERE session_id IN (SELECT session_id FROM SharedMessages WITH (NOLOCK))