|
对于带有事务的嵌套存储过程的处理网上的确有很多资料可参考。但大多是片面或过于字面化。对不清楚如何解决这种问题的同仁来说没有多大用处。最近正好工作不忙，就研究了下，发现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
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
