Deadlocks can be frustrating to troubleshoot. I’ve blogged about deadlock priority in the past but what about deadlock graphs? Let’s take a look at deadlock graphs and what I think is the best way to decipher them.
Test Deadlock Setup
Let’s open up two query windows in SSMS. In the first window, we’ll have these two queries running in the StackOverflow2013 database:
BEGIN TRAN UPDATE Users SET Reputation = 10000 WHERE Id = 1 UPDATE Posts SET Score = 10 WHERE OwnerUserId = 1 ROLLBACK TRAN
And in the second window we’ll have the same two queries but opposite order:
BEGIN TRAN UPDATE Posts SET Score = 10 WHERE OwnerUserId = 1 UPDATE Users SET Reputation = 10000 WHERE Id = 1 ROLLBACK TRAN
To create our deadlock, we’ll want to start by running the BEGIN TRAN and first UPDATE statement in the first query window. Then, we’ll run the BEGIN TRAN and both UPDATE statements in the second query window.
Now, if we go back to the first query window and run the remaining UPDATE statement, we’ll get our deadlock:
Msg 1205, Level 13, State 51, Line 7
Transaction (Process ID 59) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
We know we have a deadlock and we need to find out more information.
Deadlock Graph from Extended Events
How do we obtain a deadlock graph so that we can review information about a deadlock? One way is to use the system_health session in Extended Events.
In Object Explorer in SSMS, go to Management > Extended Events > Sessions > system_health. Right click on system_health, start the session if it’s not already started, and select Watch Live Data. Now, if we run through our example from above, we will see the following when the deadlock occurs:
Clicking on the Deadlock tab towards the bottom of the window will show us our deadlock graph.
This is one way to watch for deadlocks and grab the deadlock graph as they occur.
Deadlock Graph in Action
For this example, click off of the Deadlock tab and go back to the Details tab. Right click on the row of information and copy it. From here, paste it into your favorite text editor, remove the “xml_report” text at the beginning of the report, and save it as XML.
If you don’t already have Sentry One Plan Explorer, got to https://www.sentryone.com/plan-explorer and download it. It’s free, it’s amazing for troubleshooting execution plans, and it’s also useful for deadlock graphs as we’re going to see below. Once installed, open Sentry One Plan Explorer, click File > Open, and select the deadlock graph that was just saved a moment ago. When that opens, you should see a screen that looks like this:
You can expand the top section to see and scan through the deadlock information.
What I love about this is the ability to play through the deadlock step by step. Click the play button at the bottom left and rewatch the deadlock as it happens.
Seeing these types of visualizations was exciting and worth checking out. Other layout options are available as well so if you haven’t ever played around with this I recommend giving it a try.
Thanks for reading!