如果第一个存储过程在主存储过程中正常工作,但第二个存储过程失败,那么它会自动回滚主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只是一种形式,但必须完成.
函数XACT_STATE可用于确定事务是处于活动状态,不可控制还是不存在.建议(至少某些人)在CATCH块中检查此功能以确定结果是否为-1(即不可拒绝),而不是测试@@ TRANCOUNT> 0.但是在XACT_ABORT开启的情况下,这应该是唯一可能存在的状态,因此似乎测试@@ TRANCOUNT> 0和XACT_STATE()<> 0是等价的.另一方面,当XACT_ABORT为OFF并且存在活动事务时,则CATCH块中可能具有1或-1的状态,这允许发出COMMIT而不是ROLLBACK(尽管如此,如果事务是可提交的,那么当有人想要COMMIT时,我们就不会想到这种情况.有关在XACT_ABORT ON的CATCH块中使用XACT_STATE()的更多信息和研究可以在我对以下DBA.SE问题的答案中找到：In what cases a transaction can be committed from inside the CATCH block when XACT_ABORT is set to ON?.请注意XACT_STATE()存在一个小错误导致它错误地返回1在某些情况下：XACT_STATE() returns 1 when used in SELECT with some system variables but without FROM clause
关于原始代码的说明：
