SQL事务提交后意外回滚的诱因及排查追踪方法咨询

异常可能原因 #

• 嵌套事务/外层事务回滚：当前脚本里用WHILE @@TRANCOUNT > 0 COMMIT TRAN会提交所有层级的事务，但如果调用方（应用层、上层存储过程）在调用这个脚本之前开启了事务未提交，后续触发外层回滚，会把当前脚本里已经提交的操作全部回滚。这种情况不会触发当前脚本的CATCH逻辑，也不会留下错误记录。
• 读操作误判事务状态：如果打印凭证的查询语句加了WITH (NOLOCK)查询提示，会读取到未提交的脏数据。如果后续事务因为报错回滚，会出现误以为事务已经提交成功、实际并未提交的情况。
• 可用性组副本同步问题：如果你的SQL Server部署了Always On可用性组，且读请求路由到了异步提交的辅助副本，当主节点发生故障转移时，主节点上已经提交但未同步到旧辅助副本的事务会被回滚，此时在副本上查询就会出现数据消失的情况。
• 后台作业误删除数据：如果有定时归档、数据清理类的定时作业，存在逻辑漏洞（比如时间条件判断错误、主键匹配错误），极低概率下会刚好删除刚写入的过账数据，看起来和事务回滚现象一致。
• 触发器隐藏逻辑：如果过账涉及的表上有INSTEAD OF或AFTER触发器，触发器内存在回滚、数据删除逻辑，或者触发了未被捕获的错误，可能导致事务整体回滚但上层没有拿到完整错误信息。
• 分布式事务(DTC)异常：如果过账逻辑涉及跨库、跨实例的分布式事务，DTC服务出现瞬时故障、网络抖动时，可能会触发分布式事务的全局回滚，部分场景下不会在本地SQL Server日志留下明显错误。

定位与日志优化方案 #

• 新增审计埋点：在事务COMMIT前新增审计表写入逻辑，将本次过账的唯一业务单号、事务IDXACT_ID()、操作时间、当前实例名、用户信息写入永久审计表。如果后续业务数据消失但审计表存在对应记录，说明事务确实提交成功，问题出在后续数据删除操作；如果审计表也无记录，说明事务最终未提交。
• 优化错误捕获逻辑：将当前脚本的RAISERROR替换为SQL Server 2012及以上版本支持的THROW，避免错误信息被截断。同时在CATCH块中新增错误日志写入逻辑，把错误号、错误信息、业务单号、时间戳永久写入错误日志表，避免错误信息丢失。
• 启用扩展事件跟踪：创建扩展事件会话捕获sqlserver.transaction_commit、sqlserver.transaction_rollback、sqlserver.error_reported事件，过滤过账逻辑对应的数据库ID、客户端应用名，长期运行收集异常时刻的事务状态数据。
• 排查环境配置：检查是否开启了读副本路由、打印凭证的查询是否用了NOLOCK提示、是否存在未受控的定时数据清理作业、涉及的表是否存在异常触发器逻辑。
• 事务日志回溯：保留完整的事务日志备份，出现异常时用fn_dblog函数查询指定时间范围内的事务日志，定位对应业务单号的事务最终是提交还是回滚，是否存在后续的DELETE操作记录。

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