Sql-server – How to understand this DeadLock Graph

deadlocksql server

I'm attempting to decipher the content of this deadlock graph. Is there some way to view this information in a more visually meaningful way? Manually reading through the XML is hurting my eyes.

<deadlock-list>
  <deadlock victim="process46d288">
    <process-list>
      <process id="process46d288" taskpriority="0" logused="9968" waitresource="PAGE: 14:1:4468703" waittime="492" ownerId="292248020" transactionname="user_transaction" lasttranstarted="2015-03-03T16:25:12.250" XDES="0x38c300b50" lockMode="U" schedulerid="4" kpid="12432" status="suspended" spid="51" sbid="0" ecid="4" priority="0" trancount="0" lastbatchstarted="2015-03-03T16:25:12.250" lastbatchcompleted="2015-03-03T16:25:12.250" clientapp=".Net SqlClient Data Provider" hostname="ER-PC" hostpid="5524" isolationlevel="read committed (2)" xactid="292248020" currentdb="14" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
        <executionStack>
          <frame procname=" Live_Data.dbo.DeleteOrderByID" line="58" stmtstart="2624" stmtend="2976" sqlhandle="0x03000e009cb1ba26ad504d0133a400000100000000000000">
            DELETE a
            FROM
            [dbo].[OrderContactAddresses] a
            LEFT JOIN [dbo].[OrderContactAddressLink] b ON a.[ID] = b.[OrderContactAddressID]
            WHERE
            b.[ID] IS NULL
          </frame>
        </executionStack>
        <inputbuf>
        </inputbuf>
      </process>
      <process id="process453048" taskpriority="0" logused="10000" waittime="1087" schedulerid="3" kpid="9072" status="suspended" spid="51" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2015-03-03T16:25:12.250" lastbatchcompleted="2015-03-03T16:25:12.250" clientapp=".Net SqlClient Data Provider" hostname="ER-PC" hostpid="5524" loginname="CSUser" isolationlevel="read committed (2)" xactid="292248020" currentdb="14" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
        <executionStack>
          <frame procname=" Live_Data.dbo.DeleteOrderByID" line="58" stmtstart="2624" stmtend="2976" sqlhandle="0x03000e009cb1ba26ad504d0133a400000100000000000000">
            DELETE a
            FROM
            [dbo].[OrderContactAddresses] a
            LEFT JOIN [dbo].[OrderContactAddressLink] b ON a.[ID] = b.[OrderContactAddressID]
            WHERE
            b.[ID] IS NULL
          </frame>
        </executionStack>
        <inputbuf>
          Proc [Database Id = 14 Object Id = 649769372]
        </inputbuf>
      </process>
      <process id="process13f288" taskpriority="0" logused="10000" waittime="489" schedulerid="1" kpid="10700" status="suspended" spid="51" sbid="0" ecid="1" priority="0" trancount="0" lastbatchstarted="2015-03-03T16:25:12.250" lastbatchcompleted="2015-03-03T16:25:12.250" clientapp=".Net SqlClient Data Provider" hostname="ER-PC" hostpid="5524" isolationlevel="read committed (2)" xactid="292248020" currentdb="14" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
        <executionStack>
          <frame procname=" Live_Data.dbo.DeleteOrderByID" line="58" stmtstart="2624" stmtend="2976" sqlhandle="0x03000e009cb1ba26ad504d0133a400000100000000000000">
            DELETE a
            FROM
            [dbo].[OrderContactAddresses] a
            LEFT JOIN [dbo].[OrderContactAddressLink] b ON a.[ID] = b.[OrderContactAddressID]
            WHERE
            b.[ID] IS NULL
          </frame>
        </executionStack>
        <inputbuf>
        </inputbuf>
      </process>
      <process id="process429948" taskpriority="0" logused="21824" waitresource="KEY: 14:72057594044219392 (4e026ee4a4e6)" waittime="759" ownerId="292248016" transactionname="user_transaction" lasttranstarted="2015-03-03T16:25:09.990" XDES="0x25cf193c0" lockMode="S" schedulerid="2" kpid="14072" status="suspended" spid="79" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2015-03-03T16:25:11.973" lastbatchcompleted="2015-03-03T16:25:11.970" clientapp=".Net SqlClient Data Provider" hostname="2800-PC" hostpid="2112" loginname="CSUser" isolationlevel="read committed (2)" xactid="292248016" currentdb="14" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
        <executionStack>
          <frame procname="adhoc" line="1" stmtstart="94" sqlhandle="0x020000002050d705ed913668c479421685d2ab98cbf71802">
            DELETE FROM [dbo].[OrderContactPhones] WHERE ([ID] = @p0) AND ([PhoneNumber] = @p1) AND ([Extension] = @p2)
          </frame>
          <frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">
            unknown
          </frame>
        </executionStack>
        <inputbuf>
          (@p0 int,@p1 nvarchar(4000),@p2 nvarchar(4000))DELETE FROM [dbo].[OrderContactPhones] WHERE ([ID] = @p0) AND ([PhoneNumber] = @p1) AND ([Extension] = @p2)
        </inputbuf>
      </process>
    </process-list>
    <resource-list>
      <pagelock fileid="1" pageid="4468703" dbid="14" objectname=" Live_Data.dbo.OrderContactAddresses" id="lock1e373ec00" mode="IX" associatedObjectId="72057594205306880">
        <owner-list>
          <owner id="process429948" mode="IX"/>
        </owner-list>
        <waiter-list>
          <waiter id="process46d288" mode="U" requestType="wait"/>
        </waiter-list>
      </pagelock>
      <exchangeEvent id="Port801ab600" WaitType="e_waitPortOpen" nodeId="5">
        <owner-list>
          <owner id="process13f288"/>
        </owner-list>
        <waiter-list>
          <waiter id="process453048"/>
        </waiter-list>
      </exchangeEvent>
      <exchangeEvent id="Pipe1151a6800" WaitType="e_waitPipeGetRow" nodeId="9">
        <owner-list>
          <owner id="process46d288"/>
        </owner-list>
        <waiter-list>
          <waiter id="process13f288"/>
        </waiter-list>
      </exchangeEvent>
      <keylock hobtid="72057594044219392" dbid="14" objectname=" Live_Data.dbo.OrderContactPhoneLink" indexname="PK_OrderContactPhoneLink" id="lock1cc613b00" mode="X" associatedObjectId="72057594044219392">
        <owner-list>
          <owner id="process453048" mode="X"/>
        </owner-list>
        <waiter-list>
          <waiter id="process429948" mode="S" requestType="wait"/>
        </waiter-list>
      </keylock>
    </resource-list>
  </deadlock>
</deadlock-list>

Best Answer

An easier way to understand a deadlock graph is taking that information you have and saving it as a .xdl file. That is the default deadlock graph file extension. Then you can open it with either SSMS or SQL Sentry Explorer. With the graph you will have a quicker understanding on what happened, which process was waiting, which one was blocked, which resource they were waiting for, etc.

The using some queries you can grab even more information, find the root cause of the deadlock and try to solve it. Replace the Object_id and hobt_id values on the were clause with yours. On this cases some of the objects are indexes and tables, modify at will for other objects.

And finally, most important, do a quick search on your prefered search engine on how to interpret deadlock graphs, grab one of the tutorials and use yours to learn how to do it.

SELECT  o.name
      , i.name
      , o.id
FROM    sysobjects AS o
        JOIN sysindexes AS i
            ON o.id = i.id
WHERE   o.id = your_object_id_value_here

SELECT  OBJECT_NAME(your_object_id_value_here)

SELECT  hobt_id
      , OBJECT_NAME(p.[your_object_id_value_here])
      , index_id
      , object_id
FROM    sys.partitions p
WHERE   hobt_id IN (your_hobt_id_value_here)

SELECT  OBJECT_NAME(S.[object_id]) AS [OBJECT NAME]
      , I.[name] AS [INDEX NAME]
      , user_seeks
      , user_scans
      , user_lookups
      , user_updates
FROM    sys.dm_db_index_usage_stats AS S
        INNER JOIN sys.indexes AS I
            ON I.[object_id] = S.[object_id]
               AND I.index_id = S.index_id
WHERE   OBJECTPROPERTY(S.[object_id], 'IsUserTable') = 1
        AND I.[object_id] = your_object_id_value_here

Here is the deadlock graph, in red the object_id and hobt_id values:

Deadlock graph from SSMS

PS: if you don't have SQL Sentry Explorer installed, stop reading and go install the tool, is free and will make your life easier.

Related Question