How to solve a deadlock with an index

Deadlocks are hard...

Deadlocks can creep up at seemingly random occasions and slow things down for a bit and then tell your error log that a query was chosen as a deadlock victim, telling you to re-run the transaction. And of course your application is built to automatically re-run transactions that have been chosen as deadlock victims, right?

...right?

No?

Okay then, let's talk about how to prevent the deadlock from happening in the first place.

There are many ways to tackle deadlock issues, and one of them is by bypassing the locking altogether, using an index. This might not be useful in your particular situation, and you should definitely watch out for bloating your database with way too many indexes. However if it is useful to you, or just find it an interesting knowledge to have in your arsenal, I'm pleased to inform you about it!

Let's recreate a simply deadlocking issue, where two separate sessions are updating two separate tables, each holding an exclusive lock. Now session one wants to select that exact data session two is locking, and session two wants to select that exact data session one is locking! Oh geez...

Session one updates the score on two rows in the Posts table

Session two updates the score on two rows in the Comments table

Now both sessions are holding a lock each.

Session one is now trying to access the exact same rows that are locked in Session two

...and it is taking foooooorever, as it's blocked by another query

Now Session two is trying to access the rows that are locked by Session one!

All of a sudden, we are at a standoff, as Session one is waiting for Session two and Session two is waiting for Session one. Nobody wants to yield their claim to the throne, so SQL Server decides to take things into its own hands and kill the one with the lowest query cost.

Poor Session number two... RIP

Look on the bright side, though... at least Session one is happy.

But this can be prevented.

This whole thing occurs because the selects are trying to access the same object as someone else is currently holding. What if we don't even have to touch that object at all and instead get our data from somewhere else?

Introducing a covering index:

Alternatively, you could add a covering index on the Posts table. It's going to have the same effect.

Before this index, our select on the comments table was scanning the clustered index, and the other session was also updating the clustered index.

But after we add our index, our select will no longer read from the object that the other session is holding a lock on (because it doesn't need to, because Session two doesn't want to update creationdate. It only wants to update the score. If Session two also wanted to update creationdate, then it would be a different matter, as it would have wanted to lock this index as well).

...with the added benefit of executing in 0.000 seconds, instead of 15.751 seconds

Now, if we go back to our original problem, with the new index in place...

Both Session one and Session two holds their locks, just as they did before...

...but when Session One tries to select the creationdate from Comments (that Session two is currently updating), instead of being blocked, it finishes instantly!

And it then proceeds to commit its transaction, freeing up Session two from running its select as well, which finishes just fine!