Time to check your work!

See the index that prevents this deadlock from happening again, and learn why it solves this deadlock problem.

Transcript

Alright, let’s solve this deadlock.

Here’s the indexing idea I have…

The indexing idea I have is to create a nonclustered index on CountryId, on the Application Countries table, and include the CountryName.

Now, the reason that I want to index Application.Countries is the select query wants a lock on that, and it doesn’t want all of the columns in the table.

It doesn’t care about, for instance, LatestRecordedPopulation at all.

The query who is doing the updates, it’s updating and incrementing the LatestRecordedPopulation.

Which indexes does the update lock?

Now, when you run an update and you’re changing a value, that value is in the base table. So it’s going to have to get a lock on the clustered primary key of the table.

But, here’s the magic. If there are indexes in the table that don’t contain LatestRecordedPopulation at all, then the update query doesn’t have to lock those indexes.

We can create an index that the update query WON’T lock

So as long as we’re creating an index for the select query that has ALL of the columns it needs, that “covers” the select query.

It can use that nonclustered index and not have to go to the base table at all, the base table where the locking is happening.

Let’s take a look and test out this index in SQL Server and prove whether or not it prevents that deadlock

Here’s the index that I say is a deadlock killer.

We’re going to create an index named IX_deadlock_killer on the Application.Countries table on CountryId and we’re going to include the CountryName column.

Let’s go over and look at our query who is the deadlock victim. On the Application.Countries table, it is joining on CountryID. I have that in the key of my nonclustered index so that it can quickly find the row it wants to join on based on the CountryID.

It is pulling back the CountryName as part of the query. Now, it’s not prefix ctry, so you might have to do a little guessing assumptions or double check in WideWorldImporters to make sure. It’s a sloppily written query, but that’s kind of the real world too, right?

We don’t have any other references to the Country table in here, so that index on CountryID include CountryName covers the Countries table for the purpose of this query.

Let’s go ahead and create the index on the table– and this is a nice itty bitty table, it doesn’t take any time at all, and then let’s see if this works. So, let’s restart our sequence again.

First up, our transaction begins and the first session updates Application.Countries, just like before. Before it can get to the second part of its transaction, however, the query begins and something is different already.

I got results immediately.

This query didn’t get blocked, didn’t get stuck waiting for rows to come back

When I did this before, this second query was blocked and got stuck in that executing status.

This second query, if I run it again– I’ve now turned on actual execution plans using this button here at the top– and I’m going to run it again. Looking at the execution plan, the map of how it ran this query, I can see that in this execution plan– I’m going to highlight one operator– it says I am using the IX_deadlock_killer index on the Application.Countries table.

All of the other operators in here are for different tables. This one is StateProvinces, this one is for Cities.

It no longer– this query no longer had to use the primary key, which is a clustered primary key, on Application.Country.