If I deciphered the following deadlock graph correctly, it looks like two processes (SPIDs: 216 and 209) own the exclusive (X) lock on the very same page:
The XDL <resource-list>
shows
<pagelock
fileid="1"
pageid="17410848"
dbid="21"
subresource="FULL"
objectname="33bd93e0-f5b2-43f6-93ca-56bbe6493e0c.dbo.sync_publishers2"
id="lock630b1d5380"
mode="X"
associatedObjectId="72057608416264192">
<owner-list>
<owner
id="process90763f08c8"
mode="X"
requestType="wait" />
</owner-list>
<waiter-list>
<waiter
id="process861129bc28"
mode="X"
requestType="wait" />
</waiter-list>
</pagelock>
And a bit further down
<pagelock
fileid="1"
pageid="17410848"
dbid="21"
subresource="FULL"
objectname="33bd93e0-f5b2-43f6-93ca-56bbe6493e0c.dbo.sync_publishers2"
id="lock630b1d5380"
mode="X"
associatedObjectId="72057608416264192">
<owner-list>
<owner
id="process90763f04e8"
mode="X" />
</owner-list>
<waiter-list>
<waiter
id="process90763f08c8"
mode="X"
requestType="wait" />
</waiter-list>
</pagelock>
How it is even possible and what does it mean?
The full deadlock definition is available here: http://pastebin.com/A4Te3Chx.
UPD: I've filed an item on Microsoft Connect to try to gather authoritative response: https://connect.microsoft.com/SQLServer/Feedback/Details/3119334.
Locks are held on SQL Server resources, such as rows read or modified during a transaction, to prevent concurrent use of resources by different transactions. For example, if an exclusive (X) lock is held on a row within a table by a transaction, no other transaction can modify that row until the lock is released.
SQL Server automatically acquires exclusive locks on data when the data is modified by an INSERT, UPDATE, or DELETE operation.
This just means that there was a queue waiting on that lock.
You can reproduce it with the following (run the setup and then tran 1. You then have 15 seconds to start tran 2 and tran 3 in sequence in different connections).
Setup
USE tempdb
CREATE TABLE T
(
X INT PRIMARY KEY WITH(ALLOW_ROW_LOCKS = OFF),
Filler AS CAST('A' AS CHAR(8000)) PERSISTED
);
INSERT INTO T VALUES (1), (2), (3);
Tran 1
SET XACT_ABORT ON
USE tempdb -- t1
BEGIN TRAN
UPDATE T SET X = X WHERE X = 1
WAITFOR DELAY '00:00:15'
--See what locks are granted just before the deadlock
SELECT resource_description,
request_status,
request_session_id,
X
FROM sys.dm_tran_locks tl
LEFT JOIN T WITH(NOLOCK)
ON sys.fn_PhysLocFormatter(T.%% physloc%%) = '(' + RTRIM(resource_description) + ':0)'
WHERE resource_associated_entity_id = (SELECT partition_id
FROM sys.partitions
WHERE object_id = object_id('T'));
RAISERROR ('',0,1) WITH NOWAIT;
UPDATE T SET X = X WHERE X = 3
WAITFOR DELAY '00:00:20'
ROLLBACK
Tran 2
SET XACT_ABORT ON
USE tempdb -- t2
BEGIN TRAN
UPDATE T SET X = X WHERE X = 2
UPDATE T SET X = X WHERE X = 1
WAITFOR DELAY '00:00:20'
ROLLBACK
Tran 3
SET XACT_ABORT ON
USE tempdb -- t3
BEGIN TRAN
UPDATE T SET X = X WHERE X = 3
UPDATE T SET X = X WHERE X = 1
ROLLBACK
The result of the query against tran_locks
immediately before requesting the lock that will cause deadlock shows
+----------------------+----------------+--------------------+---+
| resource_description | request_status | request_session_id | X |
+----------------------+----------------+--------------------+---+
| 4:416 | GRANT | 61 | 1 |
| 4:416 | WAIT | 64 | 1 |
| 4:416 | WAIT | 65 | 1 |
| 4:418 | GRANT | 64 | 2 |
| 4:419 | GRANT | 65 | 3 |
+----------------------+----------------+--------------------+---+
The deadlock graph I received is as follows.
Though it says the deadlock victim was waiting on a lock owned by tran 2 this is not actually the case. At the time of the deadlock the lock was owned by tran 1 and tran 2 was first in line for it before tran 3.
The deadlock graph XML shows this as it has two nodes for the same resource (page 416) and in one the "owner" has a requestType="wait"
<resource-list>
<pagelock
fileid="4"
pageid="416"
dbid="2"
subresource="FULL"
objectname="tempdb.dbo.T"
id="lock2486d8c4380"
mode="X"
associatedObjectId="936748728230805504">
<owner-list>
<owner
id="process2486ba0cca8"
mode="X"
requestType="wait" />
</owner-list>
<waiter-list>
<waiter
id="process2485370c8c8"
mode="X"
requestType="wait" />
</waiter-list>
</pagelock>
<pagelock
fileid="4"
pageid="416"
dbid="2"
subresource="FULL"
objectname="tempdb.dbo.T"
id="lock2486d8c4380"
mode="X"
associatedObjectId="936748728230805504">
<owner-list>
<owner
id="process2485370c4e8"
mode="X" />
</owner-list>
<waiter-list>
<waiter
id="process2486ba0cca8"
mode="X"
requestType="wait" />
</waiter-list>
</pagelock>
<pagelock
fileid="4"
pageid="419"
dbid="2"
subresource="FULL"
objectname="tempdb.dbo.T"
id="lock248636ace80"
mode="X"
associatedObjectId="936748728230805504">
<owner-list>
<owner
id="process2485370c8c8"
mode="X" />
</owner-list>
<waiter-list>
<waiter
id="process2485370c4e8"
mode="X"
requestType="wait" />
</waiter-list>
</pagelock>
</resource-list>
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With