In a SQL XDL File, how do I read the waitresource attribute on process nodes which are deadlocking?_问答_开发者

On SQL Server 2005,

I'm getting a deadlock when updating two different keys in the same table.

note from below that these two waitresources have the same beginning part, but different ending parts.

waitresource="KEY: 6:72057594090487808 (d900ed5a6cc6)"

and

waitresource="KEY: 6:72057594090487808 (d900fb5261bb)"

These two keys are locking, and I need to figure out why.

The question:

If the values in parenthesis are different, why are the first half of the key's the same?

<deadlock-list>
 <deadlock victim="processffffffff8f5863e8">
  <process-list>
   <process id="processaf02f8" taskpriority="0" logused="0" waitresource="KEY: 6:72057594090487808 (d900fb5261bb)" waittime="2281" ownerId="1370264705" transactionname="user_transaction" lasttranstarted="2010-06-17T00:35:25.483" XDES="0x69453a70" lockMode="U" schedulerid="3" kpid="7624" status="suspended" spid="339" sbid="0" ecid="0" priority="0" transcount="2" lastbatchstarted="2010-06-17T00:35:25.483" lastbatchcompleted="2010-06-17T00:35:25.483" clientapp=".Net SqlClient Data Provider" hostname="RISKBBG_VM" hostpid="5848" loginname="RiskOpt" isolationlevel="read committed (2)" xactid="1370264705" currentdb="6" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
    <executionStack>
     <frame procname="MKP_RISKDB.dbo.MarketDataCurrentRtUpload" line="14" stmtstart="840" stmtend="1220" sqlhandle="0x03000600005f9d24c8878f00849d00000100000000000000">
UPDATE c WITH (ROWLOCK) SET LastUpdate = t.LastUpdate, Value = t.Value, Source = t.Source 
        FROM MarketDataCurrent c INNER JOIN #TEMPTABLE2 t ON c.MDID = t.mdid;

        -- Insert new MDID     </frame>
     <frame procname="adhoc" line="1" sqlhandle="0x010006004a58132228bf8d73000000000000000000000000">
MarketDataCurrentBlbgRtUpload     </frame>
    </executionStack>
    <inputbuf>
MarketDataCurrentBlbgRtUpload    </inputbuf>
   </process>
   <process id="processffffffff8f5863e8" taskpriority="0" logused="0" waitresource="KEY: 6:72057594090487808 (d900ed5a6cc6)" waittime="2281" ownerId="1370264646" transactionname="user_transaction" lasttranstarted=开发者_开发百科"2010-06-17T00:35:25.450" XDES="0x1cb72be8" lockMode="U" schedulerid="5" kpid="1880" status="suspended" spid="287" sbid="0" ecid="0" priority="0" transcount="2" lastbatchstarted="2010-06-17T00:35:25.450" lastbatchcompleted="2010-06-17T00:35:25.450" clientapp=".Net SqlClient Data Provider" hostname="RISKAPPS_VM" hostpid="1424" loginname="RiskOpt" isolationlevel="read committed (2)" xactid="1370264646" currentdb="6" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
    <executionStack>
     <frame procname="MKP_RISKDB.dbo.MarketDataCurrent_BulkUpload" line="28" stmtstart="1062" stmtend="1720" sqlhandle="0x03000600a28e5e4ef4fd8e00849d00000100000000000000">
UPDATE c WITH (ROWLOCK) SET LastUpdate = getdate(), Value = t.Value, Source = @source 
FROM MarketDataCurrent c INNER JOIN #MDTUP t ON c.MDID = t.mdid
WHERE c.lastUpdate &lt; @updateTime
and   c.mdid not in (select mdid from MarketData where BloombergTicker is not null and PriceSource like &apos;Live.%&apos;)
and   c.value &lt;&gt; t.value     </frame>
     <frame procname="adhoc" line="1" stmtstart="88" sqlhandle="0x01000600c1653d0598706ca7000000000000000000000000">
exec MarketDataCurrent_BulkUpload @clearBefore, @source     </frame>
     <frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">
unknown     </frame>
    </executionStack>
    <inputbuf>
(@clearBefore datetime,@source nvarchar(10))exec MarketDataCurrent_BulkUpload @clearBefore, @source    </inputbuf>
   </process>
  </process-list>
  <resource-list>
   <keylock hobtid="72057594090487808" dbid="6" objectname="MKP_RISKDB.dbo.MarketDataCurrent" indexname="PK_MarketDataCurrent" id="lock64ac7940" mode="U" associatedObjectId="72057594090487808">
    <owner-list>
     <owner id="processffffffff8f5863e8" mode="U"/>
    </owner-list>
    <waiter-list>
     <waiter id="processaf02f8" mode="U" requestType="wait"/>
    </waiter-list>
   </keylock>
   <keylock hobtid="72057594090487808" dbid="6" objectname="MKP_RISKDB.dbo.MarketDataCurrent" indexname="PK_MarketDataCurrent" id="lockffffffffb8d2dd40" mode="U" associatedObjectId="72057594090487808">
    <owner-list>
     <owner id="processaf02f8" mode="U"/>
    </owner-list>
    <waiter-list>
     <waiter id="processffffffff8f5863e8" mode="U" requestType="wait"/>
    </waiter-list>
   </keylock>
  </resource-list>
 </deadlock>
</deadlock-list>

The 6:72057594090487808 is just the database_id:hobtid as can be seen further down in the XDL.

<keylock hobtid="72057594090487808" dbid="6"  
objectname="MKP_RISKDB.dbo.MarketDataCurrent" 
indexname="PK_MarketDataCurrent" id="lock64ac7940" mode="U" 
associatedObjectId="72057594090487808">

The figures in brackets are the hashed values for different keys within MKP_RISKDB.dbo.MarketDataCurrent(PK_MarketDataCurrent)

The first part is the same because its deadlocking on the same resource (likely Index), but different Row (2nd Part) of wait resource. With the wait resource you can actually get the exact row or page, the previous answer only had the database and table or index identified. Replace the below code with the appropriate ID's in the waitresource.

In your example waitresource="KEY: 6:72057594090487808 (d900ed5a6cc6)

Database - retrieve the database involved in the deadlock with
```
SELECT * FROM sys.databases WHERE database_id IN (6)
```

Table or Index - retrieve the table or index involved in the deadlock

SELECT b.name AS TableName, 
       c.name AS IndexName, c.type_desc AS IndexType, * 
FROM sys.partitions a
INNER JOIN sys.objects b 
   ON a.object_id = b.object_id
INNER JOIN sys.indexes c 
   ON a.object_id = c.object_id  AND a.index_id = c.index_id
WHERE partition_id IN ('72057594090487808')

Exact Row - retrieve the exact row or page, in your specific case the wait resource was a KEY, so you search the "column" %%lockres%% (yes the column name is actually %%lockres%%, its known as an "undocumented Virtual column"). If your table is not too out of date or if it is not a DELETE operation, then you will find the exact row from that hash, after you have determined which table that "partition id" or "hobt_id" is from then alter and run the below code (disclaimer - the hashes and page locations may have changed by the time you are doing the debugging, though unlikely with the key hashes)
```
SELECT 
   sys.fn_PhysLocFormatter(%%physloc%%) AS PageResource, 
   %%lockres%% AS LockResource, *
FROM <<InsertTableNameFromStep2Here>>
WHERE %%lockres%% IN ('(d900ed5a6cc6)')
```

Keep in mind that usually 2 resources are conflicting which caused the deadlock. However, it doesn't necessarily have to be both of the statements provided in the deadlock graph which is doing both of the locking. It is also possible that a statement prior, but within the same transaction (but not identified in the deadlock graph) locked 1 of the 2 resources. But 1 of the 2 statements in the deadlock is definitely involved in locking 1 of the 2 resources causing the deadlock at the time the deadlock was logged.

Before you do all of the above, if will be handy if you have the Deadlock Graphs, you can trace this with the Profiler or SQL Logs with Trace Flag, but the more handy way is to use the SQL Server Extended events feature. Simply by running the Query Below you can retrieve the deadlock graphs.

SELECT
   DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), CURRENT_TIMESTAMP), DeadlockEventXML.value('(event/@timestamp)[1]', 'datetime2')) AS [EventTime],
   DeadlockEventXML.value('(//process[@id[//victim-list/victimProcess[1]/@id]]/@hostname)[1]', 'nvarchar(max)') AS HostName,
   DeadlockEventXML.value('(//process[@id[//victim-list/victimProcess[1]/@id]]/@clientapp)[1]', 'nvarchar(max)') AS ClientApp,
   DB_NAME(DeadlockEventXML.value('(//process[@id[//victim-list/victimProcess[1]/@id]]/@currentdb)[1]', 'nvarchar(max)')) AS [DatabaseName],
   DeadlockEventXML.value('(//process[@id[//victim-list/victimProcess[1]/@id]]/@transactionname)[1]', 'nvarchar(max)') AS VictimTransactionName,
   DeadlockEventXML.value('(//process[@id[//victim-list/victimProcess[1]/@id]]/@isolationlevel)[1]', 'nvarchar(max)') AS IsolationLevel,
   DeadlockEventXML.query('(event/data[@name="xml_report"]/value/deadlock)[1]') AS DeadLockGraph,
   DeadlockEventXML
FROM
(
   SELECT 
      XEvent.query('.') AS DeadlockEventXML,
     Data.TargetData
   FROM 
   (
      SELECT 
        CAST(target_data AS XML) AS TargetData
        FROM sys.dm_xe_session_targets st
        JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address
        WHERE s.name = 'system_health' AND
          st.target_name = 'ring_buffer'
   ) AS Data
   CROSS APPLY TargetData.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData(XEvent)
) AS DeadlockInfo

For SQL Server 2008 and 2008 R2, Use this query.

SELECT
   DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), CURRENT_TIMESTAMP), DeadlockEventXML.value('(event/@timestamp)[1]', 'datetime2')) AS [EventTime],
   DeadlockEventXML.value('(//process[@id[//victim-list/victimProcess[1]/@id]]/@hostname)[1]', 'nvarchar(max)') AS HostName,
   DeadlockEventXML.value('(//process[@id[//victim-list/victimProcess[1]/@id]]/@clientapp)[1]', 'nvarchar(max)') AS ClientApp,
   DB_NAME(DeadlockEventXML.value('(//process[@id[//victim-list/victimProcess[1]/@id]]/@currentdb)[1]', 'nvarchar(max)')) AS [DatabaseName],
   DeadlockEventXML.value('(//process[@id[//victim-list/victimProcess[1]/@id]]/@transactionname)[1]', 'nvarchar(max)') AS VictimTransactionName,
   DeadlockEventXML.value('(//process[@id[//victim-list/victimProcess[1]/@id]]/@isolationlevel)[1]', 'nvarchar(max)') AS IsolationLevel,
   DeadlockEventXML.query('(event/data[@name="xml_report"]/value/deadlock)[1]') AS DeadLockGraph,
   DeadlockEventXML
FROM
(
   SELECT 
            CONVERT(XML,REPLACE(REPLACE(CONVERT(VARCHAR(MAX),XEvent.query('.')), '&lt;', '<'), '&gt;', '>'))
   AS DeadlockEventXML,
     Data.TargetData
   FROM 
   (
      SELECT 
        CAST(target_data AS XML) AS TargetData
        FROM sys.dm_xe_session_targets st
        JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address
        WHERE s.name = 'system_health' AND
          st.target_name = 'ring_buffer'
   ) AS Data
   CROSS APPLY TargetData.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData(XEvent)
) AS DeadlockInfo