Using Indexing To Solve Blocking and Deadlocking Issues

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.

7 thoughts on “Using Indexing To Solve Blocking and Deadlocking Issues

Add yours

  1. Hello David

    Thank you for your interesting article

    The following text seems to be out of context

    UPDATE Address
    SET Street = ‘1 The Road’
    WHERE City = ‘Thunder Bay’

    Regards

    Stephen Keogh

    Like

  2. Hello David

    Thank you for your prompt reply

    Your replacement UPDATE statement

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

    does *not match the row being locked

    Regards

    Stephen Keogh

    Like

  3. This is all very fine but how would you know that a deadlock like that is going to arise in advance ? I mean there are many situations that can cause a deadlock.

    Like

    1. This is more something to think when you’re faced with a persistent deadlocking issue, you’ve done your troubleshooting and you’ve identified that using an index in this way could help you out.

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Create a website or blog at WordPress.com

Up ↑

%d bloggers like this: