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

Reproducing one more intermittent deadlock on only one table

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

 

Published Thursday, March 26, 2009 11:46 AM 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

 

Alexander Kuznetsov said:

Selects under READ COMMITTED may return incorrect results if the data they select is being modified at

April 10, 2009 8:41 PM
 

Dinesh said:

How get mail alert from deadlock??

September 19, 2012 6:30 AM

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 at DRW Trading Group in Chicago, where he leads a team of developers, practicing agile development, defensive programming, TDD, and database unit testing.

This Blog

Syndication

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