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

以下两个程序很可能会陷入僵局：

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是确定代码是否容易出现死锁的唯一方法是将其暴露给高并发.

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

（编辑：晋中站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!