Now we’ll step through the deadlock graph

I’ll show you how to interpret this weird little map.

At the end of the video you get a challenge: can you design an index that will prevent the deadlock from happening again?

Transcript

This is our frenemy, the deadlock graph

Just like we saw in Management Studio, this is a screenshot of the very same thing. When we hover over those processes, we see that inputbuff. Again, it’s no more complete than what we saw in the XML. It’s the exact same thing, just shown in a little tool tip.

Our circle on the left of that deadlock graph is the select query. It does the joins between cities, states, and countries.

The circle on the right of the graph is that transaction that ends in updating state provinces.

The circles on the graph represent the queries

When we look over at that circle on the right, the query that’s doing the updates, see the lines coming into it from the boxes? The one that saws Owner Mode: X, that table under the box is the Application.Countries table.

And it’s got an index name on there as well. The index name is PK_Application_Countries.

That line from the box and the contents in the box itself, it’s saying this query has a key lock. It is an owner, the key lock on the primary key of Application.Countries. I know it’s a key lock because at the top of the box it says key lock.

The arrow at the bottom is pointing to the other box, saying Request Mode: X. That one is, if we look inside the box, Application.StateProvinces. So I am requesting, I want a key lock on the primary key of the state provinces table.

I’ve got a lock on the Countries table. I want a lock on the StateProvinces table for the query who’s doing the updates

If we go over to the query on the left, the one who has the big old X on it, that’s our victim, so sad! The box at the bottom has an arrow pointing at him, and it says Owner Mode. You know that box is the state provinces box.

So this one has the key lock on the primary key of the state provinces table.

The other line to the top box says Request Mode. I want, I am requesting a key lock on countries.

Putting this all together…

the query on the right who’s doing the updates is saying you are blocking my key lock on state provinces, whereas the query on the left is like, well, you’re blocking my key lock on countries.

The way that locking works in SQL Server, because these happen to start and collide at the same time, it’s like they’re on a really narrow road and they just can’t get past one another.

They do not have a mechanism for one of them to temporarily back up and let the other one pass.

The only way that they can release locks is if something comes in and kills them off entirely and they release their locks forever until they come back and retry.

That’s what the deadlock monitor steps in and does!

It comes in and looks at them and says, who looks like they should be voted off the island? (I am a Survivor fan.)

And in this case, this select query, it was declared the victim. It was killed off, so it released all of its locks. It doesn’t even want a lock anymore because it’s gone.

And our query on the right who does the updates is the victor and proceeds.

Wow, that’s rough. That’s the problem.

So how do we solve this?

When I am looking at these, and the first thing I do is just try to step through it and understand because just taking in all the information is somewhat time consuming, so hover around, look at stuff, make some notes and figure out what it all means.

But then once I have the information, I try to narrow down what the conflict is and I look at the indexes on the table first. Indexes, you know how I said it’s like two queries on a narrow road?

My question is always this…

Is there a way I can get one of these queries onto a different road so that they just pass one another by on roads that are near one another but not the exact same index or the exact same road?

Query on the left, that’s select query. It wants a key lock on the primary key of countries. That’s what it’s requesting. That’s what it’s blocked on.

Query on the right is the update query who wants the key lock on the primary key of state provinces.

Here’s your challenge

Given this information, can you design an index that will create two different roads so that these queries just pass one another by and they do not collide in an index?

That’s your challenge. Take some time, sketch it out, see if you can, and think of how it does, and feel free to go ahead and test this out in the WideWorldImporters database yourself, too, before you check your answer in the next video.

Write down: