从一个存储过程启动3个存储过程时如何回滚

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

#1 楼

仅给出问题中显示的代码，并假设三个子进程均未进行任何显式事务处理，则是的，这三个子进程中的任何一个都会捕获错误，并且ROLLBACK块中的CATCH将滚动支持所有工作。

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



一次真正的交易（第一个），无论您调用多少次BEGIN TRAN


您都可以为交易命名（如此处所做的那样），该名称将出现在日志中，但命名仅对第一个/最外层事务有意义（因为，​​第一个是事务）。
每次调用BEGIN TRAN时，无论它是否命名，事务计数器都增加1 。
通过执行SELECT @@TRANCOUNT;可以看到当前水平

当COMMIT等于或大于2时发出的任何@@TRANCOUNT命令无非是减少一次，一次t交易计数器。
在COMMIT位于@@TRANCOUNT时发出1之前，不会做出任何操作

只要以上信息不能清楚地表明：无论交易级别如何，没有实际的事务嵌套。



保存点允许在事务内创建可以撤消的工作子集。


通过SAVE TRAN {save_point_name}命令创建/标记保存点
保存点标记了可以撤消而无需回滚整个事务的工作子集的开始。
保存点名称不需要唯一，但多次使用相同的名称仍会创建不同的保存点。
可以嵌套保存点。
不能提交保存点。
可以通过ROLLBACK {save_point_name}撤消保存点。 （有关此内容，请参见下文）
回滚保存点将撤消最近一次调用SAVE TRAN {save_point_name}之后发生的任何工作，包括创建被回滚的保存点之后创建的所有保存点（因此称为“嵌套”）。
回滚保存点对事务计数/级别没有影响
除非发出整个事务的完整SAVE TRAN，否则在初始ROLLBACK之前完成的任何工作都无法撤消。
请注意：当COMMIT为等于或大于2时，对保存点没有影响（同样，因为大于1的事务级别不在该计数器之外）。


您不能提交特定的命名事务。事务“名称”（如果与@@TRANCOUNT一起提供）将被忽略，并且仅出于可读性而存在。
不带名称的COMMIT将始终回滚所有事务。名称必须与以下任一名称相对应：


假设第一个交易名为：
假设没有使用相同的交易名称调用ROLLBACK，则将回滚所有交易。 br />一个“保存点”（如上所述）：
此行为将“撤消”自最近一次调用ROLLBACK以来所做的所有更改。发出带有其名称的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

<现在，如果发出问题（以下两种情况彼此独立）：



SAVE TRAN {save_point_name}一次：它将撤消“ DML查询4”。 SAVE TRAN仍然是2。

ROLLBACK TRAN B两次：它将撤消“ DML查询4”，然后由于没有对应的“ B”保存点而出错。 @@TRANCOUNT仍为2。

ROLLBACK TRAN B一次：将撤消“ DML查询4”和“ DML查询3”。 @@TRANCOUNT仍然是2。

ROLLBACK TRAN A两次：它将撤消“ DML查询4”，“ DML查询3”和“ DML查询2”。 @@TRANCOUNT仍然是2。

ROLLBACK TRAN A三次：它将撤消“ DML查询4”，“ DML查询3”和“ DML查询2”。然后它将回滚整个事务（剩下的只是“ DML查询1”）。 @@TRANCOUNT现在为0。

ROLLBACK TRAN A一次：@@TRANCOUNT下降至1。 4英寸（证明COMMIT没有做任何事情）。 COMMIT仍为1。





事务名称和保存点名称：


可以具有最多32个字符
被视为具有二进制排序规则（当前文档不区分大小写），无论实例级别的排序规则还是数据库级别的排序规则。
有关详细信息，请参阅事务以下帖子的“名称”部分：名称中有什么？：在T-SQL标识符的古怪世界内



。如果没有启动任何显式事务，则每个查询都是一个隐式事务。这就是为什么除非有程序上的原因要执行@@TRANCOUNT，否则不需要围绕单个查询进行显式事务的原因，否则查询中的任何错误都是该查询的自动回滚。

调用存储过程时，它必须退出，并且COMMIT的值与调用时的值相同。意味着，您不能：


在proc中启动ROLLBACK TRAN B而不提交它，而希望在调用/父进程中提交。在调用proc之前启动，因为它将使@@TRANCOUNT返回0。

如果退出的存储过程的事务计数高于或低于它的开始计数，则会收到类似于以下消息的错误：消息266，级别16，状态2，过程YourProcName，第0行

与常规变量一样，表变量不受事务约束。


关于拥有可以独立调用（因此需要事务处理）或从其他proc调用（因此不需要事务处理）的proc中的事务处理：这可以通过几种不同的方式来实现。

我已经处理了好几年了，看来效果很好，只是在最外层使用了@@TRANCOUNT / ROLLBACK / @@TRANCOUNT。子过程调用仅跳过事务命令。我在下面概述了我在每个proc中放置的内容（嗯，每个需要事务处理的东西）。


在每个proc的顶部，BEGIN TRAN

< br代替简单的ROLLBACK，执行：

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;

代替简单的@@TRANCOUNT，执行：

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

<代替简单的BEGIN，请执行以下操作：

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

无论是否事务是在SQL Server中启动的，或者是在应用程序层启动的。有关COMMIT构造中此事务处理的完整模板，请参阅以下DBA.SE问题的答案：我们需要使用C＃代码以及存储过程来处理事务。


除了“基本”之外，还有一些其他细微的事务需要注意：



默认情况下，在大多数情况下，发生错误时，事务不会自动回滚/取消。只要您有适当的错误处理并亲自致电ROLLBACK，这通常就不成问题。但是，有时情况会变得复杂，例如在发生中止批处理错误的情况下，或者在使用DECLARE @InNestedTransaction BIT;（或通常为链接服务器）时，在远程系统上会发生错误。尽管可以使用BEGIN TRAN捕获大多数错误，但是有两种无法以这种方式捕获（尽管现在无法记住，但正在搜索）。在这些情况下，必须使用COMMIT正确回滚事务。

SET XACT_ABORT ON会使SQL Server立即回滚任何事务（如果一个活动），并在发生任何错误时中止批处理。此设置存在于SQL Server 2005之前，后者引入了ROLLBACK构造。在大多数情况下，TRY...CATCH处理大多数情况，因此大多数情况下不再需要ROLLBACK。但是，当使用OPENQUERY（以及目前可能我还不记得的其他情况）时，您仍将需要使用TRY...CATCH。 。这会导致触发器内的任何错误，从而取消触发触发器的整个DML语句。

您应该始终进行正确的错误处理，尤其是在使用事务时。 SQL Server 2005中引入的SET XACT_ABORT ON构造提供了一种处理几乎所有情况的方法，这是对每条语句后对TRY...CATCH进行测试的可喜的改进，这对于批量中止错误没有太大帮助。

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



OPENQUERY和中止语句错误：事务仍然处于活动状态，并且继续处理下一条语句（如果有的话）。

SET XACT_ABORT ON;和某些中止批处理的错误：事务已回滚，并继续处理下一个批处理（如果有的话）。回滚并继续处理下一个批处理。


但是，使用XACT_ABORT时，中止批处理错误不会中止该批处理，而是将控制权转移到ON块。当TRY...CATCH为@@ERROR时，该交易在大多数时间仍将处于活动状态，您将需要TRY...CATCH或最有可能是TRY...CATCH。但是，当遇到某些批中止错误时（例如XACT_ABORT OFF），或者当XACT_ABORT OFF为XACT_ABORT OFF时，事务将处于新状态“不可提交”。在这种状态下，您将无法XACT_ABORT ON，也无法进行任何DML操作。您所能做的就是TRY...CATCH和CATCH语句。但是，在这种“无法使用”状态下，事务在发生错误时已回滚，并且发出XACT_ABORT只是一种形式，但这是必须完成的一种形式。

函数XACT_STATE可用于确定事务是处于活动状态，不可提交状态还是不存在。建议（至少有人）在OFF块中检查此功能以确定结果是否为COMMIT（即不可提交），而不是测试ROLLBACK。但是，对于OPENQUERY，这应该是唯一可能的状态，因此似乎对XACT_ABORT和ON的测试是等效的。另一方面，当COMMIT为ROLLBACK且存在活动事务时，则SELECT块中的状态可能为ROLLBACK或CATCH，这允许发出-1而不是@@TRANCOUNT > 0的可能性（尽管我不能想一想当某人想要事务是否可提交时的一种情况）。在以下DBA.SE问题的答案中，可以找到有关在带有XACT_ABORT ON的@@TRANCOUNT > 0块中使用XACT_STATE() <> 0的更多信息和研究：在什么情况下，当XACT_ABORT设置为ON时，可以从CATCH块内部提交事务。请注意，在某些情况下，XACT_ABORT有一个小错误，导致它错误地返回OFF：当在SELECT中使用某些系统变量但不使用FROM子句时，XACT_STATE（）返回1。
有关原始代码的注意事项：


您可以删除为交易指定的名称，因为它无济于事。
不需要1和每个-1调用周围的CATCH

评论

---

这是一个很好的答案。

– McNets
17年7月25日在10:33

---

哇，这是一个全面的答案！谢谢！顺便说一句，下一页是否解决了您所暗示的Try ... Catch未捕获的错误？ （在“错误不受TRY…CATCH构造影响”标题下？technet.microsoft.com/zh-cn/library/ms175976（v = sql.110）.aspx

– jrdevdba
18-2-13在20:21

---

@jrdevdba谢谢:-)。欢迎您。关于未捕获的错误，我几乎是指以下两个：编译错误（例如语法错误）（阻止批处理运行）和语句级重新编译期间发生的错误（例如由于延迟而导致的对象名称解析错误）名称解析..但是这种情况并不经常发生，当您发现这种情况时，要么对其进行修复（如果它是代码中的错误），要么将其置于子进程（EXEC或sp_executesql）中，以便进行尝试。 ..CATCH可以捕获它。

–所罗门·鲁兹基
18-2-14在22:05

---

#2 楼

是的，如果由于任何错误而将执行主存储过程的catch语句中的回滚代码，它将回滚由任何直接语句或通过其中的任何嵌套存储过程执行的所有操作。

如果您尚未在嵌套存储过程中应用任何显式事务，则这些存储过程仍将使用隐式事务，并且将在完成时提交，但是您通过嵌套存储过程中的显式或隐式事务提交的SQL Server引擎将忽略它并回滚如果主存储过程失败并且事务已回滚，则这些嵌套存储过程的所有操作。

每次根据最外部事务结束时采取的操作，事务都将提交或回退。如果外部事务已提交，则内部嵌套事务也将提交。如果外部事务被回滚，则所有内部事务也会被回滚，而不管内部事务是否被单独提交。

作为参考http://technet.microsoft.com/en -us / library / ms189336（v = sql.105）.aspx