Database Reference
In-Depth Information
<resource-list>
<
information about resource involved in the deadlock
>
...
</ i
nformation about resource involved in the deadlock
>
<
information about resource involved in the deadlock
>
...
</
information about resource involved in the deadlock
>
</resource-list>
</deadlock>
</deadlock-list>
The first section, called
<process-list>
, includes information about the processes involved in the deadlock.
Each
<process>
node shows details for specific process, as shown in Listing 19-6. I removed the values from some of
the attributes to make it easier to read. I have highlighted the ones that can help us in the troubleshooting.
Listing 19-6.
Deadlock graph: Information about the process involved in the deadlock
<process
id="process3e4b29868"
taskpriority="0" logused="264"
waitresource="KEY:
14:72057594039500800 (74a07545ba5b)"
waittime="..." ownerId="..." transactionname="user_transaction"
lasttranstarted="..." XDES="..."
lockMode="S"
schedulerid="2" kpid="..." status="suspended" spid="55"
sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="..." lastbatchcompleted="..."
lastattention="..." clientapp="..." hostname="..." hostpid="..." loginname="..."
isolationlevel="read
committed (2)"
xactid="..." currentdb="14" lockTimeout="..." clientoption1="..." clientoption2="...">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="26" sqlhandle="...">
SELECT COUNT(*) [Cnt] FROM [Delivery].[Orders] WHERE [CustomerId]=@1
</frame>
</executionStack>
<inputbuf>
select count(*) as [Cnt]
from Delivery.Orders
where CustomerId = 766
commit
</inputbuf>
</process>
The process
id
attribute uniquely identifies the process.
Waitresource
and
lockMode
provide you information
about the type of lock and the resource for which process it is waiting. In our example, you can see that the process
is waiting for the shared (S) lock on one of the rows (keys). The
Isolationlevel
attribute shows you the current
transaction isolation level. Finally,
executionStack
and
inputBuf
allows you to find the SQL statement that was
executed when deadlock occurs. In some cases, especially when stored procedures are involved, you would need to
use the
sys.dm_exec_sql_text
function to get the SQL statements in the same way as we did in Listing 18-5 in the
previous chapter.
The second section of the deadlock graph, called
<resource-list>
,
contains information about the resources
involved in the deadlock. In our example, it would include the XML code shown in Listing 19-7.
Listing 19-7.
Deadlock graph: Information about the resources involved in the deadlock
<resource-list>
<keylock hobtid="72057594039500800" dbid="14"
objectname="SqlServerInternals.Delivery.Orders"
indexname="PK_Orders" id="lock3e98b5d00"
