最佳实践：SQL存储过程序列化执行

我之前碰到过一模一样的场景：有个在行插入后触发执行的存储过程（SP），它会在事务内更新多张表。当多行插入引发这个SP并发执行时，死锁问题就频繁出现了。之前试过各种优化手段降低死锁概率，但始终没法彻底根除。

后来我们尝试了用应用级锁来串行化关键操作的方案，具体代码如下：

DECLARE @padLock INT
BEGIN TRY
    EXEC @padLock = sp_getapplock 
        @Resource='mytable_modify', 
        @LockMode='Exclusive', 
        @LockOwner='Transaction',
        @LockTimeout=10000; -- 可根据业务需求调整超时时间

    -- 这里编写原存储过程中的事务逻辑：更新多张表的操作
    -- ...

END TRY
BEGIN CATCH
    -- 异常处理逻辑（注：若@LockOwner设为Transaction，事务结束时锁会自动释放，无需手动处理）
    -- ...
    THROW;
END CATCH

方案核心思路 #

• 通过sp_getapplock获取名为mytable_modify的独占锁，把所有执行该SP的请求串行化，从根源上避免并发修改多张表时出现循环等待导致的死锁
• 将@LockOwner设为Transaction，锁会与当前事务绑定，事务提交或回滚时自动释放锁，省去手动释放锁的麻烦
• 可通过@LockTimeout参数设置等待锁的超时时间，避免请求无限阻塞

注意事项 #

• 要处理sp_getapplock的返回值：返回-1表示超时，-2表示被取消，-3表示死锁，这些情况需要在业务逻辑中做对应处理（比如重试或返回错误提示）
• 这种串行化方式会降低并发性能，需要评估业务场景的并发量是否能接受这种取舍
• 确保@Resource的命名唯一，避免和其他业务的应用锁产生冲突

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