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 deadlocks involving only one table

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;

Published Thursday, January 01, 2009 6:12 PM 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:

Apparently for high isolation levels some heap tables may be more prone to deadlocks than identical tables

March 15, 2009 7:02 PM
 

Alexander Kuznetsov said:

I have already described several deadlock scenarios that involve only one table in another post. This

March 26, 2009 12:00 PM
 

jatric said:

Alexander:

What should be the best way to avoid deadlock condition or prompt user a valid message rather than .net error in existing and big application?

July 18, 2012 1:56 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