The best way to learn to tackle deadlocks is practice

I’ll show you re-runnable code that you can use to cause a deadlock in WideWorldImporters, then show you how to capture the deadlock graph with an Extended Events trace.

Prefer to use a server side SQL Trace? That’s OK, I’ve got sample code for that, too!

Sample code to trace deadlock graphs

I’ve got a Gist with sample code to trace deadlock graphs in both SQL Trace and Extended Events. Check it out here: https://gist.github.com/LitKnd/19a5942e2527af3e90692c5145c3a059 (Gist License info.)

Transcript

We want to capture information about deadlocks. The best way to do that in SQL Server is to capture the deadlock graph.

Collecting deadlock graphs

The deadlock graph contains rich information about how it actually mapped out, what queries were involved with it, where did the lock conflicts happen. We can see this in XML, as well as a pictorial representation of the XML.

Now just like the blocked process report, we need to do this with a trace. We can do it with Ye Old SQL Trace done as a Server Side SQL trace. We can do it with Extended Events.

Now technically speaking there are some tools on modern SQL Servers where there’s a system health extended events trace running in the background. It picks up deadlock graphs among other things. It’s possible that if you’re having deadlocks you could get the information out of the system health trace. Sometimes you get lucky and it’s there.

But like I said, sometimes you get lucky. Sometimes you’re not lucky and that system health trace, by the time you get to it it’s rolled over because it doesn’t contain history for all time. It’s set to only capture a certain amount of stuff that can happen because they try to make it not consume too many resources on your SQL Server.

In Extended Events there’s just one event that we need to collect, the XML deadlock report. Here I’ve got an Extended Events trace that says hey, create a session. It’s gonna capture that event, save it to a file again on my S drive, that’s where I happen to keep my Extended Events traces on this instance.

Again with similar kind of default settings it’s okay if you lose an event. If you’re under stress don’t worry about it too much. Just try to stay alive if things get bad in SQL Server.

Let’s go ahead and create our events session. To get it actually collecting stuff we need to start it as well.

Now that it’s set up, we need a deadlock to happen. Oh look, I’ve got some code for one!

I really like this code which, I wrote this code. I don’t like all the code I write, but I like this code because this is a re-runnable deadlock.

It does do some updates. You’ll see it does change some data but it does it in a way that you can rerun this sequence of events multiple times and get it deadlocked each time without having to re-restore the database or undo the update that you did between them. This code is a rerunable deadlock which is nice and convenient.

It does use that WideWorldImporters database, so let’s get in the right database.

The first query involved in this deadlock, it comes in and says I am going to start a transaction and I’m updating the country’s table. This first session is gonna do two statements as part of this transaction but this deadlock, a deadlock happens when two queries are running at the same time.

This first query in session one, at this point it’s just done the first of two statements in its transaction. It has updated the Countries tables and incremented the latest recorded population.

Before it can get to the rest of its code though, another query comes in and another query starts in another session.

We’re gonna open up our new second session. This query is also using WideWorldImporters. It is running a select query that joins between Application.Cities, Application.StateProvinces, and Application.Countries. It wants to know information for state province Virginia.

We’ve got a situation where that second query, both of them are running.

Our first query meanwhile, gets to the point where it’s ready to run its second update and finish up.

Let’s get both of its updates on the screen at the same time, there we go.

Our first query gets– and imagine this all happened just at the same time. It didn’t really wait around for me to talk. It now updates the Application.StateProvinces table.

Here it goes and it’s hm, it’s taking a minute for it to run that update. But it finished. It finished successfully.

Our other query, it’s no longer executing. When we go and we look at the results we can see that oh man, I don’t know about you but this error like the message that you have been chosen as the deadlock victim, no matter how many times I see it it’s always sad. :(

But it gives you the helpful advice that you need to catch that error and re-run your transaction.

What we have here is a deadlock

Let’s go ahead and re-run that sequence just to prove that it really is re-runable.

Our first statement comes in. It runs its update. Our second query it’s like ah, I’ll run my transaction. Okay cool, it starts to rerun it, but it’s unlucky. It happened at the same time our first query was running again. Our first session goes to run its second update and commit and boom, once again the select query is chosen as the deadlock victim.

I have two deadlocks now, right?

I generated two deadlocks since I started up my extended events trace. Did it catch them? Let’s take a look. I’m gonna go into my extended events sessions. I’ll refresh this.

Sure enough, here is my SW deadlock graph extended event session. To open the file I’m just gonna go ahead and double-click it right here to open it in Management Studio.

Of course it’s on my file system as well. I could copy it off and look at it elsewhere.

Viewing the deadlock graph in SSMS

Sure enough we have two rows here that it has caught. This doesn’t look like a lot of information at first, right?

You’re like, we have an XML deadlock report and a time stamp.

We know these are identical so we’re going to highlight one of them.

When you look at this bottom pane it’s got more information than it looks like at first.

There are two tabs here. I always find it kind of hard to see the second tab. But if we click on that deadlock tab it will show us this graphical version of the deadlock right in Management Studio.

If we hover over things we can see more information about what’s going on in them. You’ll see it’s trying to show us the statement involved with it. We’ll talk about that a little more soon.

It is worth knowing that on the details tab it can be useful to double-click that value and open up the actual XML for the deadlock graph, too because if I want to do something like copy out information that graphical view of the deadlock graph is not super-duper handy. But this view I can copy out queries and I can see okay, I an decode the XML. So here is the list of the victims.

This process ID is this victim. Here is the detail for that process ID and here is its input buffer. Just like the input buffer from the blocked process report, this is not always complete information.

See this guy? This is that update, that set of two updates that we have, the begin tran and then ran an update against Application.Countries. We don’t see the update against Application.Countries in here at all.

All the input buff has is that last, most recent part of it that it ran. So to figure out what it ran before this we’ve got a big old hint here. We have a commit without a begin tran. We have kind of a clue right there that there’s more to it, but we may have to you know, it will give us a hint as to what the code is, but not always complete information.

We do, of course, have some job security from that because it takes people to work with one another to figure it all out. Let’s dig more into the deadlock graph and I’m gonna do this in some slides because it’s just a little easier for me to draw things on the slides and show them to you. Let’s figure out how to solve this guy.