A while back, I was having a conversation about a deadlocking issue and suggested that an index could perhaps help solve it. The reaction I got was along the lines of, ‘What, how can in index solve a deadlocking issue?’

So, can we solve a deadlocking issue with an index?

Let’s create a rather simple, contrived deadlock situation.

I’m going to start by creating a couple of rather simple tables.

--Address Table
CREATE TABLE [dbo].[Address](
[AddressID] [int] IDENTITY(1,1) NOT NULL,
[Street] [varchar](255) NULL,
[City] [varchar](255) NULL,
PRIMARY KEY CLUSTERED 
(
[AddressID] ASC
))
GO
--Name Table
CREATE TABLE [dbo].[Name](
[NameID] [int] IDENTITY(1,1) NOT NULL,
[Forename] [varchar](255) NULL,
[Surname] [varchar](255) NULL,
PRIMARY KEY CLUSTERED 
(
[NameID] ASC
)) 
GO

I’ll then populate the pair of them with 500 rows.

Create a Deadlock

Now I’ll open up two sessions on that database and create a simple deadlock situation.

On the first session, I’ll run the following code

BEGIN TRANSACTION
UPDATE Address
SET Street = '1 The Road'
WHERE City = 'Thunder Bay'

and on the second…

BEGIN TRANSACTION
UPDATE Name
SET Forename = 'Bob'
WHERE Surname = 'Blackwell'

We’re now in a place where we’ve got two sessions each holding an exclusive row lock in their respective tables. A pretty standard situation in SQL and not at all sinister.

Now, back to the first session and I’ll run the following select statement…

SELECT ForeName, Surname
FROM Name
WHERE Surname = 'Bryan'

Nothing gets returned, we’re blocked. That’s to be expected of course, session 2 is holding an exclusive lock on ‘Name’ thanks to the UPDATE that it’s not yet committed.

Let’s run the following from session 2

SELECT Street, City 
FROM Address
WHERE City = 'Karapinar'

and…… DEADLOCK!

A pretty straight forward deadlock scenario, I’m not going to explain that here, there’re plenty of resources explaining how and why this happens out there.

Can An Index Help Us Here?

Now for the big question, can we solve this using an index? Before we look at that, let’s have a look at what’s going on inside our ‘name’ table during this situation.

UPDATE Name
SET Forename = ‘Bob’
WHERE Surname = ‘Blackwell’

The first thing that happens is the UPDATE statement takes out a lock on the row it’s updating.

Now lets run our SELECT statement from the second session and see what happens.

SELECT Forename, Surname FROM Name
WHERE Surname = ‘Bryan’

We get blocked. Let’s quickly check the execution plan and see what’s happening…

A clustered index scan. So what that means is that SQL is scanning the clustered index from top to bottom until it hits the locked row. It can’t go any further at that point so ends up getting blocked.

I wonder if we can help SQL out here. If we can make it easier for SQL to find that row, we might be able to avoid that block and in turn, avoid the deadlock situation.

What about the following index…

CREATE INDEX ix_Name_Surname_INCLUDE 
ON Name (Surname) INCLUDE(Forename)

Let’s create that and try to recreate our original deadlock situation…

WOW, no deadlock!

So what’s happening now. Let’s think about the update first, now be aware that because we’ve added an index, our update as also got to update that index too. Because of that, we’ll now see a lock on the new index as well.

But why is our SELECT not getting blocked? Let’s have a look at that execution plan now…

Notice anything different? Because we’ve built a covering index, we can now perform a seek on the index and avoid the locked record altogether.

So by adding a covering index we can avoid our session getting blocked and prevent the deadlock from occurring.

Just To Prove That The Seek Was The Cure

Just to prove that the scan is the cause of the block, we can add FORCESCAN to our query and see what happens.

SELECT * 
FROM Name WITH (FORCESCAN)
WHERE Surname = 'Burt'

So now we can see that we’re once again scanning the index and now we’re back to the blocking situation.

Hopefully the above illustrates how the use of an index can help prevent blocking and ultimately, deadlock situations from occurring.

Obviously the usual caveats around indexing apply, have too many or excessively large indexes on your tables can hurt write performance so make sure that an index is the right way forward for you.