在SQL Server中处理并发访问键表而没有死锁

我有一个表，该表由旧版应用程序用来替代其他各种表中的IDENTITY字段。

表中的每一行都存储了LastID中命名字段的最后使用的ID IDName。有时，存储的proc会出现死锁-我相信我已经构建了适当的错误处理程序；但是我很想看看这种方法是否能像我认为的那样起作用，或者我是否在这里树错了树。

我相当确定应该有一种方法来访问此方法该表完全没有任何死锁。

数据库本身配置有READ_COMMITTED_SNAPSHOT = 1。

首先，这里是该表：

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

和IDName字段上的非聚集索引：

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

一些示例数据：

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

用于更新表中存储的值并返回下一个ID的存储过程：

CREATE PROCEDURE [dbo].[GetNextID](
    @IDName nvarchar(255)
)
AS
BEGIN
    /*
        Description:    Increments and returns the LastID value from tblIDs
        for a given IDName
        Author:         Max 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

存储的proc的示例执行：
<

EXEC GetNextID 'SomeTestID';

NewID
2

EXEC GetNextID 'SomeTestID';

NewID
3

EXEC GetNextID 'SomeOtherTestID';

NewID
2

编辑：

我添加了一个新索引，因为SP没有使用现有的索引IX_tblIDs_Name。我假设查询处理器正在使用聚簇索引，因为它需要存储在LastID中的值。无论如何，实际执行计划都会使用该索引：

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);

编辑＃2：

@AaronBertrand给出的建议并对其进行了稍微的修改。此处的总体思路是优化语句以消除不必要的锁定，并总体上提高SP的效率。

下面的代码替换了上面的从BEGIN TRANSACTION到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;

由于我们的代码从不向LastID中的该表添加记录，因此我们可以假设，如果@NewID为1，则打算将新ID附加到列表中，否则我们将更新列表中的现有行。

与如何配置数据库以支持RCSI无关。您有意在此处升级为SERIALIZABLE。

是的，我只想添加所有相关信息。很高兴您确认这无关紧要！

使sp_getapplock成为死锁的受害者很容易，但是如果您开始事务，则不要调用sp_getapplock一次以获取排他锁，然后继续进行修改。

IDName是否唯一？然后建议“创建唯一的非聚集索引”。但是，如果您需要空值，那么索引也需要过滤。

#1 楼

首先，我将避免针对每个值往返数据库。例如，如果您的应用程序知道它需要20个新ID，则不要进行20次往返。只进行一个存储过程调用，并将计数器增加20。另外，将表拆分为多个表也可能更好。

可以完全避免死锁。我的系统中完全没有死锁。有几种方法可以做到这一点。我将展示如何使用sp_getapplock消除死锁。我不知道这是否对您有用，因为SQL Server是封闭源代码，所以我看不到源代码，因此我不知道我是否已经测试了所有可能的情况。

以下介绍对我有效的方法。 YMMV。

首先，让我们从一个总是出现大量死锁的场景开始。其次，我们将使用sp_getapplock消除它们。这里最重要的一点是对您的解决方案进行压力测试。您的解决方案可能有所不同，但是您需要使其具有高并发性，正如我稍后将演示的那样。

先决条件

让我们建立一个包含一些测试数据的表：

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

死锁很可能包含以下两个过程：

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

再现死锁

每次运行时，以下循环应重现20个以上的死锁。如果少于20，则增加迭代次数。

在一个选项卡中运行此命令；

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 ;

在另一个选项卡中运行这个脚本。

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 ;

请确保您都在几秒钟之内启动它们。

使用sp_getapplock消除死锁

更改两个过程，重新运行循环，然后查看不再有死锁：

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

使用具有一行的表消除死锁

我们可以调用下表来代替调用sp_getapplock：

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);

一旦创建并填充了该表，就可以在以下两个过程中用以下一行替换以下行：

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

br />
您可以重新运行压力测试，并亲自查看我们没有死锁。

结论

如我们所见，可以使用sp_getapplock序列化对其他资源的访问。因此，它可以用来消除死锁。

当然，这会大大减慢修改速度。为了解决这个问题，我们需要为排他锁选择合适的粒度，并在可能的情况下使用集合而不是单个行。

在使用这种方法之前，您需要自己进行压力测试。首先，您需要确保您的原始方法至少遇到十几个僵局。其次，使用修改后的存储过程重新运行相同的repro脚本时，应该不会出现死锁。

一般来说，我认为没有什么好的方法可以确定T-SQL是否可以避免死锁只是看它或看执行计划。 IMO确定您的代码是否容易出现死锁的唯一方法是使代码具有高并发性。

消除死锁的好运！我们的系统完全没有任何僵局，这对我们的工作与生活平衡非常有用。

+1作为sp_getapplock是一个鲜为人知的有用工具。鉴于“混乱的情况可能需要花费一些时间才能解决，将序列化的进程陷入僵局是一个方便的技巧。但是，对于这样一种容易理解并且可以（也许应该）通过标准锁定机制处理的情况，它是否应该是首选？

– Mark Storey-Smith
13年3月16日在1:07

@ MarkStorey-Smith这是我的首选，因为我仅对其进行了研究和压力测试，并且在任何情况下都可以重用它-序列化已经发生，因此sp_getapplock之后发生的所有事情都不会影响结果。使用标准的锁定机制，我永远不能确定-添加索引或仅获得另一个执行计划都可能导致死锁，而在此之前没有死锁。问我我怎么知道。

–A-K
13年3月16日在1:35

我想我缺少明显的东西，但是如何使用UPDATE dbo.DeadlockTestMutex SET Toggle = 1-Toggle WHERE ID = 1;防止死锁？

–戴尔K
19-09-13在4:14

#2 楼

在您的XLOCK方法或以下SELECT上使用UPDATE提示应该可以避免这种类型的死锁：

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;

还会返回一些其他变体（如果不被打败！）。

虽然XLOCK可以防止现有的计数器从多个连接中更新，但是您是否不需要TABLOCKX来防止多个连接添加相同的新计数器？

–戴尔K
19年9月13日在4:13

@DaleBurrell不，您将对IDName进行PK或唯一约束。

– Mark Storey-Smith
19-09-13在13:29

#3 楼

Mike Defehr向我展示了一种以非常轻巧的方式完成此操作的优雅方法：

ALTER PROCEDURE [dbo].[GetNextID](
    @IDName nvarchar(255)
)
AS
BEGIN
    /*
        Description:    Increments and returns the LastID value from tblIDs for a given IDName
        Author:         Max 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

（为完整性起见，这是与存储的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

这是最新版本的执行计划：

这是原始版本的执行计划版本（易受死锁影响）：

显然，新版本获胜！

作为比较，带有(XLOCK)的中间版本产生了以下计划：

我想这是胜利！谢谢大家的帮助！

确实应该可以，但是在不适用的地方使用了SERIALIZABLE。幻像行在这里不存在，那么为什么要使用一个存在的隔离级别来防止它们出现？同样，如果有人从另一个或从外部事务已启动的连接中调用您的过程，则他们发起的任何其他操作将在SERIALIZABLE处进行。可能会变得混乱。

– Mark Storey-Smith
13年3月14日在23:32

没有SERIALIZABLE可以防止幻影。它的存在是为了提供可序列化的隔离语义，即对数据库的持久影响，就好像所涉及的事务以某种未指定的顺序串行执行一样。

–保罗·怀特♦
2014年12月24日在6:46

#4 楼

不要偷走Mark Storey-Smith的风头，但他的职位上面有东西（顺带一提，是票数最多的东西）。我给Max的建议集中在“ UPDATE set @variable = column = column + value”结构上，我觉得这很酷，但我认为可能没有记载（必须予以支持，尽管它专门用于TCP基准）。这里是Mark答案的一种变体-因为您将新的ID值作为记录集返回，因此可以完全消除标量变量，也不需要显式事务，并且我同意，也不需要搞乱隔离级别。结果非常干净和光滑...

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

同意这应该可以避免死锁，但是如果您忽略该事务，则很容易在插件上出现竞争状态。

– Mark Storey-Smith
13年3月16日在14:50

#5 楼

我去年通过更改以下方法修复了系统中的类似死锁：

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;

对此：

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

通常，仅选择COUNT来确定是否存在非常浪费。在这种情况下，因为它是0或1，所以它并不是很多工作，但是（a）这个习惯可能会渗入其他情况下，而这会导致成本更高（在这些情况下，请使用IF NOT EXISTS而不是IF COUNT() = 0），并且（ b）完全不需要额外的扫描。 UPDATE执行基本上相同的检查。

此外，这对我来说似乎是一种严重的代码气味：

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

这里的意思是什么？为什么不只使用身份列或在查询时使用ROW_NUMBER()导出该序列？

我们拥有的大多数表都使用IDENTITY。该表支持一些用MS Access编写的遗留代码，这些代码很可能需要进行改造。 SET @ NewID =行仅增加给定ID的表中存储的值（但您已经知道）。您可以扩展我如何使用ROW_NUMBER（）吗？

– Max Vernon♦
13年3月13日在17:35

@MaxVernon并非不知道LastID在模型中的真正含义。目的是什么？这个名字并不完全是不言自明的。 Access如何使用它？

–亚伦·伯特兰（Aaron Bertrand）
2013年3月13日17:37

Access中的一个函数希望将行添加到任何没有IDENTITY的给定表中。首次访问调用GetNextID（'WhatevertheIDFieldIsCalled'）以获取下一个要使用的ID，然后将其与所需的任何数据一起插入到新行中。

– Max Vernon♦
13年3月13日在17:39

我将执行您的更改。一个纯粹的案例是“少即是多”！

– Max Vernon♦
13年13月13日在17:42

您固定的死锁可能会重新出现。您的第二种模式也很容易受到攻击：sqlblog.com/blogs/alexander_kuznetsov/archive/2010/01/12/…要消除死锁，我将使用sp_getapplock。可能有数百个用户的混合负载系统没有死锁。

–A-K
13年3月13日在18:09

编程黑洞网