Transcript

Welcome to the SQLChallenge, Defuse the Deadlock. I’m Kendra Little from SQLWorkbooks.com.

Your challenge today is to take a deadlock and figure out: how can I prevent this deadlock from happening?

You need to defuse the deadlock by creating an index. You’re not allowed to change the queries involved, you’re not allowed to change their isolation levels, or the isolation level on the database, you need to defuse the deadlock just by creating an index. And in fact, you can create more than one index.

There is more than one way to defuse this deadlock

For extra credit, you can find a second way– and by finding a second way, I don’t mean just creating the same index definition with a different name.

To solve this problem you get some tools

I’m giving you a copy of the deadlock graph and even more. In the problem SQL file, I give you the steps to reproduce the deadlock, and I’m going to show you how this deadlock plays out right now. Here I am in Management Studio and the first thing you get in the problem file is information on the demo database and where to get it.

This demo uses Contoso Retail Data Warehouse, which is a Microsoft sample database. It’s not super huge, I’ve got the URL for you to download it in the script, as well as steps to restore the database. I have it set up with the drives on my test instance. Depending on where you put the backup file and how your test instance is set up, you may need to change some of those drive letters and folders. As you can see, Contoso restores super fast.

Here’s how to reproduce the deadlock

I’m going to use the database, and in our first session, we’ve got a transaction that has two update statements in it. The deadlock happens when things go like this.

In our first session here, we begin our transaction and then run the first update.

Before that transaction can complete, though, another query starts up in a totally different session. You have this at the bottom of your script. So I’m going to copy it, I’m going to open up a new session, paste that in, and just for convenience I’m going to put it in a new vertical tab group, so we can see them side by side.

What we’ve done so far is: in session one, it’s opened a transaction and it’s run this first update, but before it gets to the second one, a user comes in and they’re running a report.

Now, this query isn’t anything too nefarious, we’re just looking for some aggregate information from a view named dbo.V_CustomerOrders.

They start up their query, and as you can see, it’s not finishing up super fast, it’s blocked by our first update.

Back in our first session, we go to do the second update, and notice that I even have a ROLLBACK at the end of this. The reason that I’m not committing our transaction is just to make this whole thing re-runnable so that you can do this over and over. Beause if I committed it, when I went to go update again, and again, it wouldn’t find the same row.

The update completes successfully but oh look, our select query has fallen victim to the deadlock. It says over there, hey I was chosen as the deadlock victim. I need to rerun my transaction.

This is re-runnable!

I’m going to go over here and I’m going to say once again in our first session, let’s say the same thing happens again with the same timing, begin transaction runs the first update.

Hey, our user comes back in to retry their query after having gotten that nasty deadlock and now back in the first session, the second update runs, same sort of situation: we’ve got a deadlock again.

So, this is our reproduction of the deadlock.

What I’ve given you also is a file that contains a graph of this deadlock

I have an extended events trace that is looking for deadlocks, and it captured this deadlock graph, which I’ve saved as a .xdl file. That can be opened up by Management Studio.

We’ve got our vertical tab groups going here, so, it’s a little bit weird. Now, you may notice, look it’s really small, how do I resize it? Let’s get rid of our vertical tab groups because just moving things around didn’t fix it, right? So, I’m going to drag things back over here, and now say open recent file, open that deadlock graph, now I can see it in a bigger format.

You get the deadlock graph, you get the steps to reproduce the deadlock, and your challenge is: create an index that prevents this deadlock from happening when you walk through those same steps to reproduce the deadlock.

I’ll be back in the next video to show you two different ways to solve the deadlock.