如何在多个存储过程中使用事务？

Can you start a transaction in one stored procedure and then roll it back or commit it in a nested procedure?

您可以在一个存储过程中启动事务，然后将其回滚或在嵌套过程中提交吗？

4 个解决方案

#1

Commit and rollback have different effects

提交和回滚具有不同的效果

COMMIT decrements @@TRANCOUNT
COMMIT减少@@ TRANCOUNT
ROLLBACK pushes it back to zero
ROLLBACK将其推回零

This happens because SQL Server does not really support nested transactions.

发生这种情况是因为SQL Server并不真正支持嵌套事务。

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

如果您在嵌套的存储过程（而不是事务）中提交或回滚，那么由于启动和输入的@@ TRANCOUNT不匹配，您将生成错误266

The rollback issue can be resolved by using SET XACT_ABORT ON which is "auto rollback" (simply) and suppresses error 266.

回滚问题可以通过使用SET XACT_ABORT ON来解决，这是“自动回滚”（简单地）并且抑制错误266。

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.

提交问题......你不能这样。但是，您可以通过在存储的proc条目上注明@@ TRANCOUNT并仅在零时提交来控制它发生的位置。

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?

有关正确的事务处理，请在此处查看我的答案：包含TRY CATCH ROLLBACK模式的嵌套存储过程？并且我是否在T-SQL的catch块中回滚之前计算事务？

#2

You can't commit it in a nested procedure, but starting a transaction will wrap all nested procedures within it. So the transaction is good for all stored procedures nested within the transaction. In distributed transactions, data integrity even crosses machine boundaries.

您不能在嵌套过程中提交它，但启动事务将包装其中的所有嵌套过程。因此，该事务适用于嵌套在事务中的所有存储过程。在分布式事务中，数据完整性甚至跨越机器边界。

http://msdn.microsoft.com/en-us/library/ms188929(v=SQL.90).aspx

#3

You should pair up your BEGIN TRAN and COMMITs in the same SPROC

你应该在同一个SPROC中配对你的BEGIN TRAN和COMMIT

If you then call another SPROC which also has a transaction, subsequent BEGIN TRAN / COMMIT TRAN pairs will increment and decrement @@Trancount respectively.

如果您再调用另一个也有事务的SPROC，则后续的BEGIN TRAN / COMMIT TRAN对将分别递增和递减@@ Trancount。

The transaction is committed on the 'last' COMMIT TRAN (@@Trancount = 1)

交易在“最后一次”COMMIT TRAN（@@ Trancount = 1）上提交

However, any ROLLBACK will always roll back the transaction.

但是，任何ROLLBACK将始终回滚事务。

MSDN has a good explanation.

MSDN有一个很好的解释。

#4

Yes, it is possible. With programming languages like C#, when you pass the connection and transaction object with the command. if anything is caught as wrong than rollback the transaction:

对的，这是可能的。使用C＃等编程语言时，使用命令传递连接和事务对象。如果发现任何错误而不是回滚事务：

   string customerConnection = "Connection";
        string query = "insert into temp values ('Data2','data1','data2','data3')";
        string query2 = "update tempcst set data = 'Hello data'";

        SqlConnection myConnection = new SqlConnection(customerConnection);
        myConnection.Open();


        SqlTransaction myTrans = myConnection.BeginTransaction();

 Try{

        int result = executeNonQuery(query, myConnection, myTrans, "");
        i = executeNonQuery(query2, myConnection, myTrans, "");
   myTrans.Commit();}



  catch{
        myTrans.Rollback();
        myConnection.Close();
 }

#1