MSSQL自动更新修改时间的AFTER触发器执行死锁问题

这类死锁和服务器负载高低没有关系，完全是触发器的设计缺陷导致的，核心有两个诱因：

• 首先是递归更新带来的额外锁开销：你创建的是AFTER INSERT, UPDATE类型的触发器，触发逻辑内部又对同一张Table1执行了UPDATE操作——这个UPDATE本身属于表的更新事件，会再次触发这个触发器。如果库级别开启了RECURSIVE_TRIGGERS配置，会直接形成递归调用链，直到触发SQL Server默认的32层递归上限报错；哪怕默认递归触发器是关闭状态，这个二次更新动作也会在原始INSERT/UPDATE事务已经持有行排他锁的基础上，额外申请更新锁、排他锁，大幅拉长事务持锁时间，还会产生冗余的事务日志。
• 其次是锁升级带来的循环等待：如果ID字段不是Table1的主键、没有建立聚集唯一索引，触发器里的UPDATE和INSERTED虚拟表关联时会走全表扫描，会把原本的行级锁升级为表级排他锁。这种场景下哪怕每分钟只有10次事务，只要两个并发事务交叉执行：比如事务A刚更新完行1、持有行1的锁，触发器全表扫描需要申请行2的锁；事务B刚更新完行2、持有行2的锁，触发器全表扫描需要申请行1的锁，就会直接形成死锁。

另外这个写法本身还有逻辑问题：就算没有死锁，每次插入/更新都会实际执行两次写操作，还会覆盖业务语句主动传入的LastModified值，合理性也有问题。

按优先级从高到低选择即可：

1. 最优方案：弃用AFTER触发器，从根源消除额外锁开销
   根本不需要用触发器实现这个需求：
   • 插入场景：给LastModified列加默认值约束，插入时不指定该列就会自动填充当前时间，零额外开销：

     ALTER TABLE Table1 
     ADD CONSTRAINT DF_Table1_LastModified DEFAULT GETDATE() FOR LastModified;
     

   • 更新场景：如果可以调整业务代码，直接在所有更新Table1的SQL的SET子句里加上LastModified = GETDATE()即可，全程只有一次表写入，没有任何触发器带来的额外开销，完全不会出现这类死锁。
2. 次优方案：换用INSTEAD OF触发器，避免二次更新
   如果不想改动业务代码，就删掉原来的AFTER触发器，换成INSTEAD OF类型，直接替换原始的插入/更新动作，在单次写入操作里直接给LastModified赋值，不会触发递归，也不会产生额外的锁：

   -- 先删除原有问题触发器
   DROP TRIGGER IF EXISTS updateDatetime;
   GO
   -- 处理插入逻辑
   CREATE TRIGGER trg_Table1_Insert
   ON Table1
   INSTEAD OF INSERT
   AS
   BEGIN
       SET NOCOUNT ON;
       -- 注意：此处按表实际字段顺序，填写除LastModified外的所有列名
       INSERT INTO Table1 (Col1, Col2, /* 其他业务字段 */ LastModified)
       SELECT Col1, Col2, /* 对应上面的业务字段顺序 */ GETDATE()
       FROM INSERTED;
   END
   GO
   -- 处理更新逻辑
   CREATE TRIGGER trg_Table1_Update
   ON Table1
   INSTEAD OF UPDATE
   AS
   BEGIN
       SET NOCOUNT ON;
       UPDATE t
       SET 
           -- 此处按实际字段做匹配，例如 t.Col1 = i.Col1, t.Col2 = i.Col2
           t.LastModified = GETDATE()
       FROM Table1 t
       INNER JOIN INSERTED i ON t.ID = i.ID;
   END
   GO
   

   这个方案的缺点是需要在触发器里写全所有业务字段，后续表结构变更时要同步修改触发器，适合表结构稳定的场景。
3. 临时快速修复：保留AFTER触发器，补充优化项
   如果暂时不想大改逻辑，只需要给原有触发器补两个优化点，基本就能解决死锁问题：
   • 首先确认ID字段是表的主键，给ID创建聚集唯一索引，保证触发器里的关联更新走单行查找，不会因为全表扫描导致锁升级；
   • 给触发器加嵌套层级判断阻断递归，同时增加判断避免重复更新业务已经主动设置了LastModified的行：

     ALTER TRIGGER updateDatetime
     ON Table1
     AFTER UPDATE, INSERT
     AS BEGIN
        SET NOCOUNT ON;
        -- 阻断递归：触发器自身触发的调用直接返回
        IF TRIGGER_NESTLEVEL(OBJECT_ID('updateDatetime'), 'AFTER', 'DML') > 1 RETURN;
        
        UPDATE Table1
        SET LastModified = GETDATE()
        FROM INSERTED i
        LEFT JOIN DELETED d ON i.ID = d.ID
        WHERE i.ID = Table1.ID
        -- 插入场景、或者更新时未修改LastModified列才执行更新
        AND (d.ID IS NULL OR NOT UPDATE(LastModified) OR i.LastModified = d.LastModified)
     END
     

内容的提问来源于stack exchange，提问作者Dave Hamilton