Handling concurrent access to a key table without deadlocks in SQL Server

Question

I have a table that is used by a legacy application as a substitute for IDENTITY fields in various other tables.

Each row in the table stores the last used ID LastID for the field named in IDName.

Occasionally the stored proc gets a deadlock - I believe I've built an appropriate error handler; however I'm interested to see if this methodology works as I think it does, or if I'm barking up the wrong tree here.

I'm fairly certain there should be a way to access this table without any deadlocks at all.

The database itself is configured with READ_COMMITTED_SNAPSHOT = 1.

First, here is the table:

CREATE TABLE [dbo].[tblIDs](
    [IDListID] [int] NOT NULL 
        CONSTRAINT PK_tblIDs 
        PRIMARY KEY CLUSTERED 
        IDENTITY(1,1) ,
    [IDName] [nvarchar](255) NULL,
    [LastID] [int] NULL,
);

And the nonclustered index on the IDName field:

CREATE NONCLUSTERED INDEX [IX_tblIDs_IDName] 
ON [dbo].[tblIDs]
(
    [IDName] ASC
) 
WITH (
    PAD_INDEX = OFF
    , STATISTICS_NORECOMPUTE = OFF
    , SORT_IN_TEMPDB = OFF
    , DROP_EXISTING = OFF
    , ONLINE = OFF
    , ALLOW_ROW_LOCKS = ON
    , ALLOW_PAGE_LOCKS = ON
    , FILLFACTOR = 80
);

GO

Some sample data:

INSERT INTO tblIDs (IDName, LastID) 
    VALUES ('SomeTestID', 1);
INSERT INTO tblIDs (IDName, LastID) 
    VALUES ('SomeOtherTestID', 1);
GO

The stored procedure used to update the values stored in the table, and return the next ID:

CREATE PROCEDURE [dbo].[GetNextID](
    @IDName nvarchar(255)
)
AS
BEGIN
    /*
        Description:    Increments and returns the LastID value from tblIDs
        for a given IDName
        Author:         Hannah Vernon
        Date:           2012-07-19
    */

    DECLARE @Retry int;
    DECLARE @EN int, @ES int, @ET int;
    SET @Retry = 5;
    DECLARE @NewID int;
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    SET NOCOUNT ON;
    WHILE @Retry > 0
    BEGIN
        BEGIN TRY
            BEGIN TRANSACTION;
            SET @NewID = COALESCE((SELECT LastID 
                FROM tblIDs 
                WHERE IDName = @IDName),0)+1;
            IF (SELECT COUNT(IDName) 
                FROM tblIDs 
                WHERE IDName = @IDName) = 0 
                    INSERT INTO tblIDs (IDName, LastID) 
                    VALUES (@IDName, @NewID)
            ELSE
                UPDATE tblIDs 
                SET LastID = @NewID 
                WHERE IDName = @IDName;
            COMMIT TRANSACTION;
            SET @Retry = -2; /* no need to retry since the operation completed */
        END TRY
        BEGIN CATCH
            IF (ERROR_NUMBER() = 1205) /* DEADLOCK */
                SET @Retry = @Retry - 1;
            ELSE
                BEGIN
                SET @Retry = -1;
                SET @EN = ERROR_NUMBER();
                SET @ES = ERROR_SEVERITY();
                SET @ET = ERROR_STATE()
                RAISERROR (@EN,@ES,@ET);
                END
            ROLLBACK TRANSACTION;
        END CATCH
    END
    IF @Retry = 0 /* must have deadlock'd 5 times. */
    BEGIN
        SET @EN = 1205;
        SET @ES = 13;
        SET @ET = 1
        RAISERROR (@EN,@ES,@ET);
    END
    ELSE
        SELECT @NewID AS NewID;
END
GO

Sample executions of the stored proc:

EXEC GetNextID 'SomeTestID';

NewID
2

EXEC GetNextID 'SomeTestID';

NewID
3

EXEC GetNextID 'SomeOtherTestID';

NewID
2

---

EDIT:

I've added a new index, since the existing index IX_tblIDs_Name is not being used by the SP; I assume the query processor is using the clustered index since it needs the value stored in LastID. Anyway, this index IS used by the actual execution plan:

CREATE NONCLUSTERED INDEX IX_tblIDs_IDName_LastID 
ON dbo.tblIDs
(
    IDName ASC
) 
INCLUDE
(
    LastID
)
WITH (FILLFACTOR = 100
    , ONLINE=ON
    , ALLOW_ROW_LOCKS = ON
    , ALLOW_PAGE_LOCKS = ON);

---

EDIT #2:

I've taken the advice that @AaronBertrand gave and modified it slightly. The general idea here is to refine the statement to eliminate unnecessary locking, and overall to make the SP more efficient.

The code below replaces the code above from BEGIN TRANSACTION to END TRANSACTION:

BEGIN TRANSACTION;
SET @NewID = COALESCE((SELECT LastID 
        FROM dbo.tblIDs 
        WHERE IDName = @IDName), 0) + 1;

IF @NewID = 1
    INSERT INTO tblIDs (IDName, LastID) 
    VALUES (@IDName, @NewID);
ELSE
    UPDATE dbo.tblIDs 
    SET LastID = @NewID 
    WHERE IDName = @IDName;

COMMIT TRANSACTION;

Since our code never adds a record to this table with 0 in LastID we can make the assumption that if @NewID is 1 then the intention is append a new ID to the list, else we are updating an existing row in the list.

Answer 1

First, I would avoid making a round trip to the database for every value. For example, if your application knows it needs 20 new IDs, do not make 20 round trips. Make only one stored procedure call, and increment the counter by 20. Also it might be better to split your table into multiple ones.

It is possible to avoid deadlocks altogether. I have no deadlocks at all in my system. There are several ways to accomplish that. I will show how I would use sp_getapplock to eliminate deadlocks. I have no idea if this will work for you, because SQL Server is closed source, so I cannot see the source code, and as such I do not know if I have tested all possible cases.

The following describes what works for me. YMMV.

First, let us start with a scenario where we always get a considerable amount of deadlocks. Second, we shall use sp_getapplock eliminate them. The most important point here is to stress test your solution. Your solution may be different, but you need to expose it to high concurrency, as I will demonstrate later.

Prerequisites

Let us set up a table with some test data:

CREATE TABLE dbo.Numbers(n INT NOT NULL PRIMARY KEY); 
GO 

INSERT INTO dbo.Numbers 
    ( n ) 
        VALUES  ( 1 ); 
GO 
DECLARE @i INT; 
    SET @i=0; 
WHILE @i<21  
    BEGIN 
    INSERT INTO dbo.Numbers 
        ( n ) 
        SELECT n + POWER(2, @i) 
        FROM dbo.Numbers; 
    SET @i = @i + 1; 
    END;  
GO

SELECT n AS ID, n AS Key1, n AS Key2, 0 AS Counter1, 0 AS Counter2
INTO dbo.DeadlockTest FROM dbo.Numbers
GO

ALTER TABLE dbo.DeadlockTest ADD CONSTRAINT PK_DeadlockTest PRIMARY KEY(ID);
GO

CREATE INDEX DeadlockTestKey1 ON dbo.DeadlockTest(Key1);
GO

CREATE INDEX DeadlockTestKey2 ON dbo.DeadlockTest(Key2);
GO

The following two procedures are quite likely to embrace in a deadlock:

CREATE PROCEDURE dbo.UpdateCounter1 @Key1 INT
AS
SET NOCOUNT ON ;
SET XACT_ABORT ON;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION ;
UPDATE dbo.DeadlockTest SET Counter1=Counter1+1 WHERE Key1=@Key1;
SET @Key1=@Key1-10000;
UPDATE dbo.DeadlockTest SET Counter1=Counter1+1 WHERE Key1=@Key1;
COMMIT;
GO

CREATE PROCEDURE dbo.UpdateCounter2 @Key2 INT
AS
SET NOCOUNT ON ;
SET XACT_ABORT ON;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION ;
SET @Key2=@Key2-10000;
UPDATE dbo.DeadlockTest SET Counter2=Counter2+1 WHERE Key2=@Key2;
SET @Key2=@Key2+10000;
UPDATE dbo.DeadlockTest SET Counter2=Counter2+1 WHERE Key2=@Key2;
COMMIT;
GO

Reproducing deadlocks

The following loops should reproduce more than 20 deadlocks every time you run them. If you get less than 20, increase the number of iterations.

In one tab, run this;

DECLARE @i INT, @DeadlockCount INT;
SELECT @i=0, @DeadlockCount=0;

WHILE @i<5000 BEGIN ;
  BEGIN TRY 
    EXEC dbo.UpdateCounter1 @Key1=123456;
  END TRY
  BEGIN CATCH
    SET @DeadlockCount = @DeadlockCount + 1;
    ROLLBACK;
  END CATCH ;
  SET @i = @i + 1;
END;
SELECT 'Deadlocks caught: ', @DeadlockCount ;

In another tab, run this script.

DECLARE @i INT, @DeadlockCount INT;
SELECT @i=0, @DeadlockCount=0;

WHILE @i<5000 BEGIN ;
  BEGIN TRY 
    EXEC dbo.UpdateCounter2 @Key2=123456;
  END TRY
  BEGIN CATCH
    SET @DeadlockCount = @DeadlockCount + 1;
    ROLLBACK;
  END CATCH ;
  SET @i = @i + 1;
END;
SELECT 'Deadlocks caught: ', @DeadlockCount ;

Make sure you start both within a couple of seconds.

Using sp_getapplock to eliminate deadlocks

Alter both procedures, rerun the loop, and see that you no longer have deadlocks:

ALTER PROCEDURE dbo.UpdateCounter1 @Key1 INT
AS
SET NOCOUNT ON ;
SET XACT_ABORT ON;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION ;
EXEC sp_getapplock @Resource='DeadlockTest', @LockMode='Exclusive';
UPDATE dbo.DeadlockTest SET Counter1=Counter1+1 WHERE Key1=@Key1;
SET @Key1=@Key1-10000;
UPDATE dbo.DeadlockTest SET Counter1=Counter1+1 WHERE Key1=@Key1;
COMMIT;
GO

ALTER PROCEDURE dbo.UpdateCounter2 @Key2 INT
AS
SET NOCOUNT ON ;
SET XACT_ABORT ON;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION ;
EXEC sp_getapplock @Resource='DeadlockTest', @LockMode='Exclusive';
SET @Key2=@Key2-10000;
UPDATE dbo.DeadlockTest SET Counter2=Counter2+1 WHERE Key2=@Key2;
SET @Key2=@Key2+10000;
UPDATE dbo.DeadlockTest SET Counter2=Counter2+1 WHERE Key2=@Key2;
COMMIT;
GO

Using a table with one row to eliminate deadlocks

Instead of invoking sp_getapplock, we can modify the following table:

CREATE TABLE dbo.DeadlockTestMutex(
ID INT NOT NULL,
CONSTRAINT PK_DeadlockTestMutex PRIMARY KEY(ID),
Toggle INT NOT NULL);
GO

INSERT INTO dbo.DeadlockTestMutex(ID, Toggle)
VALUES(1,0);

Once we have this table created and populated, we can replace the following line

EXEC sp_getapplock @Resource='DeadlockTest', @LockMode='Exclusive';

with this one, in both procedures:

UPDATE dbo.DeadlockTestMutex SET Toggle = 1 - Toggle WHERE ID = 1;

You can rerun the stress test, and see for yourself that we have no deadlocks.

Conclusion

As we have seen, sp_getapplock can be used to serialize access to other resources. As such it can be used to eliminate deadlocks.

Of course, this can significantly slow down modifications. To address that, we need to choose the right granularity for the exclusive lock, and whenever possible, work with sets instead of individual rows.

Before using this approach you need to stress test it yourself. First, you need to make sure you get at least a couple dozen deadlocks with your original approach. Second, you should get no deadlocks when you rerun the same repro script using modified stored procedure.

In general, I do not think there is a good way to determine if your T-SQL is safe from deadlocks just by looking at it or looking at the execution plan. IMO the only way to determine if your code is prone to deadlocks is to expose it to high concurrency.

Good luck with eliminating deadlocks! We do not have any deadlocks in our system at all, which is great for our work-life balance.

Answer 2

Use of the XLOCK hint on either your SELECT approach or the following UPDATE should be immune to this type of deadlock:

DECLARE @Output TABLE ([NewId] INT);
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

BEGIN TRANSACTION;

UPDATE
    dbo.tblIDs WITH (XLOCK)
SET 
    LastID = LastID + 1
OUTPUT
    INSERTED.[LastId] INTO @Output
WHERE
    IDName = @IDName;

IF(@@ROWCOUNT = 1)
BEGIN
    SELECT @NewId = [NewId] FROM @Output;
END
ELSE
BEGIN
    SET @NewId = 1;

    INSERT dbo.tblIDs
        (IDName, LastID)
    VALUES
        (@IDName, @NewId);
END

SELECT [NewId] = @NewId ;

COMMIT TRANSACTION;

Will return with a couple of other variants (if not beaten to it!).

Answer 3

Mike Defehr showed me an elegant way to accomplish this in a very lightweight way:

ALTER PROCEDURE [dbo].[GetNextID](
    @IDName nvarchar(255)
)
AS
BEGIN
    /*
        Description:    Increments and returns the LastID value from tblIDs for a given IDName
        Author:         Hannah Vernon / Mike Defehr
        Date:           2012-07-19

    */

    DECLARE @Retry int;
    DECLARE @EN int, @ES int, @ET int;
    SET @Retry = 5;
    DECLARE @NewID int;
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    SET NOCOUNT ON;
    WHILE @Retry > 0
    BEGIN
        BEGIN TRY
            UPDATE dbo.tblIDs 
            SET @NewID = LastID = LastID + 1 
            WHERE IDName = @IDName;

            IF @NewID IS NULL
            BEGIN
                SET @NewID = 1;
                INSERT INTO tblIDs (IDName, LastID) VALUES (@IDName, @NewID);
            END
            SET @Retry = -2; /* no need to retry since the operation completed */
        END TRY
        BEGIN CATCH
            IF (ERROR_NUMBER() = 1205) /* DEADLOCK */
                SET @Retry = @Retry - 1;
            ELSE
                BEGIN
                SET @Retry = -1;
                SET @EN = ERROR_NUMBER();
                SET @ES = ERROR_SEVERITY();
                SET @ET = ERROR_STATE()
                RAISERROR (@EN,@ES,@ET);
                END
        END CATCH
    END
    IF @Retry = 0 /* must have deadlock'd 5 times. */
    BEGIN
        SET @EN = 1205;
        SET @ES = 13;
        SET @ET = 1
        RAISERROR (@EN,@ES,@ET);
    END
    ELSE
        SELECT @NewID AS NewID;
END
GO

(For completeness, here is the table associated with the stored proc)

CREATE TABLE [dbo].[tblIDs]
(
    IDName nvarchar(255) NOT NULL,
    LastID int NULL,
    CONSTRAINT [PK_tblIDs] PRIMARY KEY CLUSTERED 
    (
        [IDName] ASC
    ) WITH 
    (
        PAD_INDEX = OFF
        , STATISTICS_NORECOMPUTE = OFF
        , IGNORE_DUP_KEY = OFF
        , ALLOW_ROW_LOCKS = ON
        , ALLOW_PAGE_LOCKS = ON
        , FILLFACTOR = 100
    ) 
);
GO

This is the execution plan for the latest version:

And this is the execution plan for the original version (deadlock susceptible):

Clearly, the new version wins!

For comparison, the intermediate version with the (XLOCK) etc, produces the following plan:

I'd say that's a win! Thanks for everyone's help!

Answer 4

Not to steal Mark Storey-Smith's thunder, but he is onto something with his post above (that has incidentally received the most upvotes). The advice I gave Hannah was centered around the "UPDATE set @variable = column = column + value" construct which I find really cool, but I think may be undocumented (it has to be supported, though because it is there specifically for the TCP benchmarks).

Here is a variation of Mark's answer - because you are returning the new ID value as a recordset, you can do away with the scalar variable entirely, no explicit transaction should be necessary either, and I would agree that messing around with isolation levels is unnecessary as well. The result is very clean and pretty slick...

ALTER PROC [dbo].[GetNextID]
  @IDName nvarchar(255)
  AS
BEGIN
SET NOCOUNT ON;

DECLARE @Output TABLE ([NewID] INT);

UPDATE dbo.tblIDs SET LastID = LastID + 1
OUTPUT inserted.[LastId] INTO @Output
WHERE IDName = @IDName;

IF(@@ROWCOUNT = 1)
    SELECT [NewID] FROM @Output;
ELSE
    INSERT dbo.tblIDs (IDName, LastID)
    OUTPUT INSERTED.LastID AS [NewID]
    VALUES (@IDName,1);
END

Answer 5

I fixed a similar deadlock in a system last year by changing this:

IF (SELECT COUNT(IDName) FROM tblIDs WHERE IDName = @IDName) = 0 
  INSERT INTO tblIDs (IDName, LastID) VALUES (@IDName, @NewID)
ELSE
  UPDATE tblIDs SET LastID = @NewID WHERE IDName = @IDName;

To this:

UPDATE tblIDs SET LastID = @NewID WHERE IDName = @IDName;
IF @@ROWCOUNT = 0
BEGIN
  INSERT ...
END

In general, selecting a COUNT just to determine presence or absence is quite wasteful. In this case since it is either 0 or 1 it's not like it's a lot of work, but (a) that habit can bleed into other cases where it will be much costlier (in those cases, use IF NOT EXISTS instead of IF COUNT() = 0), and (b) the additional scan is completely unnecessary. The UPDATE performs essentially the same check.

Also, this looks like a serious code smell to me:

SET @NewID = COALESCE((SELECT LastID FROM tblIDs WHERE IDName = @IDName),0)+1;

What is the point here? Why not just use an identity column or derive that sequence using ROW_NUMBER() at query time?