sql-server – 如何从一个存储过程启动3个存储过程时回滚

我有一个存储过程,只在其中执行3个存储过程.如果主SP成功,我只使用1个参数来存储.

如果第一个存储过程在主存储过程中正常工作,但第二个存储过程失败,那么它会自动回滚主SP中的所有SP还是我必须做一些命令？

这是我的程序：

CREATE PROCEDURE [dbo].[spSavesomename] 
    -- Add the parameters for the stored procedure here

    @successful bit = null output
AS
BEGIN
begin transaction createSavebillinginvoice
    begin Try
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

   BEGIN 

   EXEC [dbo].[spNewBilling1]

   END

   BEGIN 

   EXEC [dbo].[spNewBilling2]

   END

   BEGIN 

   EXEC [dbo].[spNewBilling3]

   END 

   set @successful  = 1

   end Try

    begin Catch
        rollback transaction createSavesomename
        insert into dbo.tblErrorMessage(spName,errorMessage,systemDate) 
             values ('spSavesomename',ERROR_MESSAGE(),getdate())

        return
    end Catch
commit transaction createSavesomename
return
END

GO

解决方法

但是这里有一些关于事务的注意事项(至少在SQL Server中)：

>无论您多少次拨打BEGIN TRAN,都只有一次真正的交易(第一次)

>您可以命名一个事务(就像您在此处所做的那样)并且该名称将出现在日志中,但命名仅对第一个/最外部事务具有意义(因为同样,第一个事务是事务).
>每次调用BEGIN TRAN时,无论是否命名,事务计数器都会增加1.
>您可以通过SELECT @@ TRANCOUNT查看当前级别;
>当@@ TRANCOUNT为2或更高时发出的任何COMMIT命令都只能减少,一次减少一个事务计数器.
>在@@ TRANCOUNT为1时发出COMMIT之前,没有任何事情发生
>以防上述信息未明确指出：无论交易级别如何,都没有实际的交易嵌套.

>保存点允许在事务中创建可撤消的工作子集.

>通过SAVE TRAN {save_point_name}命令创建/标记保存点
>保存点标记可以撤消的工作子集的开头,而不回滚整个事务.
>保存点名称不必是唯一的,但多次使用相同的名称仍会创建不同的保存点.
>保存点可以嵌套.
>无法提交保存点.
>可以通过ROLLBACK {save_point_name}撤消保存点. (以下更多内容)
>回滚保存点将撤消在最近一次调用SAVE TRAN {save_point_name}之后发生的任何工作,包括在创建回滚之后创建的任何保存点(因此“嵌套”).
>回滚保存点对事务计数/级别没有影响
>除了通过发出整个交易的完整ROLLBACK之外,在初始SAVE TRAN之前完成的任何工作都无法撤消.
>只是要明确：当@@ TRANCOUNT为2或更高时发出COMMIT对保存点没有影响(因为在该计数器之外不存在高于1的事务级别).

>您无法提交特定的命名事务.事务“名称”(如果与COMMIT一起提供)将被忽略,仅出于可读性而存在.
>没有名称的ROLLBACK将始终回滚所有事务.
>使用名称发出的ROLLBACK必须对应于：

>第一个事务,假设它被命名为：
假设没有使用相同的事务名称调用SAVE TRAN,这将回滚所有事务.
>“保存点”(如上所述)：
此行为将“撤消”自最近的SAVE TRAN {save_point_name}被调用以来所做的所有更改.
>如果第一个事务是a)命名并且b)已经发出了带有其名称的SAVE TRAN命令,那么该事务名称的每个ROLLBACK将撤消每个保存点,直到该名称没有剩余.之后,发出该名称的ROLLBACK将回滚所有事务.
>例如,假设以下显示的顺序运行以下命令：

BEGIN TRAN A -- @@TRANCOUNT is now 1
-- DML Query 1
SAVE TRAN A
-- DML Query 2
SAVE TRAN A
-- DML Query 3

BEGIN TRAN B -- @@TRANCOUNT is now 2
SAVE TRAN B
-- DML Query 4

现在,如果您发布(以下每个方案彼此独立)：

> ROLLBACK TRAN B一次：它将撤消“DML Query 4”. @@ TRANCOUNT仍然是2.
> ROLLBACK TRAN B两次：它将撤消“DML Query 4”然后出错,因为“B”没有相应的保存点. @@ TRANCOUNT仍然是2.
> ROLLBACK TRAN A一次：它将撤消“DML Query 4”和“DML Query 3”. @@ TRANCOUNT仍然是2.
> ROLLBACK TRAN A两次：它将撤消“DML Query 4”,“DML Query 3”和“DML Query 2”. @@ TRANCOUNT仍然是2.
> ROLLBACK TRAN三次：它将撤消“DML Query 4”,“DML Query 3”和“DML Query 2”.然后它将回滚整个事务(剩下的就是“DML Query 1”). @@ TRANCOUNT现在为0.
> COMMIT一次：@@ TRANCOUNT降为1.
> COMMIT一次然后ROLLBACK TRAN B一次：@@ TRANCOUNT下降到1.然后它将撤消“DML Query 4”(证明COMMIT没有做任何事情). @@ TRANCOUNT仍然是1.

>交易名称和保存点名称：

>最多可包含32个字符
>被视为具有二进制排序规则(不像文档当前所述的区分大小写),无论实例级别还是数据库级别排序规则.
>有关详细信息,请参阅以下帖子的“交易名称”部分：What’s in a Name?: Inside the Wacky World of T-SQL Identifiers

>存储过程本身不是隐式事务.如果没有启动显式事务,则每个查询都是隐式事务.这就是为什么不需要围绕单个查询的显式事务,除非可能有编程原因来执行ROLLBACK,否则查询中的任何错误都是该查询的自动回滚.
>调用存储过程时,它必须以@@ TRANCOUNT的值与调用时相同的方式退出.意思是,你不能：

>在proc中启动BEGIN TRAN而不提交它,期望在调用/父进程中提交.
>如果在调用proc之前启动了显式事务,则无法发出ROLLBACK,因为它会将@@ TRANCOUNT返回到0.

如果退出存储过程的事务计数高于或低于它的凝视时间,则会出现类似于以下内容的错误：

Msg 266,Level 16,State 2,Procedure YourProcName,Line 0
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = X,current count = Y.

>表变量与常规变量一样,不受事务约束.

关于在proc中进行事务处理,可以独立调用(因此需要事务处理)或从其他proc调用(因此不需要事务处理)：这可以通过几种不同的方式完成.

我多年来一直处理它的方式似乎运行良好的是在最外层只有BEGIN / COMMIT / ROLLBACK.子proc调用只是跳过事务命令.我在下面概述了我在每个proc中放入的内容(好吧,每个需要事务处理的内容).

>在每个proc的顶部,DECLARE @InBestedTransaction BIT;
>代替简单的BEGIN TRAN,执行：

IF (@@TRANCOUNT = 0)
BEGIN
   SET @InNestedTransaction = 0;
   BEGIN TRAN; -- only start a transaction if not already in one
END;
ELSE
BEGIN
   SET @InNestedTransaction = 1;
END;

>代替简单的COMMIT,执行：

IF (@@TRANCOUNT > 0 AND @InNestedTransaction = 0)
BEGIN
   COMMIT;
END;

>代替简单的ROLLBACK,执行：

IF (@@TRANCOUNT > 0 AND @InNestedTransaction = 0)
BEGIN
   ROLLBACK;
END;

无论事务是在SQL Server中启动还是在应用层启动,此方法都应该相同.

有关TRY … CATCH结构中此事务处理的完整模板,请参阅以下DBA.SE问题的答案：Are we required to handle Transaction in C# Code as well as in stored procedure.

超越“基础”,还需要注意一些额外的交易细微差别：

>默认情况下,大多数情况下,发生错误时,事务不会自动回滚/取消.只要您有适当的错误处理并自己调用ROLLBACK,这通常不是问题.但是,有时事情变得复杂,例如在批量中止错误的情况下,或者在使用OPENQUERY(或一般的链接服务器)时,并且远程系统上发生错误.虽然大多数错误都可以使用TRY … CATCH来捕获,但是有两个错误不能被捕获(不记得当前哪些错误 – 研究).在这些情况下,必须使用SET XACT_ABORT ON才能正确回滚事务.

SET XACT_ABORT ON使SQL Server立即回滚任何事务(如果一个处于活动状态)并在发生任何错误时中止批处理.此设置存在于SQL Server 2005之前,它引入了TRY … CATCH构造.在大多数情况下,TRY … CATCH处理大多数情况,因此大多数都废弃了对XACT_ABORT ON的需求.但是,当使用OPENQUERY(可能还有一个我目前不记得的场景)时,您仍然需要使用SET XACT_ABORT ON;.
>在触发器内部,XACT_ABORT隐式设置为ON.这会导致触发器中的任何错误取消触发触发器的整个DML语句.
>您应始终进行适当的错误处理,尤其是在使用事务时. SQL Server 2005中引入的TRY … CATCH构造提供了一种处理几乎所有情况的方法,这是在每个语句之后对@@ ERROR进行测试的一种受欢迎的改进,这对批量中止错误没有多大帮助.

然而,TRY …… CATCH引入了一个新的“状态”.当不使用TRY … CATCH构造时,如果您有一个活动的事务并且发生错误,那么可以采用几个路径：

> XACT_ABORT OFF和语句中止错误：事务仍处于活动状态,并继续处理下一个语句(如果有).
> XACT_ABORT OFF和大多数批量中止错误：事务处于活动状态,处理将继续进行下一批处理(如果有).
> XACT_ABORT OFF和某些批量中止错误：回滚事务并继续处理下一批(如果有).
> XACT_ABORT ON和任何错误：回滚事务并继续处理下一批(如果有).

但是,当使用TRY … CATCH时,批量中止错误不会中止批处理,而是将控制转移到CATCH块.当XACT_ABORT为OFF时,交易在绝大多数时间内仍然处于活动状态,您需要COMMIT,或者很可能需要ROLLBACK.但是当遇到某些批量中止错误(例如使用OPENQUERY)或XACT_ABORT为ON时,事务将处于新状态,“不可拒绝”.在这种状态下你不能COMMIT,也不能做任何DML操作.您所能做的就是ROLLBACK和SELECT语句.但是,在这种“不可注册”状态下,事务在发生错误时回滚,并且发出ROLLBACK只是一种形式,但必须完成.

（编辑：安卓应用网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!