SQL Server触发器中两种更新逻辑的性能与死锁风险对比

场景说明 #

表结构 #

• Table1结构：

ProductId int(primary key), CurrentStatus, CurrentStatusDate

• Table2结构：

NewProductId, NewStatus, NewStatusDate

在Table2的插入触发器中，从inserted表获取@NewProductId、@NewStatus、@NewStatusDate，有两种更新Table1的处理方式：

方式1 #

SELECT @CurrentStatusDate = CurrentStatusDate
FROM Table1
WHERE ProductId = @NewProductId

IF @NewStatusDate >= @CurrentStatusDate
BEGIN
    UPDATE Table1 SET
        CurrentStatus = @NewStatus
        , CurrentStatusDate = @NewStatusDate
    WHERE ProductId = @NewProductId
END

方式2 #

UPDATE Table1 SET
  CurrentStatus = @NewStatus
  , CurrentStatusDate = @NewStatusDate
WHERE ProductId = @NewProductId
AND CurrentStatusDate <= @NewStatusDate

已知触发器每日运行数万至数十万次，其中90%的场景下NewStatusDate晚于Table1的CurrentStatusDate，需要执行更新。

性能对比 #

方式1需要对Table1的主键执行两次查找：先SELECT获取当前状态日期，再在满足条件时执行UPDATE。由于90%的场景都要执行UPDATE，等于绝大多数请求都要做两次重复的主键IO操作。

方式2将判断条件直接整合到UPDATE的WHERE子句中，只需要一次主键查找操作，无论是否满足更新条件，都只触发一次IO。这种情况下，方式2的性能大幅更优，能减少大量重复的磁盘IO和锁资源消耗。

死锁风险对比 #

方式1的操作分为两步：SELECT加共享锁（默认隔离级别下读取后立即释放），之后再尝试加排他锁执行UPDATE。这中间存在时间窗口，若其他事务同时操作同一个ProductId，可能出现事务A等待事务B释放锁、事务B等待事务A释放锁的死锁场景。

方式2是原子性的UPDATE操作，直接在查找时判断条件并完成更新，锁的持有时间极短，没有中间的时间窗口，死锁风险大幅降低。

结论 #

方式2在性能和死锁风险方面均大幅更优，非常适合当前高触发频率、高更新占比的场景。

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