Understanding Deadlock graph of sql server

Question

I need some little help to understand why a specific deadlock is happening on a specific table of my database. I know a very little of deadlocks, specially deadlocks on a same table.

Could you give some help with this deadlock? I'm not searching a specific solution, I just only want to know why this deadlock is happening:

<deadlock-list>
    <deadlock victim="process4aa5b88">
        <process-list>
            <process id="process4aa5b88" taskpriority="0" logused="0" waitresource="PAGE: 14:1:6535" 
            waittime="4912" ownerId="260658" transactionname="UPDATE" lasttranstarted="2015-02-10T09:35:10.040" 
            XDES="0x8006bb70" lockMode="U" schedulerid="8" kpid="2804" status="suspended" spid="221" sbid="0" ecid="5" 
            priority="0" trancount="0" lastbatchstarted="2015-02-10T09:35:09.993" 
            lastbatchcompleted="2015-02-10T09:35:09.993" clientapp=".Net SqlClient Data Provider" hostname="ANP-APP" 
            hostpid="6728" isolationlevel="read committed (2)" xactid="260658" currentdb="14" lockTimeout="4294967295" 
            clientoption1="671088672" clientoption2="128056">
                <executionStack>
                    <frame procname="adhoc" line="1" stmtstart="2" sqlhandle="0x02000000c7f3e035aa74a68d308785ac6386d1ee4b1f924e">  
                    UPDATE ic_seguimiento_contenedor   SET nave_id = M.id_interno, nave_nombre=M.campo2   FROM mantenedor_general M   WHERE ic_seguimiento_contenedor.nave_id = 0   AND ic_seguimiento_contenedor.estado_cod&lt;20 AND M.id_empresa=ic_seguimiento_contenedor.id_empresa AND ic_seguimiento_contenedor.id_empresa=105 AND M.mantenedor=&apos;mant_nave&apos;   AND ltrim(rtrim(M.campo1))=ltrim(rtrim(ic_seguimiento_contenedor.nave_cod)) AND ic_seguimiento_contenedor.key_negocio =&apos;53E010566725403&apos;     </frame>
                </executionStack>
                <inputbuf>      </inputbuf>
            </process>
            <process id="process4a8b288" taskpriority="0" logused="0" waitresource="PAGE: 14:1:6535" 
            waittime="6287" ownerId="260658" transactionname="UPDATE" lasttranstarted="2015-02-10T09:35:10.040" 
            XDES="0xbf9597b0" lockMode="U" schedulerid="7" kpid="6952" status="suspended" spid="221" sbid="0" ecid="7" 
            priority="0" trancount="0" lastbatchstarted="2015-02-10T09:35:09.993" 
            lastbatchcompleted="2015-02-10T09:35:09.993" clientapp=".Net SqlClient Data Provider" hostname="ANP-APP" 
            hostpid="6728" isolationlevel="read committed (2)" xactid="260658" currentdb="14" lockTimeout="4294967295" 
            clientoption1="671088672" clientoption2="128056">
                <executionStack>
                    <frame procname="adhoc" line="1" stmtstart="2" sqlhandle="0x02000000c7f3e035aa74a68d308785ac6386d1ee4b1f924e">  
                    UPDATE ic_seguimiento_contenedor   SET nave_id = M.id_interno, nave_nombre=M.campo2   FROM mantenedor_general M   WHERE ic_seguimiento_contenedor.nave_id = 0   AND ic_seguimiento_contenedor.estado_cod&lt;20 AND M.id_empresa=ic_seguimiento_contenedor.id_empresa AND ic_seguimiento_contenedor.id_empresa=105 AND M.mantenedor=&apos;mant_nave&apos;   AND ltrim(rtrim(M.campo1))=ltrim(rtrim(ic_seguimiento_contenedor.nave_cod)) AND ic_seguimiento_contenedor.key_negocio =&apos;53E010566725403&apos;     </frame>
                </executionStack>
                <inputbuf>      </inputbuf>
            </process>
            <process id="process463d948" taskpriority="0" logused="0" waitresource="PAGE: 14:1:6503" 
            waittime="4912" ownerId="260657" transactionname="UPDATE" lasttranstarted="2015-02-10T09:35:10.040" 
            XDES="0xb4595850" lockMode="U" schedulerid="4" kpid="2060" status="suspended" spid="219" sbid="0" ecid="3" 
            priority="0" trancount="0" lastbatchstarted="2015-02-10T09:35:09.993" 
            lastbatchcompleted="2015-02-10T09:35:09.993" clientapp=".Net SqlClient Data Provider" hostname="ANP-APP" 
            hostpid="6728" isolationlevel="read committed (2)" xactid="260657" currentdb="14" lockTimeout="4294967295" 
            clientoption1="671088672" clientoption2="128056">
                <executionStack>
                    <frame procname="adhoc" line="1" stmtstart="2" sqlhandle="0x02000000c7f3e035aa74a68d308785ac6386d1ee4b1f924e">  
                    UPDATE ic_seguimiento_contenedor   SET nave_id = M.id_interno, nave_nombre=M.campo2   FROM mantenedor_general M   WHERE ic_seguimiento_contenedor.nave_id = 0   AND ic_seguimiento_contenedor.estado_cod&lt;20 AND M.id_empresa=ic_seguimiento_contenedor.id_empresa AND ic_seguimiento_contenedor.id_empresa=105 AND M.mantenedor=&apos;mant_nave&apos;   AND ltrim(rtrim(M.campo1))=ltrim(rtrim(ic_seguimiento_contenedor.nave_cod)) AND ic_seguimiento_contenedor.key_negocio =&apos;53E010566725403&apos;     </frame>
                </executionStack>
                <inputbuf>      </inputbuf>
            </process>
            <process id="process449ebc8" taskpriority="0" logused="0" waitresource="PAGE: 14:1:6503" 
            waittime="6287" ownerId="260657" transactionname="UPDATE" lasttranstarted="2015-02-10T09:35:10.040" 
            XDES="0x80007a70" lockMode="U" schedulerid="1" kpid="6936" status="suspended" spid="219" sbid="0" ecid="5" 
            priority="0" trancount="0" lastbatchstarted="2015-02-10T09:35:09.993" 
            lastbatchcompleted="2015-02-10T09:35:09.993" clientapp=".Net SqlClient Data Provider" hostname="ANP-APP" 
            hostpid="6728" isolationlevel="read committed (2)" xactid="260657" currentdb="14" lockTimeout="4294967295" 
            clientoption1="671088672" clientoption2="128056">
                <executionStack>
                    <frame procname="adhoc" line="1" stmtstart="2" sqlhandle="0x02000000c7f3e035aa74a68d308785ac6386d1ee4b1f924e">  
                    UPDATE ic_seguimiento_contenedor   SET nave_id = M.id_interno, nave_nombre=M.campo2   FROM mantenedor_general M   WHERE ic_seguimiento_contenedor.nave_id = 0   AND ic_seguimiento_contenedor.estado_cod&lt;20 AND M.id_empresa=ic_seguimiento_contenedor.id_empresa AND ic_seguimiento_contenedor.id_empresa=105 AND M.mantenedor=&apos;mant_nave&apos;   AND ltrim(rtrim(M.campo1))=ltrim(rtrim(ic_seguimiento_contenedor.nave_cod)) AND ic_seguimiento_contenedor.key_negocio =&apos;53E010566725403&apos;     </frame>
                </executionStack>
                <inputbuf>      </inputbuf>
            </process>

            <process id="process4a8b048" taskpriority="0" logused="10000" waittime="4905" schedulerid="7" kpid="6324" 
            status="suspended" spid="219" sbid="0" ecid="0" priority="0" trancount="2" 
            lastbatchstarted="2015-02-10T09:35:09.993" lastbatchcompleted="2015-02-10T09:35:09.993" 
            clientapp=".Net SqlClient Data Provider" hostname="ANP-APP" hostpid="6728" loginname="sa" 
            isolationlevel="read committed (2)" xactid="260657" currentdb="14" lockTimeout="4294967295" 
            clientoption1="671088672" clientoption2="128056">
                <executionStack>
                    <frame procname="adhoc" line="1" stmtstart="2" sqlhandle="0x02000000c7f3e035aa74a68d308785ac6386d1ee4b1f924e">  
                    UPDATE ic_seguimiento_contenedor   SET nave_id = M.id_interno, nave_nombre=M.campo2   FROM mantenedor_general M   WHERE ic_seguimiento_contenedor.nave_id = 0   AND ic_seguimiento_contenedor.estado_cod&lt;20 AND M.id_empresa=ic_seguimiento_contenedor.id_empresa AND ic_seguimiento_contenedor.id_empresa=105 AND M.mantenedor=&apos;mant_nave&apos;   AND ltrim(rtrim(M.campo1))=ltrim(rtrim(ic_seguimiento_contenedor.nave_cod)) AND ic_seguimiento_contenedor.key_negocio =&apos;53E010566725403&apos;     </frame>
                </executionStack>
                <inputbuf>   UPDATE ic_seguimiento_contenedor   SET nave_id = M.id_interno, nave_nombre=M.campo2   FROM mantenedor_general M   WHERE ic_seguimiento_contenedor.nave_id = 0   AND ic_seguimiento_contenedor.estado_cod&lt;20 AND M.id_empresa=ic_seguimiento_contenedor.id_empresa AND ic_seguimiento_contenedor.id_empresa=105 AND M.mantenedor=&apos;mant_nave&apos;   AND ltrim(rtrim(M.campo1))=ltrim(rtrim(ic_seguimiento_contenedor.nave_cod)) AND ic_seguimiento_contenedor.key_negocio =&apos;53E010566725403&apos;    </inputbuf>
            </process>
        </process-list>
        <resource-list>
            <pagelock fileid="1" pageid="6535" dbid="14" objectname="icomexvi_dys.dbo.ic_seguimiento_contenedor" id="lock95e1e800" mode="U" associatedObjectId="72057594108248064">
                <owner-list/>
                <waiter-list>
                    <waiter id="process4aa5b88" mode="U" requestType="wait"/>
                </waiter-list>
            </pagelock>
            <pagelock fileid="1" pageid="6535" dbid="14" objectname="icomexvi_dys.dbo.ic_seguimiento_contenedor" id="lock95e1e800" mode="U" associatedObjectId="72057594108248064">
                <owner-list>
                    <owner id="process4a8b048" mode="U"/>
                </owner-list>
                <waiter-list>
                    <waiter id="process4a8b288" mode="U" requestType="wait"/>
                </waiter-list>
            </pagelock>
            <pagelock fileid="1" pageid="6503" dbid="14" objectname="icomexvi_dys.dbo.ic_seguimiento_contenedor" id="lock96c23080" mode="U" associatedObjectId="72057594108248064">
                <owner-list/>
                <waiter-list>
                    <waiter id="process463d948" mode="U" requestType="wait"/>
                </waiter-list>
            </pagelock>
            <pagelock fileid="1" pageid="6503" dbid="14" objectname="icomexvi_dys.dbo.ic_seguimiento_contenedor" id="lock96c23080" mode="U" associatedObjectId="72057594108248064">
                <owner-list>
                    <owner id="process4aa5b88" mode="U"/>
                </owner-list>
                <waiter-list>
                    <waiter id="process449ebc8" mode="U" requestType="wait"/>
                </waiter-list>
            </pagelock>
            <exchangeEvent id="Pipec6a2eac0" WaitType="e_waitPipeGetRow" nodeId="4">
                <owner-list>
                    <owner id="process463d948"/>
                </owner-list>
                <waiter-list>
                    <waiter id="process4a8b048"/>
                </waiter-list>
            </exchangeEvent>
        </resource-list>
    </deadlock>
</deadlock-list>

I very appreciate your help! Thanks in advance

Answer 1

This does not answer your question but if you want to become aware of deadlocks in real-time then you can create an alert that calls a sp that sends an email to sql operators. It pretty straightforward.

1. Create a table to hold deadlock events.

   CREATE TABLE [dbo].[DeadlockEvents]( [AlertTime] [datetime] NULL, [DeadlockGraph] [xml] NULL ) ON [PRIMARY]

2. Create a SQL Agent Job with the following exec sql as step 1.

   DECLARE @xml XML; SELECT @xml=N'$(ESCAPE_SQUOTE(WMI(TextData)))'

   INSERT INTO ILMDW.dbo.DeadlockEvents (AlertTime, DeadlockGraph) VALUES (getdate(), N'$(ESCAPE_SQUOTE(WMI(TextData)))')

   EXEC ILMDW.dbo.MAINTENANCE_Deadlock_Graph @xml

3. Create a SQL Server Management/Alert configured as follows.

4. Then have the sp send emails to you or othere when a deadlock occurs -->

   ALTER PROC [dbo].[MAINTENANCE_Deadlock_Graph] @xml XML
   AS
   BEGIN
   SET NOCOUNT ON;

   DECLARE @body VARCHAR(MAX)
   CREATE TABLE #victim_list(process_id VARCHAR(100))
   CREATE TABLE #processdetails
   (
   id VARCHAR(100),
   taskpriority VARCHAR(100),
   logused VARCHAR(100),
   waitresource VARCHAR(100),
   waittime VARCHAR(100),
   ownerId VARCHAR(100),
   transactionname VARCHAR(100),
   lasttranstarted VARCHAR(100),
   XDES VARCHAR(100),
   lockMode VARCHAR(100),
   schedulerid VARCHAR(100),
   kpid VARCHAR(100),
   status VARCHAR(100),
   spid VARCHAR(100),
   sbid VARCHAR(100),
   ecid VARCHAR(100),
   priority VARCHAR(100),
   trancount VARCHAR(100),
   lastbatchstarted VARCHAR(100),
   lastbatchcompleted VARCHAR(100),
   clientapp VARCHAR(100),
   hostname VARCHAR(100),
   hostpid VARCHAR(100),
   loginname VARCHAR(100),
   isolationlevel VARCHAR(100),
   xactid VARCHAR(100),
   currentdb VARCHAR(100),
   lockTimeout VARCHAR(100),
   clientoption1 VARCHAR(100),
   clientoption2 VARCHAR(100)
   )
   CREATE TABLE #frame_details
   (
   id VARCHAR(100),
   procname VARCHAR(100),
   line VARCHAR(100),
   stmtstart VARCHAR(100),
   sqlhandle VARCHAR(100)
   )
   CREATE TABLE #frame_values
   (
   id VARCHAR(100),
   frame VARCHAR(max)
   )
   CREATE TABLE #input_buffer
   (
   id VARCHAR(100),
   inputbuf VARCHAR(max)
   )
   CREATE TABLE #resource_details_keylock
   (
   hobtid VARCHAR(100),
   dbid VARCHAR(100),
   objectname VARCHAR(100),
   indexname VARCHAR(100),
   lock_id VARCHAR(100),
   mode VARCHAR(100),
   associatedObjectId VARCHAR(100),
   owner_id VARCHAR(100),
   owner_mode VARCHAR(100),
   waiter_id VARCHAR(100),
   waiter_mode VARCHAR(100),
   waiter_requestType VARCHAR(100)
   )

   CREATE TABLE #resource_details_objectlock
   (
   lockpartition VARCHAR(100),
   objid VARCHAR(100),
   subresource VARCHAR(100),
   dbid VARCHAR(100),
   objectname VARCHAR(100),
   lock_id VARCHAR(100),
   mode VARCHAR(100),
   associatedObjectId VARCHAR(100),
   owner_id VARCHAR(100),
   owner_mode VARCHAR(100),
   waiter_id VARCHAR(100),
   waiter_mode VARCHAR(100),
   waiter_requestType VARCHAR(100)
   )

   CREATE TABLE #resource_details_databaselock
   (
   subresource VARCHAR(100),
   dbid VARCHAR(100),
   dbname VARCHAR(100),
   lock_id VARCHAR(100),
   mode VARCHAR(100),
   owner_id VARCHAR(100),
   owner_mode VARCHAR(100),
   waiter_id VARCHAR(100),
   waiter_mode VARCHAR(100),
   waiter_requestType VARCHAR(100)
   )

   CREATE TABLE #resource_details_exchangeEvent
   (
   id VARCHAR(100),
   waitType VARCHAR(100),
   nodeId VARCHAR(100),
   owner_id VARCHAR(100),
   owner_mode VARCHAR(100),
   waiter_id VARCHAR(100),
   waiter_mode VARCHAR(100),
   waiter_requestType VARCHAR(100)
   )

   CREATE TABLE #resource_details_metadatalock
   (
   subresource VARCHAR(100),
   classid VARCHAR(100),
   dbid VARCHAR(100),
   dbname VARCHAR(100),
   lock_id VARCHAR(100),
   mode VARCHAR(100),
   owner_id VARCHAR(100),
   owner_mode VARCHAR(100),
   waiter_id VARCHAR(100),
   waiter_mode VARCHAR(100),
   waiter_requestType VARCHAR(100)
   )

   CREATE TABLE #resource_details_pagelock
   (
   fileid VARCHAR(100),
   pageid VARCHAR(100),
   dbid VARCHAR(100),
   dbname VARCHAR(100),
   objectname VARCHAR(100),
   lock_id VARCHAR(100),
   mode VARCHAR(100),
   associatedObjectId VARCHAR(100),
   owner_id VARCHAR(100),
   owner_mode VARCHAR(100),
   waiter_id VARCHAR(100),
   waiter_mode VARCHAR(100),
   waiter_requestType VARCHAR(100)
   )

   create table #resource_details_ridlock
   (
   pageid VARCHAR(100),
   dbid VARCHAR(100),
   dbname VARCHAR(100),
   objectname VARCHAR(100),
   lock_id VARCHAR(100),
   mode VARCHAR(100),
   associatedObjectId VARCHAR(100),
   owner_id VARCHAR(100),
   owner_mode VARCHAR(100),
   waiter_id VARCHAR(100),
   waiter_mode VARCHAR(100),
   waiter_requestType VARCHAR(100)
   )

   INSERT INTO #victim_list
   SELECT dl.n.value('@victim','VARCHAR(100)')
   FROM @xml.nodes('TextData/deadlock-list/deadlock') dl(n)

   INSERT INTO #processdetails
   select dl.n.value('@id1','VARCHAR(100)') AS id,
   dl.n.value('@taskpriority1','VARCHAR(100)') AS taskpriority,
   dl.n.value('@logused1','VARCHAR(100)') AS logused,
   dl.n.value('@waitresource1','VARCHAR(100)') AS waitresource,
   dl.n.value('@waittime1','VARCHAR(100)') AS waittime,
   dl.n.value('@ownerId1','VARCHAR(100)') AS ownerId,
   dl.n.value('@transactionname1','VARCHAR(100)') AS transactionname,
   dl.n.value('@lasttranstarted1','VARCHAR(100)') AS lasttranstarted,
   dl.n.value('@XDES1','VARCHAR(100)') AS XDES,
   dl.n.value('@lockMode1','VARCHAR(100)') AS lockMode,
   dl.n.value('@schedulerid1','VARCHAR(100)') AS schedulerid,
   dl.n.value('@kpid1','VARCHAR(100)') AS kpid,
   dl.n.value('@status1','VARCHAR(100)') AS status,
   dl.n.value('@spid1','VARCHAR(100)') AS spid,
   dl.n.value('@sbid1','VARCHAR(100)') AS sbid,
   dl.n.value('@ecid1','VARCHAR(100)') AS ecid,
   dl.n.value('@priority1','VARCHAR(100)') AS priority,
   dl.n.value('@trancount1','VARCHAR(100)') AS trancount,
   dl.n.value('@lastbatchstarted1','VARCHAR(100)') AS lastbatchstarted,
   dl.n.value('@lastbatchcompleted1','VARCHAR(100)') AS lastbatchcompleted,
   dl.n.value('@clientapp1','VARCHAR(100)') AS clientapp,
   dl.n.value('@hostname1','VARCHAR(100)') AS hostname,
   dl.n.value('@hostpid1','VARCHAR(100)') AS hostpid,
   dl.n.value('@loginname1','VARCHAR(100)') AS loginname,
   dl.n.value('@isolationlevel1','VARCHAR(100)') AS isolationlevel,
   dl.n.value('@xactid1','VARCHAR(100)') AS xactid,
   dl.n.value('@currentdb1','VARCHAR(100)') AS currentdb,
   dl.n.value('@lockTimeout1','VARCHAR(100)') AS lockTimeout,
   dl.n.value('@clientoption11','VARCHAR(100)') AS clientoption1,
   dl.n.value('@clientoption21','VARCHAR(100)') AS clientoption2
   FROM @xml.nodes('//TextData/deadlock-list/deadlock/process-list/process') dl(n)

   INSERT INTO #frame_details
   SELECT dl.n.value('../../@id1','VARCHAR(100)') AS id,
   dl.n.value('@procname1','VARCHAR(100)') AS procname,
   dl.n.value('@line1','VARCHAR(100)') AS line,
   dl.n.value('@stmtstart1','VARCHAR(100)') AS stmtstart,
   dl.n.value('@sqlhandle1','VARCHAR(100)') AS sqlhandle
   FROM @xml.nodes('//TextData/deadlock-list/deadlock/process-list/process/executionStack/frame') dl(n)

   INSERT INTO #frame_values
   SELECT dl.n.value('../@id1','VARCHAR(100)') AS id,
   dl.n.value('frame1','VARCHAR(100)') AS frame
   FROM @xml.nodes('//TextData/deadlock-list/deadlock/process-list/process/executionStack') dl(n)

   INSERT INTO #input_buffer
   SELECT dl.n.value('@id1','VARCHAR(100)') AS id,
   dl.n.value('inputbuf1','VARCHAR(100)') AS inputbuf
   FROM @xml.nodes('//TextData/deadlock-list/deadlock/process-list/process') dl(n)

   INSERT INTO #resource_details_keylock
   SELECT dl.n.value('@hobtid1','VARCHAR(100)') AS hobtid,
   dl.n.value('@dbid1','VARCHAR(100)') AS dbid,
   dl.n.value('@objectname1','VARCHAR(100)') AS objectname,
   dl.n.value('@indexname1','VARCHAR(100)') AS indexname,
   dl.n.value('@id1','VARCHAR(100)') AS lock_id,
   dl.n.value('@mode1','VARCHAR(100)') AS mode,
   dl.n.value('@associatedObjectId1','VARCHAR(100)') AS associatedObjectId,
   dl.n.value('(owner-list/owner)1/@id','VARCHAR(100)') AS owner_id,
   dl.n.value('(owner-list/owner)1/@mode','VARCHAR(100)') AS owner_mode,
   dl.n.value('(waiter-list/waiter)1/@id','VARCHAR(100)') AS waiter_id,
   dl.n.value('(waiter-list/waiter)1/@mode','VARCHAR(100)') AS waiter_mode,
   dl.n.value('(waiter-list/waiter)1/@requestType','VARCHAR(100)') AS waiter_requestType
   FROM @xml.nodes('//TextData/deadlock-list/deadlock/resource-list/keylock') dl(n)

   INSERT INTO #resource_details_objectlock
   SELECT dl.n.value('@lockpartition1','VARCHAR(100)') AS lockpartition,
   dl.n.value('@objid1','VARCHAR(100)') AS objid,
   dl.n.value('@subresource1','VARCHAR(100)') AS subresource,
   dl.n.value('@dbid1','VARCHAR(100)') AS dbid,
   dl.n.value('@objectname1','VARCHAR(100)') AS objectname,
   dl.n.value('@id1','VARCHAR(100)') AS lock_id,
   dl.n.value('@mode1','VARCHAR(100)') AS mode,
   dl.n.value('@associatedObjectId1','VARCHAR(100)') AS associatedObjectId,
   dl.n.value('(owner-list/owner)1/@id','VARCHAR(100)') AS owner_id,
   dl.n.value('(owner-list/owner)1/@mode','VARCHAR(100)') AS owner_mode,
   dl.n.value('(waiter-list/waiter)1/@id','VARCHAR(100)') AS waiter_id,
   dl.n.value('(waiter-list/waiter)1/@mode','VARCHAR(100)') AS waiter_mode,
   dl.n.value('(waiter-list/waiter)1/@requestType','VARCHAR(100)') AS waiter_requestType
   FROM @xml.nodes('//TextData/deadlock-list/deadlock/resource-list/objectlock') dl(n)

   INSERT INTO #resource_details_databaselock
   SELECT dl.n.value('@subresource1','VARCHAR(100)') AS subresource,
   dl.n.value('@dbid1','VARCHAR(100)') AS dbid,
   db_name(dl.n.value('@dbid1','VARCHAR(100)')) AS dbname,
   dl.n.value('@id1','VARCHAR(100)') AS lock_id,
   dl.n.value('@mode1','VARCHAR(100)') AS mode,
   dl.n.value('(owner-list/owner)1/@id','VARCHAR(100)') AS owner_id,
   dl.n.value('(owner-list/owner)1/@mode','VARCHAR(100)') AS owner_mode,
   dl.n.value('(waiter-list/waiter)1/@id','VARCHAR(100)') AS waiter_id,
   dl.n.value('(waiter-list/waiter)1/@mode','VARCHAR(100)') AS waiter_mode,
   dl.n.value('(waiter-list/waiter)1/@requestType','VARCHAR(100)') AS waiter_requestType
   FROM @xml.nodes('//TextData/deadlock-list/deadlock/resource-list/databaselock') dl(n)

   INSERT INTO #resource_details_exchangeEvent
   SELECT dl.n.value('@id1','VARCHAR(100)') AS lock_id,
   dl.n.value('@waitType1','VARCHAR(100)') AS waitType,
   dl.n.value('@nodeId1','VARCHAR(100)') AS nodeId,
   dl.n.value('(owner-list/owner)1/@id','VARCHAR(100)') AS owner_id,
   dl.n.value('(owner-list/owner)1/@mode','VARCHAR(100)') AS owner_mode,
   dl.n.value('(waiter-list/waiter)1/@id','VARCHAR(100)') AS waiter_id,
   dl.n.value('(waiter-list/waiter)1/@mode','VARCHAR(100)') AS waiter_mode,
   dl.n.value('(waiter-list/waiter)1/@requestType','VARCHAR(100)') AS waiter_requestType
   FROM @xml.nodes('//TextData/deadlock-list/deadlock/resource-list/exchangeEvent') dl(n)

   INSERT INTO #resource_details_metadatalock
   SELECT dl.n.value('@subresource1','VARCHAR(100)') AS subresource,
   dl.n.value('@classid1','VARCHAR(100)') AS classid,
   dl.n.value('@dbid1','VARCHAR(100)') AS dbid,
   db_name(dl.n.value('@dbid1','VARCHAR(100)')) AS dbname,
   dl.n.value('@id1','VARCHAR(100)') AS lock_id,
   dl.n.value('@mode1','VARCHAR(100)') AS mode,
   dl.n.value('(owner-list/owner)1/@id','VARCHAR(100)') AS owner_id,
   dl.n.value('(owner-list/owner)1/@mode','VARCHAR(100)') AS owner_mode,
   dl.n.value('(waiter-list/waiter)1/@id','VARCHAR(100)') AS waiter_id,
   dl.n.value('(waiter-list/waiter)1/@mode','VARCHAR(100)') AS waiter_mode,
   dl.n.value('(waiter-list/waiter)1/@requestType','VARCHAR(100)') AS waiter_requestType
   FROM @xml.nodes('//TextData/deadlock-list/deadlock/resource-list/metadatalock') dl(n)

   INSERT INTO #resource_details_pagelock
   SELECT dl.n.value('@fileid1','VARCHAR(100)') AS fileid,
   dl.n.value('@pageid1','VARCHAR(100)') AS pageid,
   dl.n.value('@dbid1','VARCHAR(100)') AS dbid,
   db_name(dl.n.value('@dbid1','VARCHAR(100)')) AS dbname,
   dl.n.value('@objectname1','VARCHAR(100)') AS objectname,
   dl.n.value('@id1','VARCHAR(100)') AS lock_id,
   dl.n.value('@mode1','VARCHAR(100)') AS mode,
   dl.n.value('@associatedObjectId1','VARCHAR(100)') AS associatedObjectId,
   dl.n.value('(owner-list/owner)1/@id','VARCHAR(100)') AS owner_id,
   dl.n.value('(owner-list/owner)1/@mode','VARCHAR(100)') AS owner_mode,
   dl.n.value('(waiter-list/waiter)1/@id','VARCHAR(100)') AS waiter_id,
   dl.n.value('(waiter-list/waiter)1/@mode','VARCHAR(100)') AS waiter_mode,
   dl.n.value('(waiter-list/waiter)1/@requestType','VARCHAR(100)') AS waiter_requestType
   FROM @xml.nodes('//TextData/deadlock-list/deadlock/resource-list/pagelock') dl(n)

   INSERT INTO #resource_details_ridlock
   SELECT dl.n.value('@pageid1','VARCHAR(100)') AS pageid,
   dl.n.value('@dbid1','VARCHAR(100)') AS dbid,
   db_name(dl.n.value('@dbid1','VARCHAR(100)')) AS dbname,
   dl.n.value('@objectname1','VARCHAR(100)') AS objectname,
   dl.n.value('@id1','VARCHAR(100)') AS lock_id,
   dl.n.value('@mode1','VARCHAR(100)') AS mode,
   dl.n.value('@associatedObjectId1','VARCHAR(100)') AS associatedObjectId,
   dl.n.value('(owner-list/owner)1/@id','VARCHAR(100)') AS owner_id,
   dl.n.value('(owner-list/owner)1/@mode','VARCHAR(100)') AS owner_mode,
   dl.n.value('(waiter-list/waiter)1/@id','VARCHAR(100)') AS waiter_id,
   dl.n.value('(waiter-list/waiter)1/@mode','VARCHAR(100)') AS waiter_mode,
   dl.n.value('(waiter-list/waiter)1/@requestType','VARCHAR(100)') AS waiter_requestType
   FROM @xml.nodes('//TextData/deadlock-list/deadlock/resource-list/ridlock') dl(n)

   SELECT @body='Process id(Victim)'
   SELECT @body=@body+''+isnull(process_id,'')+'' from #victim_list
   SELECT @body=@body+'
   
   '

   SELECT @body=@body+'Process Details:
   
   
   Process id
   DB_Name
   procname
   waittime
   lockMode
   trancount
   clientapp
   hostname
   loginname
   frame
   inputbuf
   '

   SELECT DISTINCT isnull(pd.id,'') as [id],
   isnull(db_name(pd.currentdb),'') as [currentdb],
   isnull(fd.procname,'') as [procname],
   isnull(pd.waittime,'') as [waittime],
   isnull(pd.lockMode,'') as [lockMode],
   isnull(pd.trancount,'') as [trancount],
   isnull(pd.clientapp,'') as [clientapp],
   isnull(pd.hostname,'') as [hostname],
   isnull(pd.loginname,'') as [loginname],
   isnull(fv.frame,'') as [frame],
   isnull(ib.inputbuf,'') as [inputbuf]
   into #p_details_temp
   from #processdetails pd
   left join #frame_details fd on pd.id=fd.id
   left join #frame_values fv on fd.id=fv.id
   left join #input_buffer ib on fv.id=ib.id

   SELECT @body=@body+
   ''+id+''+
   ''+currentdb+''+
   ''+procname+''+
   ''+waittime+''+
   ''+lockMode+''+
   ''+trancount+''+
   ''+clientapp+''+
   ''+hostname+''+
   ''+loginname+''+
   ''+frame+''+
   ''+inputbuf+''
   FROM #p_details_temp
   SELECT @body=@body+'
   
   '

   IF EXISTS (SELECT TOP 1 * FROM #resource_details_keylock) BEGIN
   SELECT @body=@body+'Keylock:
   '+
   'hobtid,'+
   'dbid,'+
   'objectname,'+
   'indexname,'+
   'lock_id,'+
   'mode,'+
   'associatedObjectId,'+
   'owner_id,'+
   'owner_mode,'+
   'waiter_id,'+
   'waiter_mode,'+
   'waiter_requestType,'+
   ''
   select @body=@body+''+
   ''+isnull(hobtid,'')+''+
   ''+isnull(dbid,'')+''+
   ''+isnull(objectname,'')+''+
   ''+isnull(indexname,'')+''+
   ''+isnull(lock_id,'')+''+
   ''+isnull(mode,'')+''+
   ''+isnull(associatedObjectId,'')+''+
   ''+isnull(owner_id,'')+''+
   ''+isnull(owner_mode,'')+''+
   ''+isnull(waiter_id,'')+''+
   ''+isnull(waiter_mode,'')+''+
   ''+isnull(waiter_requestType,'')+''+
   ''
   from #resource_details_keylock
   select @body=@body+'
   
   '
   END

   IF EXISTS (SELECT TOP 1 * FROM #resource_details_objectlock) BEGIN
   SELECT @body=@body+'ObjectLock:
   '+
   'lockpartition,'+
   'objid,'+
   'subresource,'+
   'dbid,'+
   'objectname,'+
   'lock_id,'+
   'mode,'+
   'associatedObjectId,'+
   'owner_id,'+
   'owner_mode,'+
   'waiter_id,'+
   'waiter_mode,'+
   'waiter_requestType,'+
   ''
   SELECT @body=@body+''+
   ''+isnull(lockpartition,'')+''+
   ''+isnull(objid,'')+''+
   ''+isnull(subresource,'')+''+
   ''+isnull(dbid,'')+''+
   ''+isnull(objectname,'')+''+
   ''+isnull(lock_id,'')+''+
   ''+isnull(mode,'')+''+
   ''+isnull(associatedObjectId,'')+''+
   ''+isnull(owner_id,'')+''+
   ''+isnull(owner_mode,'')+''+
   ''+isnull(waiter_id,'')+''+
   ''+isnull(waiter_mode,'')+''+
   ''+isnull(waiter_requestType,'')+''+
   ''
   FROM #resource_details_objectlock
   SELECT @body=@body+'
   
   '
   END

   IF EXISTS (SELECT TOP 1 * FROM #resource_details_databaselock) BEGIN

   SELECT @body=@body+'DatabaseLock:<br><table border="1"> <tr>'+   
    '<th>subresource,</th>'+  
    '<th>dbid,</th>'+  
    '<th>dbname,</th>'+  
    '<th>lock_id,</th>'+  
    '<th>mode,</th>'+  
    '<th>owner_id,</th>'+  
    '<th>owner_mode,</th>'+  
    '<th>waiter_id,</th>'+  
    '<th>waiter_mode,</th>'+  
    '<th>waiter_requestType,</th>'+  
    '</tr>'  
   SELECT @body=@body+'<tr>'+   
    '<td>'+isnull(subresource,'')+'</td>'+  
    '<td>'+isnull(dbid,'')+'</td>'+  
    '<td>'+isnull(dbname,'')+'</td>'+  
    '<td>'+isnull(lock_id,'')+'</td>'+  
    '<td>'+isnull(mode,'')+'</td>'+  
    '<td>'+isnull(owner_id,'')+'</td>'+  
    '<td>'+isnull(owner_mode,'')+'</td>'+  
    '<td>'+isnull(waiter_id,'')+'</td>'+  
    '<td>'+isnull(waiter_mode,'')+'</td>'+  
    '<td>'+isnull(waiter_requestType,'')+'</td>'+  
    '</tr>'  
   from #resource_details_databaselock   
   select @body=@body+'</table><br/><br/>'   
   

   END

   IF EXISTS (SELECT TOP 1 * FROM #resource_details_exchangeEvent) BEGIN
   SELECT @body=@body+'ExchangeEvent:
   '+
   'lock_id,'+
   'waitType,'+
   'nodeId,'+
   'owner_id,'+
   'owner_mode,'+
   'waiter_id,'+
   'waiter_mode,'+
   'waiter_requestType,'+
   ''
   SELECT @body=@body+''+
   ''+isnull(id,'')+''+
   ''+isnull(waitType,'')+''+
   ''+isnull(nodeId,'')+''+
   ''+isnull(owner_id,'')+''+
   ''+isnull(owner_mode,'')+''+
   ''+isnull(waiter_id,'')+''+
   ''+isnull(waiter_mode,'')+''+
   ''+isnull(waiter_requestType,'')+''+
   ''
   FROM #resource_details_exchangeEvent
   SELECT @body=@body+'
   
   '
   END

   IF EXISTS (SELECT TOP 1 * FROM #resource_details_metadatalock) BEGIN
   SELECT @body=@body+'MetadataLock:
   '+
   'subresource,'+
   'classid,'+
   'dbid,'+
   'dbname,'+
   'lock_id,'+
   'mode,'+
   'owner_id,'+
   'owner_mode,'+
   'waiter_id,'+
   'waiter_mode,'+
   'waiter_requestType,'+
   ''
   SELECT @body=@body+''+
   ''+isnull(subresource,'')+''+
   ''+isnull(classid,'')+''+
   ''+isnull(dbid,'')+''+
   ''+isnull(dbname,'')+''+
   ''+isnull(lock_id,'')+''+
   ''+isnull(mode,'')+''+
   ''+isnull(owner_id,'')+''+
   ''+isnull(owner_mode,'')+''+
   ''+isnull(waiter_id,'')+''+
   ''+isnull(waiter_mode,'')+''+
   ''+isnull(waiter_requestType,'')+''+
   ''
   FROM #resource_details_metadatalock
   SELECT @body=@body+'
   
   '
   END

   IF EXISTS (SELECT TOP 1 * FROM #resource_details_pagelock) BEGIN
   SELECT @body=@body+'PageLock:
   '+
   'fileid,'+
   'pageid,'+
   'dbid,'+
   'dbname,'+
   'objectname,'+
   'lock_id,'+
   'mode,'+
   'associatedObjectId,'+
   'owner_id,'+
   'owner_mode,'+
   'waiter_id,'+
   'waiter_mode,'+
   'waiter_requestType,'+
   ''
   SELECT @body=@body+''+
   ''+isnull(fileid,'')+''+
   ''+isnull(pageid,'')+''+
   ''+isnull(dbid,'')+''+
   ''+isnull(dbname,'')+''+
   ''+isnull(objectname,'')+''+
   ''+isnull(lock_id,'')+''+
   ''+isnull(mode,'')+''+
   ''+isnull(associatedObjectId,'')+''+
   ''+isnull(owner_id,'')+''+
   ''+isnull(owner_mode,'')+''+
   ''+isnull(waiter_id,'')+''+
   ''+isnull(waiter_mode,'')+''+
   ''+isnull(waiter_requestType,'')+''+
   ''
   FROM #resource_details_pagelock
   SELECT @body=@body+'
   
   '
   END

   IF EXISTS (SELECT TOP 1 * FROM #resource_details_ridlock) BEGIN
   SELECT @body=@body+'RidLock:
   '+
   'pageid,'+
   'dbid,'+
   'dbname,'+
   'objectname,'+
   'lock_id,'+
   'mode,'+
   'associatedObjectId,'+
   'owner_id,'+
   'owner_mode,'+
   'waiter_id,'+
   'waiter_mode,'+
   'waiter_requestType,'+
   ''
   SELECT @body=@body+''+
   ''+isnull(pageid,'')+''+
   ''+isnull(dbid,'')+''+
   ''+isnull(dbname,'')+''+
   ''+isnull(objectname,'')+''+
   ''+isnull(lock_id,'')+''+
   ''+isnull(mode,'')+''+
   ''+isnull(associatedObjectId,'')+''+
   ''+isnull(owner_id,'')+''+
   ''+isnull(owner_mode,'')+''+
   ''+isnull(waiter_id,'')+''+
   ''+isnull(waiter_mode,'')+''+
   ''+isnull(waiter_requestType,'')+''+
   ''
   FROM #resource_details_ridlock
   SELECT @body=@body+'
   
   '
   END

   SELECT @body=@body+'Original XML'+
   replace( replace( convert(VARCHAR(MAX),@xml), '<','<' ), '>','>' )+
   ''

   DECLARE @email_distribution_list VARCHAR(1024)
   SELECT @email_distribution_list = 'ross@ilearn.com;brad@ilearn.com;kenny@ilearn.com;spenser@ilearn.com'

   EXEC msdb.dbo.sp_send_dbmail
   @profile_name = 'SQL NOTIFICATIONS',
   @recipients=@email_distribution_list, @body = @body,
   @body_format='HTML',
   @subject = 'Alert! DeadLock Occurred On Server',
   @importance = 'High' ;

   DROP TABLE #victim_list
   DROP TABLE #processdetails
   DROP TABLE #frame_details
   DROP TABLE #frame_values
   DROP TABLE #input_buffer
   DROP TABLE #resource_details_databaselock
   DROP TABLE #resource_details_exchangeEvent
   DROP TABLE #resource_details_keylock
   DROP TABLE #resource_details_metadatalock
   DROP TABLE #resource_details_objectlock
   DROP TABLE #resource_details_pagelock
   DROP TABLE #resource_details_ridlock
   DROP TABLE #p_details_temp

   END

Answer 2

• These are the processes: process4aa5b88, process4a8b288, process463d948, process449ebc8 and process4a8b048.

  • Page 6535 is owned by process4a8b048 and waited by process4aa5b88 and process4a8b288.

  • Page 6503 is owned by process4aa5b88 and waited by process449ebc8 and process463d948.

  • Parallel exchange pipe Pipec6a2eac0 is owned by process463d948 and waited by process4a8b048.

The deadlock cycle is this:

1. process4aa5b88 waits page 6535, owned by process4a8b048
2. process4a8b048 waits exchange pipe owned by process463d948
3. process463d948 waits page 6503 owned by process4aa5b88

QED a cycle in the wait list => deadlock

Presence of parallelism and page granularity locks during scan is a clear indication of a missing index. Review the WHERE clause, make sure you have SARGable arguments. Read Index Design Basics and all linked chapters.