对于带有事务的嵌套存储过程的处理网上的确有很多资料可参考。但大多是片面或过于字面化。对不清楚如何解决这种问题的同仁来说没有多大用处。最近正好工作不忙,就研究了下,发现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正确,其它错误)
)

as

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 --年龄
)

as

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

as

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

as

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

 

转自: http://f.dataguru.cn/blog-11439-1499.html