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

Question

你能在一个存储过程中启动一个事务，然后在嵌套的存储过程中回滚或提交它吗？

- chama

Answer 1

提交（Commit）和回滚（Rollback）具有不同的效果

• COMMIT会减少@@TRANCOUNT
• ROLLBACK会将其推回零

这是因为SQL Server实际上不支持嵌套事务。

如果在嵌套存储过程（非事务）中提交或回滚，则由于开始和进入时的@@TRANCOUNT不匹配，您将生成错误266。

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

至于提交问题...您不能像这样做。但是，您可以通过在存储过程输入时注意@@TRANCOUNT，并仅在为零时提交来控制其发生位置。

有关正确的事务处理，请参见我的答案： Nested stored procedures containing TRY CATCH ROLLBACK pattern? 和 Have I to count transactions before rollback one in catch block in T-SQL?

- gbn

Answer 2

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

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

- Robert Harvey

Answer 3

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

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

事务将在“最后”的 COMMIT TRAN (@@Trancount = 1) 上提交。

然而，任何 ROLLBACK 都会回滚事务。

MSDN 有一个很好的解释。

- StuartLC

Answer 4

是的，这是可能的。使用像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();
 }

- C S Tiwari