完全可以避免死锁.我的系统中根本没有死锁.有几种方法可以实现这一目标.我将展示如何使用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是确定代码是否容易出现死锁的唯一方法是将其暴露给高并发.

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