sql-server – 在SQL Server中处理对密钥表的并发访问而不会出现死锁

前端之家收集整理的这篇文章主要介绍了sql-server – 在SQL Server中处理对密钥表的并发访问而不会出现死锁前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。
我有一个表,遗留应用程序使用它作为各种其他表中的IDENTITY字段的替代.

表中的每一行都存储IDName中指定的字段的最后使用的ID LastID.

偶尔存储的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,@ET);
    END
    ELSE
        SELECT @NewID AS NewID;
END
GO

存储过程的示例执行:

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_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中为此表添加0记录,因此我们可以假设如果@NewID为1,则意图将新ID添加到列表中,否则我们将更新列表中的现有行.

解决方法

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

完全可以避免死锁.我的系统中根本没有死锁.有几种方法可以实现这一目标.我将展示如何使用sp_getapplock来消除死锁.我不知道这是否适合你,因为sql Server是封闭源代码,所以我看不到源代码,因此我不知道我是否已经测试了所有可能的情况.

以下描述了适合我的方法.因人而异.

首先,让我们从一个总是会遇到大量死锁的场景开始.其次,我们将使用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=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';

在这两个程序中:

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

您可以重新运行压力测试,并亲眼看看我们没有死锁.

结论

正如我们所见,sp_getapplock可用于序列化对其他资源的访问.因此,它可以用于消除死锁.

当然,这可以显着减慢修改速度.为了解决这个问题,我们需要为独占锁选择合适的粒度,并尽可能使用集而不是单独的行.

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

一般来说,我认为只有通过查看或查看执行计划,才能确定您的T-sql是否安全无死锁. IMO是确定代码是否容易出现死锁的唯一方法是将其暴露给高并发.

祝你好运,消除死锁!我们的系统根本没有任何僵局,这对我们的工作与生活平衡很有帮助.

原文链接:https://www.f2er.com/mssql/80098.html

猜你在找的MsSQL相关文章