如何回滚存储过程中的事务? [英] How to rollback a transaction in a stored procedure?

问题描述

查看 SQL Server 联机丛书，Microsoft 似乎有一个(不正确的)在存储过程中处理嵌套事务的方法:

Looking at the SQL Server Books Online, Microsoft seems to have an (incorrect) method of handling nested transactions in a stored procedure:

显式事务可以嵌套.这主要是为了支持存储过程中的事务，这些事务既可以从已经在事务中的进程调用，也可以从没有活动事务的进程调用.

Nesting Transactions

Explicit transactions can be nested. This is primarily intended to support transactions in stored procedures that can be called either from a process already in a transaction or from processes that have no active transaction.

该示例继续显示一个存储过程，该过程启动自己的事务(无论执行它的任何进程的事务模式如何，该过程都会强制执行其事务."):

The example goes on to show a stored procedure that starts its own transaction ("The procedure enforces its transaction regardless of the transaction mode of any process that executes it."):

CREATE PROCEDURE TransProc @PriKey INT, @CharCol CHAR(3) AS
   BEGIN TRANSACTION InProc
      ...
   COMMIT TRANSACTION InProc;

然后可以在不运行事务的情况下调用此过程:

This procedure can then either be called without a transaction running:

EXECUTE TransProc 3,'bbb';

或使用显式交易:

BEGIN TRANSACTION OutOfProc;

EXEC TransProc 1, 'aaa';

COMMIT TRANSACTION OutOfProc

他们没有解决的是存储产品时会发生什么:

What they don't address is what happens when the stored produre:

• 因错误而失败，但事务仍在运行
• 因错误而失败，但不会让事务继续运行
• 遇到错误，但在事务打开的情况下继续执行
• 遇到错误，但继续执行并回滚事务

没有:

• SET XACT_ABORT ON
• @@TRANCOUNT

规范示例中的任何位置.

anywhere in the canonical example.

如果我不知道更好，我会认为这行:

If i didn't know any better, i would have thought that the line:

以下示例显示了嵌套事务的预期用途.

The following example shows the intended use of nested transactions.

实际上应该阅读

以下示例展示了如何不使用嵌套事务.

The following example shows the how not to use nested transactions.

除非有人可以对这个 BOL 示例做出正面或反面?

Unless someone can make heads or tails of this BOL example?

推荐答案

您需要在事务中使用 try catch 块.因此，如果您在 catch 块中遇到错误，那么您可以回滚您的事务.

You need to use the try catch block with the transaction. So in case you get the error in your catch block then you can rollback your transaction.

请查看下面的 sql server 代码.

Please see the below sql server code for that.

BEGIN TRANSACTION;

BEGIN TRY
    -- Some code
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH

    ROLLBACK TRANSACTION;
END CATCH;

这篇关于如何回滚存储过程中的事务?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！