Apparently for high isolation levels some heap tables may be
more prone to deadlocks than identical tables with clustered indexes. I have a
simple repro script which successfully completes if a table has a clustered
index but embraces in a deadlock if it runs against a heap. Here is the table:
CREATE TABLE dbo.NarrowTable(ID INT NOT NULL, i1 INT NOT NULL, i2 INT NULL, filler CHAR(1) NULL)
GO
SET NOCOUNT ON;
DECLARE @i INT;
SET @i=0;
WHILE @i<100 BEGIN
INSERT INTO dbo.NarrowTable(ID, i1, i2, filler) VALUES(@i,10000-@i,1,'?');
SET @i = @i+1;
END
GO
CREATE UNIQUE NONCLUSTERED INDEX UNQ_NarrowTable_ID ON
dbo.NarrowTable(ID);
Run the following script simultaneously from two
connections:
DECLARE @count INT;
SET TRANSACTION ISOLATION
LEVEL SERIALIZABLE;
SET NOCOUNT ON;
SET @count = 0;
WHILE @count<100000 BEGIN
UPDATE dbo.NarrowTable SET i2 = 1 - i2 WHERE i1 = 500;
SET @count = @count + 1;
END
I consistently get a deadlock every time I run it:
Msg 1205, Level 13, State 18,
Line 6
Transaction (Process ID 54)
was deadlocked on lock resources with another process and has been chosen as
the deadlock victim. Rerun the transaction.
Yet if I drop the
index and recreate it as a clustered, there are no deadlocks:
CREATE UNIQUE CLUSTERED INDEX UNQ_NarrowTable_ID ON
dbo.NarrowTable(ID);
Note that the column I am searching on, i1, is not indexed
at all. If there is an index on i1, there are no deadlocks. Also if I down the isolation level to repeatable read, I do not get deadlocking either.
I ran these scripts on 2005. Of course, this may or may not work on other versions.
I strongly prefer posting repro scripts to describing the anatomy of deadlocks. I think it is much more useful to let the reader run and troubleshoot, than to just read what the author has to say. Anyway, Jonathan Kehayias has recently posted a very detailed post describing a similar case, when after creating a clustered index deadlocks went away.
Here is the post courtesy of Jonathan Kehayias
My previous posts about deadlocks are:
Reproducing deadlocks involving only one table
When Index Covering Prevents Deadlocks