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 ;).