I got a deadlock graph that I cannot completely understand. This is the deadlock graph xml (table names obfuscated, obviously):
<?xml version="1.0" encoding="utf-16"?>
<deadlock-list>
<deadlock>
<victim-list>
<victimProcess id="processd3486d0c8" />
</victim-list>
<process-list>
<process id="processd3486d0c8" taskpriority="0" logused="380" waitresource="KEY: 11:72057594281328640 (a4e5f79ce334)" waittime="7111" ownerId="3646949929" transactionname="user_transaction" lasttranstarted="2014-09-16T10:52:56.867" XDES="0x854fc86c8" lockMode="U" schedulerid="24" kpid="10688" status="suspended" spid="3330" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2014-09-16T10:52:57.057" lastbatchcompleted="2014-09-16T10:52:56.977" lastattention="1900-01-01T00:00:00.977" clientapp=".Net SqlClient Data Provider" hostname="HOSTANAME02" hostpid="6432" loginname="DOMAIN\UserName" isolationlevel="read committed (2)" xactid="3646949929" currentdb="11" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="48" sqlhandle="0x02000000fe50ba0363afd2298429eb5f5ba0db0d07d32d800000000000000000000000000000000000000000">
UPDATE SomeTable
SET SomeFlag = 1
WHERE KeyColum = @0
AND Version < @1
</frame>
<frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown
</frame>
</executionStack>
<inputbuf>
(@0 nvarchar(40),@1 int)UPDATE SomeTable
SET SomeFlag = 1
WHERE KeyColum = @0
AND Version < @1
</inputbuf>
</process>
<process id="processd348450c8" taskpriority="0" logused="464" waitresource="KEY: 11:72057594281328640 (70c3a45ec8ca)" waittime="7112" ownerId="3646949958" transactionname="user_transaction" lasttranstarted="2014-09-16T10:52:56.993" XDES="0xc329dcd28" lockMode="U" schedulerid="19" kpid="15520" status="suspended" spid="3130" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2014-09-16T10:52:57.057" lastbatchcompleted="2014-09-16T10:52:57.010" lastattention="1900-01-01T00:00:00.010" clientapp=".Net SqlClient Data Provider" hostname="HOSTANAME03" hostpid="9144" loginname="DOMAIN\UserName" isolationlevel="read committed (2)" xactid="3646949958" currentdb="11" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="48" sqlhandle="0x02000000fe50ba0363afd2298429eb5f5ba0db0d07d32d800000000000000000000000000000000000000000">
UPDATE SomeTable
SET SomeFlag = 1
WHERE KeyColum = @0
AND Version < @1
</frame>
<frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown
</frame>
</executionStack>
<inputbuf>
(@0 nvarchar(40),@1 int)UPDATE SomeTable
SET SomeFlag = 1
WHERE KeyColum = @0
AND Version < @1
</inputbuf>
</process>
</process-list>
<resource-list>
<keylock hobtid="72057594281328640" dbid="11" objectname="MyDatabase.dbo.SomeTable" indexname="PK_SomeTable" id="lockbea9d6300" mode="X" associatedObjectId="72057594281328640">
<owner-list>
<owner id="processd348450c8" mode="X" />
</owner-list>
<waiter-list>
<waiter id="processd3486d0c8" mode="U" requestType="wait" />
</waiter-list>
</keylock>
<keylock hobtid="72057594281328640" dbid="11" objectname="MyDatabase.dbo.SomeTable" indexname="PK_SomeTable" id="lockb3ddfb180" mode="X" associatedObjectId="72057594281328640">
<owner-list>
<owner id="processd3486d0c8" mode="X" />
</owner-list>
<waiter-list>
<waiter id="processd348450c8" mode="U" requestType="wait" />
</waiter-list>
</keylock>
</resource-list>
</deadlock>
</deadlock-list>
Looks like the same statement is deadlocking with itself, but I don't understand how.
The UPDATE statement affects multiple rows (KeyColumn is just part of the primary key).
This statement is part of a transaction that looks like this:
WITH SuppliedValues AS (
SELECT *
FROM (
VALUES (@0,@1,@2,@3,@4,@5,0,@6,@7)
) AS v (KeyColumn,Col1,version,Col3,Col4,Col5,Col6,Col7,Col8)
)
MERGE INTO SomeTable AS dest
USING SuppliedValues AS src
ON dest.KeyColumn = src.KeyColumn
AND dest.Col1 = src.Col1
WHEN MATCHED
AND src.version >= dest.version
THEN UPDATE
SET Version = src.version, Col2 = src.Col2, Col3 = src.Col3,
Col4 = src.Col4, Col5 = src.Col5, Col6 = src.Col6,
Col7 = src.Col7
WHEN NOT MATCHED
THEN INSERT (KeyColumn, Col1, Version, Col2, Col3, Col4, Col5, Col6, Col7)
VALUES (src.KeyColumn, src.Col1, src.Version, src.Col2, src.Col3, src.Col4, src.Col5, src.col6, src.Col7);
IF @@ROWCOUNT = 0
RAISERROR (''VERSION_TOO_LOW'',11,1)
UPDATE SomeTable
SET SomeColumn = 1
WHERE KeyColumn = @0
AND Version < @1
I would have expected to find the two different statements in the deadlock graph, rather than the same statement for both victim and winner.
They only way I see a deadlock happening here is that the two statements try to modify the rows in a different order, but, again, I don't see how this is possible.
The two KEY resources in the deadlock graph point to two different rows with the same "KeyColumn" value, but different values for the other column in the primary key.
EDIT: added UPDATE execution plan as requested by @Paul White
Best Answer
Uh oh - bad news. There's a known deadlocks by-design issue with MERGE:
https://connect.microsoft.com/SQLServer/feedback/details/723696/basic-merge-upsert-causing-deadlocks
And just generally, I'd avoid MERGE in general due to the problems documented by Aaron Bertrand:
http://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/