Sql-server – Weird deadlock graph

deadlocksql server

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 &lt; @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 &lt; @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 &lt; @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 &lt; @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
UPDATE execution plan

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/