Database Reference
In-Depth Information
Table 21-1.
(
continued
)
Entry in Log
Description
<keylock hobtid="72057594046840832" dbid="9"
objectname="AdventureWorks2008R2.Purchasing.
PurchaseOrderHeader" indexname="1" id="lockf98b8800"
mode="X" associatedObjectId="72057594046840832">
<owner-list>
<owner id="processf4ced868" mode="X" />
</owner-list>
<waiter-list>
<waiter id="processf4ecf498" mode="U"
requestType="wait" />
</waiter-list>
</keylock>
</resource-list>
</deadlock>
This information is a bit more difficult to read through than the clean set of data provided by the graphical
deadlock graph. However, it is a similar set of information, just more detailed. You can see, highlighted in bold near
the bottom, the definition of one of the keys associated with the deadlock. You can also see, just before it, that the text
of the execution plans is available through extended event XML output, unlike the deadlock graph. In this case, you
are much more likely to have everything you need to isolate the cause of the deadlock.
The information gathered by trace flag 1222 is almost identical to the XML data in every regard. The main
differences are the formatting and location. The output from 1222 is located in the SQL Server error log, and it's in
text format instead of nice, clean XML. The information collected by trace flag 1204 is completely different from
either of the other two sets of data and doesn't provide nearly as much detail. Trace flag 1204 is also much more
difficult to interpret. For all these reasons, I suggest you stick to using Extended Events if you can—or trace flag 1222
if you can't—to capture deadlock data. You also have the
system_health
session that captures a number of events by
default, including deadlocks. It's a great resource if you are unprepared for capturing this information. Just remember
that it keeps only four 5MB files online. As these fill, the data in the oldest file is lost. Depending on the number of
transactions in your system and the number of deadlocks or other events that could fill these files, you may have only
recent data available. Further, as mentioned earlier, since the
system_health
session uses the ring buffer to capture
events, you can expect substantial event loss, so your deadlock events could go missing.
This example demonstrated a classic circular reference. Although not immediately obvious, the deadlock
was caused by a trigger on the
Purchasing.PurchaseOrderDetail
table. When
Quantity
is updated on the
Purchasing.PurchaseOrderDetail
table, it attempts to update the
Purchasing.PurchaseOrderHeader
table.
When the first two queries are run, each within an open transaction, it's just a blocking situation. The second query is
waiting on the first to clear so that it can also update the
Purchasing.PurchaseOrderHeader
table. But when the third
query (that is, the second within the first transaction) is introduced, a circular reference is created. The only way to
resolve it is to kill one of the processes.
Before proceeding, be sure to roll back any open transactions.
Here's the obvious question at this stage: Can you avoid this deadlock? If the answer is “yes,” then how?
