I have already described several deadlock scenarios that
involve only one table in another post.
This time I will describe an intermittent deadlock which
occurs in high concurrency environments. Before providing a complex repro
script for it, I will provide two simpler repro scripts for similar deadlock
scenarios (similar deadlock graphs), all of which involve only one table and occur
only under high isolation levels. Also in every scenario only one connection
will modify the table. I will only provide the repro scripts - for information on troubleshooting them refer to
Bart Duncan's blog posts on Deadlock Troubleshooting
Prerequisites
Here are the table and sample data:
CREATE TABLE
Data.AccountBalances(
AccountNumber INT
NOT NULL
CONSTRAINT
PK_AccountBalances PRIMARY KEY,
Amount DECIMAL(10,2) NOT NULL,
CustomerID INT
NOT NULL,
SpaceFiller CHAR(100) NOT NULL
);
GO
DECLARE @i INT;
SET NOCOUNT
ON;
SET @i=100000;
WHILE(@i<300000) BEGIN
INSERT Data.AccountBalances(
AccountNumber,
Amount,
CustomerID,
SpaceFiller)
VALUES(@i, 1000, @i, 'qwerty');
SET @i=@i+1;
END;
GO
A simple deadlock with REPEATABLE READ isolation level
From one tab, run the following script:
SET TRANSACTION
ISOLATION LEVEL
REPEATABLE READ;
--
--SET
TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRAN
SELECT SUM(Amount)
FROM Data.AccountBalances
WHERE
AccountNumber <= 100010;
From another tab, run the following script:
BEGIN TRAN
UPDATE Data.AccountBalances SET Amount = Amount - 10
WHERE
AccountNumber = 250000
AND
Amount > 10;
The locks acquired by the first transaction do not block
this update; it completes right away. The following insert also completes
immediately, although the inserted row matches the range AccountNumber <
100010:
INSERT Data.AccountBalances(
AccountNumber,
Amount,
CustomerID,
SpaceFiller)
VALUES(50000, 1000, 50000, 'qwerty');
The reason is simple: by definition REPEATABLE READ does not
have to block inserts into the range. However, the following update is blocked
if the isolation level of the SELECT is higher than READ COMMITTED (that means that the isolation
level is REPEATABLE READ or SERIALIZABLE):
UPDATE Data.AccountBalances SET Amount = Amount + 10
WHERE
AccountNumber = 100005
AND
Amount > 10;
Get back to your first tab and issue another SELECT:
SELECT SUM(Amount)
FROM Data.AccountBalances
WHERE
AccountNumber > 100010;
And you will get a deadlock in one of your tabs:
Msg 3902, Level 16, State 1,
Line 1
The COMMIT TRANSACTION
request has no corresponding BEGIN TRANSACTION.
To clean up, roll back both
transactions.
A simple deadlock with SERIALIZABLE isolation level
In the first tab, up the isolation level to SERIALIZABLE and
rerun the script:
SET TRANSACTION
ISOLATION LEVEL
SERIALIZABLE;
BEGIN TRAN
SELECT SUM(Amount)
FROM Data.AccountBalances
WHERE
AccountNumber <= 100010;
In the second tab, rerun the update, which will complete:
BEGIN TRAN
UPDATE Data.AccountBalances SET Amount = Amount - 10
WHERE
AccountNumber = 250000
AND
Amount > 10;
Note that this time your insert will be blocked, because
SERIALIZABLE isolation level prevent inserts into the range:
INSERT Data.AccountBalances(
AccountNumber,
Amount,
CustomerID,
SpaceFiller)
VALUES(50000, 1000, 50000, 'qwerty');
Return to the first tab, run another select, and get a
deadlock again:
SELECT SUM(Amount)
FROM Data.AccountBalances
WHERE
AccountNumber > 100010;
Do not forget to rollback both transactions.
An intermittent deadlock in high concurrency environment
The following deadlock is intermittent – you cannot
reproduce it as easily as the ones in the previous sections. You need to mimic
high concurrency to observe it. However, this deadlock is very similar to the
ones I already described in this post.
In one tab, run the following loop:
SET TRANSACTION
ISOLATION LEVEL
REPEATABLE READ;
WHILE(@i<10000) BEGIN
SELECT
@TotalAmount = SUM(Amount)
FROM Data.AccountBalances;
SET @i=@i+1;
END;
In the other tab, run this loop:
DECLARE @i INT, @AccountNumber INT, @AnotherAccountNumber INT;
SET NOCOUNT
ON;
SET @i=0;
WHILE(@i<100000) BEGIN
SELECT
@AccountNumber = 100000 + RAND()*200000;
SELECT
@AnotherAccountNumber = 100000 + RAND()*200000;
IF
@AccountNumber <> @AnotherAccountNumber BEGIN
BEGIN TRAN
UPDATE Data.AccountBalances SET
Amount = Amount -
10
WHERE
AccountNumber = @AccountNumber
AND
Amount > 10;
IF @@ROWCOUNT = 1 BEGIN
UPDATE
Data.AccountBalances SET
Amount = Amount +
10
WHERE
AccountNumber = @AnotherAccountNumber
AND
Amount > 10;
COMMIT;
END ELSE BEGIN
ROLLBACK;
END
END
SET @i=@i+1;
END;
GO
When I run these loops, I am consistently getting deadlocks
on several different editions and service packs. Of course, I cannot guarantee
that you will get a deadlock – I have not tried my scripts out on all possible
versions of software and hardware. Yet I encourage you to try the repro scripts
out and see for yourself.
My previous posts about deadlocks are:
Some heap tables may be more prone to deadlocks than identical tables with clustered indexes
Reproducing deadlocks involving only one table
When Index Covering Prevents Deadlocks