Causes and Solutions for Deadlocks - SQL Server Query Performance Tuning - page 438

In a second connection, execute this script:
BEGIN TRANSACTION
UPDATE Purchasing.PurchaseOrderDetail
SET OrderQty = 4
WHERE ProductID = 448
AND PurchaseOrderID = 1255;
Each of these scripts opens a transaction and manipulates data, but neither commits or rolls back the
transaction. Switch back to the first transaction and run this additional query:
UPDATE Purchasing.PurchaseOrderDetail
SET OrderQty = 2
WHERE ProductID = 448
AND PurchaseOrderID = 1255;
Unfortunately, after possibly a few seconds, the first connection faces a deadlock.
Msg 1205, Level 13, State 51, Line 1
Transaction (Process ID 52) was deadlocked on lock resources with another process and has been
chosen as the deadlock victim. Rerun the transaction.
Any idea what's wrong here?
Let's analyze the deadlock by first examining the deadlock graph collected through the trace event. There is a
separate tab in the Event explorer window for the xml_deadlock_report event. Opening that tab will show you
the deadlock graph (see Figure 21-4 ).
Figure 21-4. A deadlock graph displayed in the Profiler tool
From the deadlock graph displayed in Figure 21-4 , it's fairly clear that two processes were involved: session 51
and session 52. Session 52, the one with the big X crossing it out (blue on the deadlock graph screen), was chosen
as the deadlock victim. Two different keys were in question. The top key was owned by session 51, as indicated by
the arrow pointing to the session object, named Owner Mode , and marked with an X for exclusive. Session 52 was
attempting to request the same key for an update. The other key was owned by session 54 with session 51 requesting
an update, indicated by the U . You can see the exact HoBt ID, object ID, object name, and index name for the objects
in question for the deadlock. For a classic, simple deadlock like this, you have most of the information you need.
The last piece would be the queries running from each process. These would need to be captured using a different
extended event.
This visual representation of the deadlock can do the job. However, you may need to drill down into the
underlying XML to really understand exactly where deadlocks occurred, what processes caused them, and which
objects were involved. If you open that XML file directly from the extended event value, you can find a lot more
information available than the simple set displayed for you in the graphical deadlock graph. Take a look at Figure 21-5 .