THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Alexander Kuznetsov

When Index Covering Prevents Deadlocks

Suppose that a select has just navigated all the way down a non-clustered index and is trying to lookup a row in the clustered index. Suppose that at the same time an update has just modified that row and tries to modify the corresponding entry in the non-clustered index. This situation will end up in a deadlock. However, if the non-clustered index covers the select query, once the select has navigated to the leaf level of that index, there is no need to perform a bookmark lookup. As such, this select will not embrace in a deadlock. (Note that situations like this are intermittent – they happen from time to time, but not every time you run your select.

To reproduce this quite common situation, I created the following table and index:

 

CREATE TABLE [dbo].[Tickets](

      [TicketId] [int] NOT NULL,

      [AssignedTo] [int] NOT NULL,

      [Priority] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

      [Status] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

      [LotsOfComments] [char](5000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

 CONSTRAINT [PK_Tickets] PRIMARY KEY CLUSTERED

(

      [TicketId] ASC

)

) ON [PRIMARY]

GO

 

CREATE NONCLUSTERED INDEX [NCI_Tickets_AssignedTo] ON [dbo].[Tickets]

(

      [AssignedTo] ASC

)

GO

 

I populated the table with 10K rows, and simultaneously ran the following update:

 

UPDATE Data.Tickets SET AssignedTo = 1 WHERE TicketId = 1

 

SET NOCOUNT ON

DECLARE @i INT, @Status VARCHAR(10), @TicketId INT

SET @i = 0

WHILE @i < 100000 BEGIN

  SELECT @i = @i + 1

-- because originally AssignedTo = 1 for this row,

-- it will toggle 0,1,0,1,0,1,0,1

  UPDATE Data.Tickets SET AssignedTo = 1 - AssignedTo WHERE TicketId = 1

END

 

And I ran the following select in another tab of SSMS:

 

DECLARE @i INT, @Status VARCHAR(10), @AssignedTo INT

SET @i = 0

WHILE @i < 1000000 BEGIN

  SELECT @i = @i + 1, @Status = Status

    FROM Data.Tickets WHERE AssignedTo = 1

END

 

Every time I run these two scripts, the select consistently becomes a deadlock victim in just a few seconds. Let us use index covering:

 

DROP INDEX [Data].[Tickets].[Tickets_AssignedTo]

 

CREATE NONCLUSTERED INDEX [Tickets_AssignedTo] ON [Data].[Tickets]

(

      [AssignedTo], Status

)

GO

 

Now both scripts complete without deadlocks, although they usually run simultaneously for more than 30 seconds on my laptop.

As you have seen, in this particular case index covering completely eliminated deadlocks. Of course, real life situations are usually much more complex than this simplistic one.

PS As Uri pointed out, it is necessary to compare the outcome for different isolation levels. So, if I up the isolation level to repeatable read or to serializable, I am still getting deadlocks. Uri was right, the TABLOCK hint prevents deadlocks but slows the execution down significantly. Also there are no deadlocks under snapshot isolation - we could guess that much ;).

Published Saturday, May 03, 2008 11:08 PM by Alexander Kuznetsov

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Uri Dimant said:

Hi Alex

What if you changed  this statement

UPDATE Data.Tickets SET AssignedTo = 1 - AssignedTo WHERE TicketId = 1

TO

UPDATE Data.Tickets SET AssignedTo = 1 - AssignedTo

FROM Data.Tickets  WITH (TABLOCK)

WHERE TicketId = 1

Do you still get the error? I assume it will take some time to complete , but I do not expect DEADLOCK, what do you think?

May 4, 2008 6:43 AM
 

Alexander Kuznetsov said:

Hi Uri,

That's right, I should describe how different isolation levels affect the outcome. i will do it this night. Thanks!

May 5, 2008 9:36 AM
 

Alexander Kuznetsov said:

Apparently for high isolation levels some heap tables may be more prone to deadlocks than identical tables

March 15, 2009 7:02 PM
 

Alexander Kuznetsov said:

I have already described several deadlock scenarios that involve only one table in another post. This

March 26, 2009 12:00 PM

Leave a Comment

(required) 
(required) 
Submit

About Alexander Kuznetsov

Alex Kuznetsov has been working with object oriented languages, mostly C# and C++, as well as with databases for more than a decade. He has worked with Sybase, SQL Server, Oracle and DB2. He regularly blogs on sqlblog.com, mostly about database unit testing, defensive programming, and query optimization. Alex has written a book entitled "Defensive Database Programming with Transact-SQL" and several articles on simple-talk.com and devx.com. Currently he works as an agile developer.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement