Manipulando o acesso simultâneo a uma tabela de chaves sem deadlocks no SQL Server

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.

Antes de usar essa abordagem, você precisa fazer um teste de estresse você mesmo. Primeiro, você precisa ter certeza de obter pelo menos algumas dúzias de impasses com sua abordagem original. Segundo, você não deve obter deadlocks ao executar novamente o mesmo script de reprodução usando o procedimento armazenado modificado.

Em geral, não acho que haja uma boa maneira de determinar se seu T-SQL está protegido contra deadlocks apenas observando-o ou observando o plano de execução. IMO a única maneira de determinar se o seu código é propenso a deadlocks é expô-lo a alta simultaneidade.

Boa sorte com a eliminação de impasses! Não temos nenhum impasse em nosso sistema, o que é ótimo para nosso equilíbrio entre vida profissional e pessoal.

O uso da XLOCKdica em sua SELECTabordagem ou no seguinte UPDATEdeve ser imune a esse tipo de impasse:

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;

Voltará com algumas outras variantes (se não for derrotado!).

Mike Defehr me mostrou uma maneira elegante de fazer isso de uma maneira muito leve:

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

(Para completar, aqui está a tabela associada ao proc armazenado)

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

Este é o plano de execução para a versão mais recente:

E este é o plano de execução para a versão original (suscetível a deadlock):

Claramente, a nova versão ganha!

Para efeito de comparação, a versão intermediária com o (XLOCK)etc, produz o seguinte plano:

Eu diria que é uma vitória! Obrigado pela ajuda de todos!

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

Corrigi um impasse semelhante em um sistema no ano passado, alterando isso:

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;

Para isso:

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

Em geral, selecionar um COUNTapenas para determinar presença ou ausência é um desperdício. Nesse caso, como é 0 ou 1, não é como se fosse muito trabalho, mas (a) esse hábito pode sangrar em outros casos em que será muito mais caro (nesses casos, use IF NOT EXISTSem vez de IF COUNT() = 0), e (b) a varredura adicional é completamente desnecessária. O UPDATEexecuta essencialmente a mesma verificação.

Além disso, isso parece um cheiro de código sério para mim:

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

Qual é o ponto aqui? Por que não usar apenas uma coluna de identidade ou derivar essa sequência usando ROW_NUMBER()no momento da consulta?