问题描述
您可以在一个存储过程中启动事务，然后回滚还是在嵌套过程中提交它吗?
Can you start a transaction in one stored procedure and then roll it back or commit it in a nested procedure?
推荐答案
提交和回滚具有不同的效果
Commit and rollback have different effects
- COMMIT递减@@ TRANCOUNT
- ROLLBACK将其推回零
发生这种情况是因为SQL Server并不真正支持嵌套事务.
This happens because SQL Server does not really support nested transactions.
如果您在嵌套的存储过程中提交或回滚(不是事务)，则由于开始和输入处的@@ TRANCOUNT不匹配，您将生成错误266
If you commit or rollback in a nested stored proc (not transaction), then you'll generate error 266 because of a @@TRANCOUNT mismatch on start and entry
回退问题可以通过使用SET XACT_ABORT ON(简单地)并自动消除错误266来解决.
The rollback issue can be resolved by using SET XACT_ABORT ON which is "auto rollback" (simply) and suppresses error 266.
提交问题...您不能这样.但是，您可以通过在存储的proc条目上注明@@ TRANCOUNT，并在 only 为零的情况下提交，来控制它的发生位置.
The commit issue... you can't as such. However, you can control where it happens by noting @@TRANCOUNT on stored proc entry and committing only if zero.
为正确进行交易处理，请在此处查看我的答案:包含TRY CATCH ROLLBACK模式的嵌套存储过程?和在T-SQL的catch块中进行回滚之前，我是否需要计算事务?
For correct transaction handling, see my answers here please: Nested stored procedures containing TRY CATCH ROLLBACK pattern? and Have I to count transactions before rollback one in catch block in T-SQL?
这篇关于如何在多个存储过程上使用事务?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！