SQL Server 2019中COMMIT TRAN未生效问题咨询与排查

问题描述 #

• 执行包含显式事务的SQL代码后，SSMS显示命令执行成功，但关闭窗口时提示存在未提交事务。代码中明明写了BEGIN TRAN和COMMIT TRAN，事务却没完全提交。
• 看到有建议把服务器设置改成显式事务而非隐式，但自己代码已经是显式事务，为啥还要改？这种场景下该怎么处理？
• 加打印语句排查发现，UPDATE操作触发了新事务，@@Trancount数值从1涨到了2，提交一次后还是1，难道要写两次COMMIT TRAN？SQL Server 2019或SSMS是不是有版本变更导致这种情况？之前从没遇到过要双重提交的情况。

代码：

set xact_abort on
print '1 @@Trancount: ' + cast(coalesce(@@trancount, 0) as nvarchar(100))
begin try
print '2 @@Trancount: ' + cast(coalesce(@@trancount, 0) as nvarchar(100))
begin tran
print '3 @@Trancount: ' + cast(coalesce(@@trancount, 0) as nvarchar(100))
update [Production Order]
set [SO Number] = '0063420', [SO Line Item] = '', Parent = '553306000'
where [PrO Number] = '553306001'
print '4 @@Trancount: ' + cast(coalesce(@@trancount, 0) as nvarchar(100))
commit tran
print '5 @@Trancount: ' + cast(coalesce(@@trancount, 0) as nvarchar(100))
end try
begin catch
    print '6 @@Trancount: ' + cast(coalesce(@@trancount, 0) as nvarchar(100))
    rollback tran
    print '7 @@Trancount: ' + cast(coalesce(@@trancount, 0) as nvarchar(100))
    select ERROR_MESSAGE()
end catch
print '8 @@Trancount: ' + cast(coalesce(@@trancount, 0) as nvarchar(100))

输出结果：

1 @@Trancount: 0
2 @@Trancount: 0
3 @@Trancount: 1

(1 row affected)
4 @@Trancount: 2
5 @@Trancount: 1
8 @@Trancount: 1

Completion time: 2024-07-24T12:48:00.9458946-07:00

原因分析与解决方案 #

1. 事务嵌套的核心原因 #

@@Trancount从1变成2，说明你的UPDATE操作触发了隐式事务嵌套，大概率是目标表[Production Order]上定义了触发器：要么触发器内部显式写了BEGIN TRAN，要么触发器的执行自动纳入了嵌套事务（SQL Server中触发器默认会在当前事务上下文运行，若触发器里有DML操作，会自动累加事务计数）。

2. 关于显式/隐式事务模式的疑问 #

服务器的隐式事务模式（SET IMPLICIT_TRANSACTIONS ON）会让执行DML后自动开启事务，但你代码开头的@@Trancount是0，说明当前会话不是隐式模式。网上那种改模式的建议对你这个场景不适用，不用管。

3. 正确处理方式 #

不需要写两次COMMIT TRAN，重点要找到嵌套事务的来源：

• 检查[Production Order]表的触发器：查看触发器代码里有没有额外开启事务的逻辑，如果触发器不需要独立事务，确保它在当前事务上下文执行即可，别画蛇添足加BEGIN TRAN。
• 兜底清理方案：如果暂时找不到根源，可以在代码末尾加一段逻辑，把剩余的事务提交完：

  while @@trancount > 0
  begin
      commit tran
  end
  

  不过更推荐从触发器入手解决，这才是治本的办法。

4. 版本变更的影响 #

SQL Server 2019和SSMS没有强制要求双重提交的版本变更，这个问题本质是触发器或会话级设置导致的事务嵌套，和版本没关系。

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