Using a Clustered Index to Solve a SQL Server Deadlock Issue

By:   |   Updated: 2011-10-28   |   Comments (16)   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | > Locking and Blocking


Problem

At some time or another every DBA has been faced with the challenge of solving a deadlock issue in their SQL Server database. In the following tip we will look at how indexes and more specifically clustered indexes on the right columns can help reduce the chance of your applications receiving this dreaded error:

Msg 1205, Level 13, State 51, Line 3
Transaction (Process ID 56) was deadlocked on lock resources with another process and has
been chosen as the deadlock victim. Rerun the transaction.
Solution

Let's first setup our test scenario which we will use to demonstrate the different types of locks SQL Server will acquire based on the indexes on the table. As well we will look at under which circumstances we will encounter a deadlock. All of the following examples consist of two processes accessing different records in the same table in the following order:

Transaction 1 - BEGIN TRAN
Transaction 1 - UPDATE TABLE 1
Transaction 2 - BEGIN TRAN
Transaction 2 - UPDATE TABLE 2
Transaction 2 - UPDATE TABLE 1
Transaction 1 - UPDATE TABLE 2

Sample SQL Server Table and Data Population

For our example, let's setup two sample tables and populate each of the tables with data.  Here is the code:

-- Table creation logic
CREATE TABLE [dbo].[TABLE1] 
([col1] [int] NOT NULL,[col2] [int] NULL,[col3] [varchar](50) NULL)
GO
CREATE TABLE [dbo].[TABLE2] 
([col1] [int] NOT NULL,[col2] [int] NULL,[col3] [varchar](50) NULL)
GO
ALTER TABLE dbo.TABLE1 ADD CONSTRAINT PK_TABLE1 PRIMARY KEY CLUSTERED (col1) 
GO
ALTER TABLE dbo.TABLE2 ADD CONSTRAINT PK_TABLE2 PRIMARY KEY CLUSTERED (col1)
GO
-- Populate tables
DECLARE @val INT
SELECT @val=1
WHILE @val < 1000
BEGIN  
   INSERT INTO dbo.Table1(col1, col2, col3) VALUES(@val,@val,'TEST')
   INSERT INTO dbo.Table2(col1, col2, col3) VALUES(@val,@val,'TEST')
   SELECT @val=@val+1
END
GO

SQL Server Deadlock Example

Let's run the following code in two separate SQL Server Management Studio sessions and see what happens.

Note: In all below examples SPID 55 and 56 represent the first and second transaction respectively.

-- 1) Run in first connection
BEGIN TRAN
UPDATE dbo.TABLE1 SET col3 = 'TEST' where col2=1
-- 2) Run in second transaction
BEGIN TRAN
UPDATE dbo.TABLE2 SET col3 = 'TEST' where col2=2
UPDATE dbo.TABLE1 SET col3 = 'TEST' where col2=2
-- 3) Run in first connection
UPDATE dbo.TABLE2 SET col3 = 'TEST' where col2=1

After executing the last statement we receive the following message:

Msg 1205, Level 13, State 51, Line 3
Transaction (Process ID 56) was deadlocked on lock resources with another process and has
been chosen as the deadlock victim. Rerun the transaction.

Below are the results of sp_lock and sp_who2 right before SQL Server detected the deadlock. We can see by looking at the highlighted records that we are going to have a deadlock situation as each transaction is trying to lock the same record in each table. We should be able to avoid this situation as these two processes are trying to update different records.

sp_lock and sp_who2 output for a deadlock

Be sure to commit the transactions in the earlier SQL Server Management Studio sessions.  Here is the code:

COMMIT

SQL Server Deadlock Resolution - Add NonClustered Index Option

Let's first try to solve this by adding an index to col2 in each of our tables.

CREATE NONCLUSTERED INDEX IX_TABLE2 ON dbo.TABLE2 (col2)
GO 
CREATE NONCLUSTERED INDEX IX_TABLE1 ON dbo.TABLE1 (col2)
GO

Running the same scenario as above we no longer encounter a deadlock. Below are the results of sp_lock and sp_who2 right before each transaction executed the COMMIT statement. We can see by looking at the highlighted records that each transaction is only locking the rows that they are updating (pay special attention to the Resource column) so in this case there is no deadlock.  Be sure to commit the records as shown above.

sp_lock and sp_who2 output to prevent a deadlock with a nonclustered index

Testing the Add NonClustered Index Option to resolve the SQL Server Deadlock

Now that we have col2 indexed let's see what happens if we try to update col2. Let's run the following code in two separate SQL Server Management Studio sessions:

-- 1) Run in first connection
BEGIN TRAN
UPDATE dbo.TABLE1 SET col2=1 where col2=1
-- 2) Run in second transaction
BEGIN TRAN
UPDATE dbo.TABLE2 SET col2=2 where col2=2
UPDATE dbo.TABLE1 SET col2=2 where col2=2
-- 3) Run in first connection
UPDATE dbo.TABLE2 SET col2=1 where col2=1

After executing the last statement we receive the following message:

Msg 1205, Level 13, State 51, Line 3
Transaction (Process ID 56) was deadlocked on lock resources with another process and has
been chosen as the deadlock victim. Rerun the transaction.

Below are the results of sp_lock and sp_who2 right before SQL Server detected the deadlock. We can see by looking at the highlighted records that we are going to have a deadlock situation as each transaction is trying to lock the same record in each table. We should also be able to avoid this situation as these two processes are trying to update different records.  Be sure to commit the records as shown above.

sp_lock and sp_who2 output for a deadlock with a nonclustered index

SQL Server Deadlock Resolution - Change Clustered Index Option

Now let's see if we can solve this deadlock issue by changing the clustered index on the table.  We will make the primary key index nonclustered and the col2 index clustered on each table respectively.

DROP INDEX IX_TABLE1 ON dbo.TABLE1
GO
ALTER TABLE dbo.TABLE1 DROP CONSTRAINT PK_TABLE1 
GO
ALTER TABLE dbo.TABLE1 ADD CONSTRAINT PK_TABLE1 PRIMARY KEY NONCLUSTERED (col1)
GO
CREATE UNIQUE CLUSTERED INDEX IX_TABLE1 ON dbo.TABLE1 (col2)
GO
DROP INDEX IX_TABLE2 ON dbo.TABLE2
GO
ALTER TABLE dbo.TABLE2 DROP CONSTRAINT PK_TABLE2
GO
ALTER TABLE dbo.TABLE2 ADD CONSTRAINT PK_TABLE2 PRIMARY KEY NONCLUSTERED (col1) 
GO
CREATE UNIQUE CLUSTERED INDEX IX_TABLE2 ON dbo.TABLE2 (col2)
GO

Running the same scenario as above we no longer encounter a deadlock. Below are the results of sp_lock and sp_who2 right before each transaction executed the COMMIT statement. We can see by looking at the highlighted records that each transaction is only locking the rows that they are updating (pay special attention to the Resource column) so in this case there is no deadlock.  Be sure to commit the records as shown above.

sp_lock and sp_who2 output to prevent a deadlock with a clustered index

In addition to using sp_lock and sp_who2 we can use the query plan for one of the statements from our test scenario, UPDATE dbo.TABLE1 SET col2=1 where col2=1, to highlight why we see the locking that we are seeing. For the scenario with the clustered unique index we see the optimizer chose to do an index seek hence it only locked the records that it was updating allowing other processes to update other records in the table concurrently.

If we were to look at the query plan for the previous scenario we can see that in that case the optimizer chose to do an index scan hence it acquired shared locks on all the records it scanned. This means that no other processes can update any of these records until these shared locks are released. That is why we saw a deadlock in the previous scenario.

Summary

Careful consideration needs to be taken when laying out the indexes on your SQL Server tables and even more attention placed when deciding on which column(s) the clustered index should be put on as this can have a dramatic impact on locking in your database. In my experience I have found columns that are heavily used in WHERE clauses are usually a good candidate, but there are many factors (table size, heavily skewed data, other indexes, etc.) that can affect the query plan and locks that the SQL Server optimizer decides to use during execution. Testing with your application/database should be done to ensure that you are getting the best performance possible.

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Ben Snaidero Ben Snaidero has been a SQL Server and Oracle DBA for over 10 years and focuses on performance tuning.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2011-10-28

Comments For This Article




Tuesday, December 30, 2014 - 1:06:26 AM - chetan patel Back To Top (35785)

Thanks dear, you have save my job, i tried since 10 days, in my table there is insert and update from different 5 sql jobs, so index every time disturb, i remove cluster index with primary key and add primary key with non cluster index. my problem solved, thanks so much

 

Monday, October 22, 2012 - 7:03:11 AM - chris Back To Top (20033)

I was wondering why the 2nd example wasn't using the nonclustered index. I tried forcing it with an index hint

UPDATE t
 SET col2=1
from dbo.TABLE1 t with(index(ix_table1))
where t.col2=1

Then I saw that a table spool was needed when the nonclustered index seek was used because the column being updated is the same column used to select the row. The table spool made the estimated cost of the plan using the nonclustered index seek more expensive than the clustered index scan plan. With a larger table this will not be the case. Try adding 10K rows to the tables. Then the nonclustered index seek is used without an index hint and there is no need to change which columns are used for the clustered index.

Also, if col3 is updated in the 2nd example instead of col2, then the nonclustered index seek is automatically used with the 1000 row table because a table spool isn't needed.

Please note that I only used an index hint in this test example. I don't recommend using them to solve problems. There are other better solutions in the vast majority of cases.

Thanks for the example. It got me thinking. I used sys.dm_tran_locks to look at the locks. Profiler can be used to see the entire sequence of acquiring and releasing UPDATE locks during a scan while searching for the rows to update.

 

Monday, December 19, 2011 - 3:27:53 AM - pl80 Back To Top (15389)

Ben,

No, I've used the queries from the first example.  When using the queries from the 2nd one, it works the way you described (deadlock with non-clustered index).  Tx.

Monday, December 5, 2011 - 10:11:05 AM - Ben Snaidero Back To Top (15292)

Hi,

Interesting...I only have enterprise edition but I tested on both 9.00.4035 (32-bit) and 10.50.2500 (64-bit) and both deadlocked with only the non-clustered indexes.  I confirmed both were doing index scans as well.  Just to confirm you are using the second set of queries as they changed slightly from the first example?  Not sure what else would cause you to get a different result.

Ben.

Monday, December 5, 2011 - 2:33:32 AM - pl80 Back To Top (15288)

I've tested on 2005 developer and 2008 R2 enterprise.

Friday, December 2, 2011 - 9:21:24 AM - Ben Snaidero Back To Top (15268)
Hi What version of sql server are you using (include edition)? This could effect your results although I think I've tried it on both 2005 and 2008 with the same result. I will try this weekend and see if it makes a difference Ben

Friday, December 2, 2011 - 3:17:20 AM - pl80 Back To Top (15267)

Ben,

I've run the code provied and after creating the 2 NON-CLUSTERED indexes, the queries completed successfully without a deadlock.  SQL Server choose index seek for all 3 commands.  Didn't have to make the index a clustered one.

Tuesday, November 15, 2011 - 11:30:27 AM - Jason Back To Top (15118)

Just to clarify, indexing (clustered or nonclustered) makes the query faster, (instead of a scan, it does index seek), therefore a shorter time of locking or less locks obtained. The contention from other queries wanting to utilize the same resources excusively is reduced by minimizing the overlapping time (concurrency). The locking itself is necessary evil and it did not disappear (just lock less time and resources). To reduce contention of locking, you should use snapshot isolation. You should always index to improve queries and improve query efficiency no matter what.

See my SQLSaturday#57 presentation "Ask why my query so slow?"

Jason

http://usa.redirectme.net

Wednesday, November 2, 2011 - 6:57:05 PM - Chris Luttrell Back To Top (15000)

Ben,

Good tip and explanation.  I needed this about 3 months ago. :)  We ran into a similar problem and solved it by switching the clustered index to remove the contention.  It is nice to see validation that we ran into and did to solve it is common to others in the field.

Thanks,

Chris

Tuesday, November 1, 2011 - 8:48:17 PM - Ben Snaidero Back To Top (14988)

Hi Stranger,

I noticed this as well with some testing I did after the fact.  Also if you test with larger data sets you don't need the unique clause either which helps because you can't always add this to an index.  I'd also assume that if you loaded data more arbitrarily (ie. non sequential) or had a column with very low cardinality it would also alter the plan the optimizer chooses.  To make a long story short this example was just used to illustrate the different types of locks that sql server can acquire and how different indexes can help reduce contention for data.  Trying different indexes and checking the explain plan that sql server generates to make sure that there are no scans being done is really what I was trying to show.

One thing I just thought of (while typing above) is that if the column you were querying/updating was also a foreign key to another table this could also effect the plan.  So many things to consider :)

Thanks for reading my tip

Ben

Tuesday, November 1, 2011 - 5:46:39 PM - Stranger Back To Top (14986)

Hi Ben, if you make NCI unique in your 2nd example it'll solve the problem too.

Friday, October 28, 2011 - 11:42:37 AM - Mark Ganci Back To Top (14954)

Hi Ben, Sorry, please disregard my last comment.  I just realized that the columns being updated changed from col3 to col2. That was the source of my confusion.

Thanks

Friday, October 28, 2011 - 11:36:50 AM - Mark Ganci Back To Top (14953)

Hi Ben.   That first section starting "Running the same scenario as above we no longer encounter a deadlock." with the sreen shot, does that belong in the article? It seems like it may have been accidentally included as there are two.

Thanks

Friday, October 28, 2011 - 9:23:25 AM - William Bennett Back To Top (14951)

OK - I will give that a try and double check that example again.

I will look at the query plans too.

Friday, October 28, 2011 - 9:13:51 AM - Ben Snaidero Back To Top (14950)

Hi William,

Yes a non-clustered index can help in some cases (as it did in fixing the first deadlock issue in my examples).  And to your second question yes, a non-clustered index can also be unique.  Best thing to do is add different types on indexes and take a look at the query plans.  You want to try to avoid any table or index scans.

Ben.

Friday, October 28, 2011 - 8:44:43 AM - William Bennett Back To Top (14948)

We have problems like this all the time.

Could a non-clustered index help as well?

Can a non-clustered index be unique?

Is there anything else I should try?

TIA!














get free sql tips
agree to terms