Although the best known deadlock scenario involves two
connections modifying two tables in different order, there are also other
deadlock scenarios involving only one table. Besides, in some scenarios each
connection needs to issue only one statement, and it is enough to get a
deadlock. Also in some scenarios only one connection needs to modify or acquire
exclusive locks – the other one may only read data and only acquire shared
locks and still embrace in a deadlock. I will provide repro scripts so that you
can see for yourself. I will not analyze the deadlocks – you can reproduce them
and do the troubleshooting youself.
Prerequisites
The following script creates and populates the table:
USE master
GO
DROP DATABASE DeadlockTests;
GO
CREATE DATABASE DeadlockTests;
GO
USE
DeadlockTests;
GO
CREATE SCHEMA Data AUTHORIZATION
dbo;
GO
--
-- Setting up a
helper table with 1000 consecutive integer numbers
--
CREATE TABLE Data.Numbers(Number INT);
GO
DECLARE @i INT;
SET NOCOUNT ON;
SET @i=0;
WHILE(@i<1000) BEGIN
INSERT Data.numbers(Number)VALUES(@i);
SET @i=@i+1;
END;
GO
--
--I have a table
clustered on ID. Two multi row updates embrace in a deadlock.
--Each update
touches a subset of rows via a non-clustered index. They use different
--NCIs to locate
rows. They deadlock because they access rows in different
--orders, in orders
of used NCIs. Here is my repro:
--
--
-- I added
filler column so that I have less than 40 rows per page.
-- This is
necessary to spread out the rows that will be modified,
-- so that
modification using non-clustered index is preferable
-- to scanning
the whole clustered index.
--
CREATE TABLE Data.Test(ID INT NOT NULL CONSTRAINT PK_Test PRIMARY
KEY,
i1 INT NOT
NULL,
i2 INT NOT
NULL,
toggle1 INT NOT NULL,
toggle2 INT NOT NULL,
filler CHAR(200));
GO
--
-- Assuming that
there is a helper table Data.Numbers
-- which has at
least 1000 rows
--
INSERT INTO Data.Test(ID, i1, i2, toggle1, toggle2, filler)
SELECT n1.Number*1000 + n2.Number,
n2.Number*1000 + n1.Number,
1000000 - n2.Number*1000 - n1.Number,
0,
0,
'qwerty'
FROM Data.Numbers AS n1 CROSS JOIN Data.Numbers AS n2
WHERE n1.Number<1000 AND n2.Number<1000;
GO
CREATE UNIQUE INDEX
UNQ_Test_i1 ON Data.Test(i1);
GO
CREATE UNIQUE INDEX
UNQ_Test_i2 ON Data.Test(i2);
GO
Only one table, only one UPDATE from
each connection, still a deadlock.
This scenario is actually quite similar to the vanilla
deadlock scenario described in many textbooks: two connections modify multiple
rows in different order. However, multiple rows are modified by single UPDATE,
which may run quite fast. Usually you cannot start two UPDATEs fast enough so
that they embrace in a deadlock, this is why UPDATEs are issued many times in a
loop.
--
-- Run the
following script from one SSMS tab:
--
DECLARE @i INT;
SET NOCOUNT ON;
SET @i=0;
WHILE (@i<10000) BEGIN
UPDATE Data.Test
SET toggle1 = toggle1 + 1
WHERE i1 BETWEEN 1000 AND 1500;
SET @i = @i + 1;
END;
--
-- and the
following script from another SSMS tab simultaneously:
--
DECLARE @i INT;
SET NOCOUNT ON;
SET @i=0;
WHILE (@i<10000) BEGIN
UPDATE Data.Test
SET toggle2 = toggle2 + 1
WHERE i2 BETWEEN 998500 AND
999000;
SET @i = @i + 1;
END;
--
-- And you will
get a deadlock right away:
Msg 1205, Level 13, State 51,
Line 5
Transaction (Process ID 52)
was deadlocked on lock resources with another process and has been chosen as
the deadlock victim. Rerun the transaction.
-- Have a look
at the data: both updates touch the same rows,
-- but the order
is different.
-- Note that as
ID increases, i1 increases too,
-- and i2
decreases:
--
SELECT * FROM Data.Test
WHERE (i1
BETWEEN 1000 AND
1500)
OR (i2 BETWEEN 998500 AND 999000);
1 1000 999000
1001 1001 998999
2001 1002 998998
(snip)
499001 1499 998501
500001 1500 998500
--
-- I added
filler CHAR(200) column to guarantee that each row to be modified
-- is stored on
a separate page
--
Each UPDATE modifies only one row,
still a deadlock.
This scenario is quite simple: two connections locate a
single row via two different non-clustered indexes and update it. Even though
each connection modifies only one row, this is enough to cause a deadlock, even
under READ COMMITTED isolation level. To reproduce, first run the following
script:
CREATE PROCEDURE dbo.UpdateTest1
@i1 INT,
@addToI2 INT,
@addToToggle1 INT
AS
BEGIN
SET TRANSACTION ISOLATION
LEVEL READ
COMMITTED;
UPDATE Data.Test
SET toggle1 = toggle1 +
@addToToggle1,
-- modifies i2
so that the other non clustered index is also modified
i2 = i2 + @addToI2
WHERE i1 = @i1;
END;
GO
CREATE PROCEDURE dbo.UpdateTest2
@i2 INT,
@addToI1 INT,
@addToToggle2 INT
AS
BEGIN
SET TRANSACTION ISOLATION
LEVEL READ
COMMITTED;
UPDATE Data.Test
SET toggle2 = toggle2 +
@addToToggle2,
-- modifies i1
so that the other non clustered index is also modified
i1 = i1 + @addToI1
WHERE i2 = @i2;
END;
GO
-- these rows
are deleted so that the modifications done by
-- these two stored
procedures do not violate unique indexes
DELETE FROM Data.Test WHERE I1=999;
DELETE FROM Data.Test WHERE I1=1001;
DELETE FROM Data.Test WHERE I2=999001;
DELETE FROM Data.Test WHERE I2=998999;
Now run these loops:
--
-- Run the
following script from one SSMS tab:
--
DECLARE @i INT, @j INT;
SET NOCOUNT ON;
SELECT @i=0, @j = 0;
WHILE (@i<100000) BEGIN
BEGIN TRAN;
EXEC dbo.UpdateTest1
@i1 = 1000,
@addToI2 =
1,
@addToToggle1 =
1;
ROLLBACK;
SET @i = @i + 1;
END;
--
-- and the
following script from another SSMS tab simultaneously:
--
DECLARE @i INT, @j INT;
SET NOCOUNT ON;
SELECT @i=0, @j = 0;
WHILE (@i<100000) BEGIN
BEGIN TRAN;
EXEC dbo.UpdateTest2
@i2 =
999000,
@addToI1 =
1,
@addToToggle2 =
1
ROLLBACK;
SET @i = @i + 1;
END;
You will get a deadlock right away:
Msg 1205, Level 13, State 51,
Procedure UpdateTest1, Line 7
Transaction (Process ID 53)
was deadlocked on lock resources with another process and has been chosen as
the deadlock victim. Rerun the transaction.
One connection never acquires and
never needs exclusive locks, still a deadlock.
This scenario is similar to the previous one: two
connections locate a single row via two different non-clustered indexes. Only
one updates it, another just performs a clustered index seek. Even though only
one connection modifies the row, this is still enough to cause a deadlock. To
reproduce, first run the following script:
CREATE PROCEDURE dbo.SelectTest2
@i2 INT,
@toggle2 INT
OUT
AS
BEGIN
SET TRANSACTION ISOLATION
LEVEL READ
COMMITTED;
SELECT
@toggle2=toggle2 FROM
Data.Test
WHERE i2 = @i2;
END;
GO
Now run these loops:
--
-- Run the
following script from one SSMS tab:
--
DECLARE @i INT, @j INT;
SET NOCOUNT ON;
SELECT @i=0, @j = 0;
WHILE (@i<100000) BEGIN
BEGIN TRAN;
EXEC dbo.UpdateTest1
@i1 = 1000,
@addToI2 =
1,
@addToToggle1 =
1;
ROLLBACK;
SET @i = @i + 1;
END;
--
-- and the
following script from another SSMS tab simultaneously:
--
DECLARE @i INT, @j INT;
SET NOCOUNT ON;
SELECT @i=0, @j = 0, @toggle2 INT;
WHILE (@i<100000) BEGIN
EXEC dbo.SelectTest2 @i2 =
999000, @toggle2 OUT
SET @i = @i + 1;
END;