事务嵌套

最新推荐文章于 2023-03-29 09:50:05 发布

最新推荐文章于 2023-03-29 09:50:05 发布 · 178 阅读

文章标签：

#数据库

SQL Server中的嵌套事务与@@TranCount

在处理事务的时候，一般都用RollBack Transaction来回滚，但是如果在嵌套事务中这样使用的话，就会出现错误。

在SqlServer里，嵌套事务的层次是由@@TranCount全局变量反映出来的。每一次Begin Transaction都会引起@@TranCount加1。而每一次Commit Transaction都会使@@TranCount减1，而RollBack Transaction会回滚所有的嵌套事务包括已经提交的事务和未提交的事务，而使@@TranCount置0。例如： www.2cto.com

Begin Transaction -- @@TranCount = 1

BeginTransaction -- @@TranCount = 2

BeginTransaction -- @@TranCount = 3

Commit Transaction -- @@TranCount = 2

Commit Transaction -- @@TranCount = 1

Commit Transaction -- @@TranCount = 0

如果出现错误ROLLBACK TRANSACTION

则： www.2cto.com

Begin Transaction -- @@TranCount = 1

BeginTransaction -- @@TranCount = 2

BeginTransaction -- @@TranCount = 3

ROLLBACK TRANSACTION -- @@TranCount = 0

Commit Transaction -- @@TranCount = 0---出现错误

Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.

如果被嵌套的事务中发生错误，最简单的方法应该是无论如何都先将它提交，同时返回错误码（一个正常情况不可能出现的代码如 -1）让上一层事务来处理这个错误，从而使@@TranCount 减1。这样外层事务在回滚或者提交的时候能够保证外层事务在开始的时候和结束的时候保持一致。由于里层事务返回了错误码，因此外层事务（最外层）可以回滚事务，这样里面已经提交的事务也可以被回滚而不会出现错误。

在项目中应该会常常出现这样的情况，一个存储过程里面用了事务，但是不能保证它会被别的带有事务的存储过程调用，如果单独调用的话，出现错误可以直接回滚，但是如果是被别的带事务的存储过程调用的话，RollBack 就会出错了。因此需要一种机制来区分，建立一个临时的变量来区分是否嵌套，和嵌套的层数，如下：

DECLARE @TranCounter INT;

SET @TranCounter = @@TRANCOUNT;

IF @TranCounter > 0

SAVE TRANSACTION ProcedureSave;

ELSE

BEGIN TRANSACTION;

…………

--事务内要执行的代码

…………

IF @@ERROR<>0

goto Error

IF @TranCounter = 0

Commit Transaction

--下面返回要返回的值0只是个例子

Return 0

Error:

IF @TranCounter = 0

ROLLBACK TRANSACTION;

Else

ROLLBACK TRANSACTION ProcedureSave;

Return @Error

//=================================================================================================

对于带有事务的嵌套存储过程的处理网上的确有很多资料可参考。但大多是片面或过于字面化。对不清楚如何解决这种问题的同仁来说没有多大用处。最近正好工作不忙，就研究了下，发现sqlserver的嵌套事务存储过程在处理时确实有一定难度。原因在于微软的sqlserver在带有嵌套的事务方面存在以下几点特征：

1。 sqlserver 忽略提交内部事务。即无论内层事务是否提交，外层事务提交后，所有内层事务都会提交，反之外层事务回滚了，内层事务即使提交过，也会回滚。以外层事务的最后执行的操作（commit or rollback）为准。

2。每个commit的调用都只对应最后执行的begin transaction。意思就是每个commit对应与自己开始的begin transaction。打个比方：内层commit对应的是内层begintransaction，而不会影响外层。即使有更多层的嵌套也是如此。

3。rollback transaction 中的 transaction_name (事务名)只能引用最外层的transaction_name。引用内层或更多嵌套内层的事务名是不允许的。比如：内层begin transaction xx(事务名)，内层rollback transaction 时不能引用xx，而只能引用最外层的事务名。

4。@@trancount记录事务的嵌套级数。每begin transaction一次都会使@@trancount加1，每commit transaciton一次都会使@@trancount减1，而rollback transaction 则会直接使@@trancount为0，无论是嵌套事务还是单一事务。可通过检查@@trancount是否为0来确实是否还在事务中，大于0表明还在事务中。

由于以上几的特征的限制，使得嵌套的事务处理不那么容易。单一的事务存储过程是很好解决的。但如果需要调用已经存在的存储过程且自身已有事务处理了，那么问题就出现了（二层嵌套事务的存储过程执行结果）：

原因在于：每个事务在开始与提交时@@trancount要对应，如果开始事务的@@trancount与commit时的@@trancount不对应，就会出现以上的问题。说白了就是开始事务时@@trancount为1 ，那么在commit时也要为1,commit之后才会使@@trancount减1。

要解决嵌套事务的存储过程，避免以上的问题，个人总结两个方式：

1。使用@@trancount + 事务保存点解决。外层事务传递当前@@trancount值给内层事务，根据传递过来的@@trancount值是否大于0来选择是否保存事务点。大于0则保存，用于内层回滚到此事务点，等于0说明没开始事务，则直接开始内层的事务处理。内层事务处理中如果有错误，则根据传递过来的@@trancount值判断是回滚整个事务还是回滚到事务的保存点。

2。使用事务保存点 + rollback transactin 事务保存点 + 强制内层事务提交解决。此方式与1差别不多大，只是不使用@@trancount全局函数。内层事务开始后立即保存事务点。出错后直接回滚到事务保存点，再交由外层的事务来决定最后的执行操作。

以下是第一种方式的sql脚本的详细过程：

--测试用的数据库test
use test;
GO

--删除测试用的表
if EXISTS ( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'AgeValues') )

begin

DROP TABLE AgeValues;

end
GO

--内层存储过程已存在则先删除再创建
IF exists
(
SELECT * from sys.objects WHERE object_id = object_id(N'InnerProcedure') )

begin

drop PROCEDURE InnerProcedure;

end

--外层存储过程已存在则先删除再创建
IF exists
(
SELECT * from sys.objects where object_id = object_id(N'OuterProcedure') )

begin

drop PROCEDURE OuterProcedure;

end

--创建测试用的表
CREATE table AgeValues(age int not null check (age < 200));
GO

--设置不返回受影响的行数
SET NOCOUNT on;
GO

--内层事务存储过程，可直接执行，也可被其它带有事务的存储过程调用
create procedure InnerProcedure
(
@TranCount int, --当前@@trancount值 @in_AgeValue int, --年龄 @out_Result varchar(500) output --结果(OK正确，其它错误) )

begin

begin try--开始捕获异常 print ('开始内层事务前，@@trancount计数为：' + cast(@@trancount as varchar)); begin transaction InnerTran --开始事务 save transaction InnerTranPoint --保存事务点，用于回滚 print ('开始内层事务后，@@trancount计数为：' + cast(@@trancount as varchar)); print ('开始执行InnerProcedure存储过程的数据处理......'); insert INTO AgeValues VALUES(@in_AgeValue); IF @@error <> 0 --如果执行有错误，则@@error显示错误码，无错误时显示0 begin if @TranCount > 0 begin set @out_Result = '执行出错，错误码为：' + cast(@@error AS VARCHAR); print ('被其它事务存储过程调用时，在执行回滚事务前，@@trancount计数为：' + cast(@@trancount as varchar)); rollback transaction InnerTranPoint; --回滚到事务的保存点 print ('被其它事务存储过程调用时，在执行回滚事务后，@@trancount计数为：' + cast(@@trancount as varchar)); print ('被其它事务存储过程调用时，在执行提交事务前，@@trancount计数为：' + cast(@@trancount as varchar)); commit transaction InnerTran;--把当前的事务提交 print ('被其它事务存储过程调用时，在执行提交事务后，@@trancount计数为：' + cast(@@trancount as varchar)); end else if @TranCount = 0 begin set @out_Result = '执行出错，错误码为：' + cast(@@error AS VARCHAR); print ('直接调用时，在执行回滚事务前，@@trancount计数为：' + cast(@@trancount as varchar)); rollback transaction InnerTran; print ('直接调用时，在执行回滚事务后，@@trancount计数为：' + cast(@@trancount as varchar)); end end else--没有错误时则直接提交当前事务，并设置返回信息为1 begin