sqlserver 多重嵌套事务的存储过程处理与解决方法

最新推荐文章于 2025-06-06 23:43:36 发布

原创最新推荐文章于 2025-06-06 23:43:36 发布 · 置顶 · 5.2k 阅读

12 ·

CC 4.0 BY-SA版权

文章标签：

#存储 #sqlserver #object #insert #microsoft #测试

SqlServer 专栏收录该内容

2 篇文章

订阅专栏

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

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

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

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

/*******************************************************************************
作者：董辉
类型：新建
日期：2012-7-17 16:16
说明：用于解决嵌套事务的存储过程的方式 1

*******************************************************************************/

--内层事务存储过程，可直接执行，也可被其它带有事务的存储过程调用
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

    set @out_Result = 'OK';

    print ('执行没有出错时，在提交事务前，@@trancount计数为：' + cast(@@trancount as varchar));

    commit transaction InnerTran;

    print ('执行没有出错时，在提交事务后，@@trancount计数为：' + cast(@@trancount as varchar));

   end


end try  --结束捕获异常

begin catch --处理异常

  if @TranCount > 0

   begin

    set @out_Result = 'catch内捕获：执行出错，错误码为：' + cast(ERROR_NUMBER() AS VARCHAR) + ' 错误信息为：' + error_message();

    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_NUMBER() AS VARCHAR) + ' 错误信息为：' + error_message();

    print ('直接调用时，在执行回滚事务前，@@trancount计数为：' + cast(@@trancount as varchar));

    rollback transaction InnerTran;

    print ('直接调用时，在执行回滚事务后，@@trancount计数为：' + cast(@@trancount as varchar));

   end

end catch   --处理异常

end
go

--外层事务存储过程,用来调用其它带事务的存储过程
create procedure OuterProcedure
(
@in_Age int --年龄
)

begin

declare @TranCount int; --当前@@trancount值

declare @Result varchar(500); --用来接收返回的信息

print ('外层事务开始前，@@trancount计数为：' + cast(@@trancount as varchar));

begin transaction OuterTran --开始事务

print ('外层事务开始后，@@trancount计数为：' + cast(@@trancount as varchar));

print ('开始执行OuterProcedure存储过程的数据处理......');

set @TranCount = @@trancount; --记录当前@@trancount值

exec InnerProcedure @TranCount,@in_Age,@Result output;

IF @Result <> 'OK' --有错误

  BEGIN

   print ('外层事务回滚前，@@trancount计数为：' + cast(@@trancount as varchar));

   rollback transaction OuterTran;

   print ('外层事务回滚后，@@trancount计数为：' + cast(@@trancount as varchar));

   print ('外层：执行出错，已回滚！');

  end

else

  begin

   print ('外层事务提交前，@@trancount计数为：' + cast(@@trancount as varchar));

   commit transaction OuterTran;

   print ('外层事务提交后，@@trancount计数为：' + cast(@@trancount as varchar));

   print ('外层：执行成功，已提交！');

  end

print (char(10) + @Result);

end
go

--开始测试(值大于200则不能插入表中。AgeValues表age字段约束为不能大于200)
declare @k int = 300;
exec OuterProcedure @k;
go

这个是第二种方式的sql脚本详细过程：

/***************************************************************************
作者：董辉
类型：新建
日期：2012-7-17 9:26
说明：用于解决嵌套事务的存储过程的方式 2
   outertran事务存储过程在调用 innertran 事务存储过程与调用 innertranDemo
   事务存储过程有区别，调用第一个时由于内层出错回滚了当前事务，这时
   @@trancount被回滚到了0，外层再回滚时就会出错。而调用第二个事务存储过程
   时，由于内层事务存储过程保存了事务点，出错时回滚到事务点，这样外层就不
   会出数计数不匹配的错误。

   BEGIN distributed transaction 指定一个由 Microsoft 分布式事务处理协调器
   (MS DTC) 管理的 Transact-SQL 分布式事务的起始

***************************************************************************/

--内层事务存储过程，演示如何处理才能在嵌套的事务存储过程中正确处理事务
alter procedure innertranDemo

begin

begin tran tran1 --开始事务

save tran t   --保存事务点

insert into t1 VALUES(1);

if @@error <> 0

  begin

   rollback tran t; --回滚保存点的事务

   commit tran tran1; --提示当前事务

   return -1;

  end

insert INTO t1 VALUES(189);

IF @@error <> 0

  begin

   rollback tran t;

   commit tran tran1;

   return -1;

  end

commit tran tran1;

return 1;

end

--外层事务存储过程
alter procedure outertran

begin

-- with mark '开始事务' 指定在日志中标记事务
begin tran tran2 with mark '开始事务' --开始事务，使@@trancount加1

insert INTO t1 VALUES(2);

declare @invikecode int;

exec @invikecode = innertranDemo;--执行innertran与执行innertranDeom会有不同的结果

IF @invikecode = -1

  begin

   rollback tran tran2;

   print ('回滚，当前计数为：' + cast(@@trancount as varchar));

  end
else

  begin

   commit tran tran2;

   print ('提交，当前计数为：' + cast(@@trancount as varchar));

  end

end

--测试
exec outertran;

总结下来，对于嵌套事务的存储过程解决起来无非就是利用@@trancount来判断事务的当前嵌套状态，来决定内层的事务是回滚到事务点，还是直接全部回滚。内层回滚时是可以直接引用外层的事务名进行操作的。但不允许引用自己内层的事务名。如果说有这种情况，就是别人写好的正常执行的单个带事务操作的存储过程，你去调用，但你自己的存储过程也需要事务处理。这种情况再怎么解决也是需要去修改它的存储过程的。不是修改逻辑处理，而是按上面的方式进行能正常用于嵌套事务的处理方式来修改。毕竟单一事务与多重嵌套事务在处理上还是存在很大差别的。

以上是个人的见解，有错误的地方还望指出，共同学习！有需要一起研究的同仁，可以加下我的联系QQ：137467392。