0

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

5
  • I've just had a look at the wait times of each SPID and they are very very high. Certainly not what you would expect for a simple UPDATE using seek. Number of rows updated looks low if the line thickness is anything to go by. Furthermore I have picked up what I would consider an oddity and worth investigation. I can also see that each SPID has a trancount of 2! I am going to go out on a limb here and suggest that you have IMPLICIT_TRANSACTIONS ON? If I am correct, this means that you would need two explicit COMMITs to commit the transaction for the SPID. Can you investigate this angle and feed – Mark Broadbent Sep 17 '14 at 14:06
  • Go figure. The application uses a wild mix of native T-SQL and ORM. I would need to trace it accurately and I'm unsure I can do it. However, I doubt the trancount is the core issue. – spaghettidba Sep 17 '14 at 14:26
  • @spaghettidba, why shouldn't it deadlock? See if this answer helps you understand your issue -- – DenisT Sep 17 '14 at 14:27
  • @DenisT hmmm, seems very similar to my issue. Again, that would mean that MERGE and UPDATE are taking locks in direct and inverse order. In this case shouldn't I see MERGE in the deadlock graph? – spaghettidba Sep 17 '14 at 14:30
  • @spaghettidba that's possible, but it does depend. From what you've given us, implicit transactions on would explain then trancount (the merge opens transaction and then the update would increment the trancount). It would also mean that the merge statement duration would play a factor in the this story rather than us looking at the UPDATE in isolation. However that said, from what we currently have, I agree the picture is quite murky. – Mark Broadbent Sep 17 '14 at 14:33
3

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/

3
  • Thanks Brent. Why the MERGE statement is not in the deadlock graph then? – spaghettidba Sep 17 '14 at 11:13
  • Hi guys, not convinced it is the MERGE. Looking at the deadlock graph it suggests that it is the UPDATE causing it "UPDATE SomeTable...". Spaghetti, are you running multiple concurrent queries for this batch? I think you are running into problems because of this. Can you provide that part of the query plan please? – Mark Broadbent Sep 17 '14 at 13:17
  • @retracement I agree, I highly doubt the MERGE is the culprit here. – spaghettidba Sep 17 '14 at 13:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.