SQL Server中的嵌套事务与@@TranCount
//=================================================================================================
对于带有事务的嵌套存储过程的处理网上的确有很多资料可参考。但大多是片面或过于字面化。对不清楚如何解决这种问题的同仁来说没有多大用处。最近正好工作不忙,就研究了下,发现sqlserver的嵌套事务存储过程在处理时确实有一定难度。原因在于微软的sqlserver在带有嵌套的事务方面存在以下几点特征:
<wbr></wbr>
1。 sqlserver 忽略提交内部事务。即无论内层事务是否提交,外层事务提交后,所有内层事务都会提交,反之外层事务回滚了,内层事务即使提交过,也会回滚。以外层事务的最后执行的操作(commit or rollback)为准。
<wbr></wbr>
2。每个commit的调用都只对应最后执行的begin transaction。意思就是每个commit对应与自己开始的begin transaction。打个比方:内层commit对应的是内层begin<wbr><wbr>transaction,而不会影响外层。即使有更多层的嵌套也是如此。</wbr></wbr>
<wbr></wbr>
3。rollback transaction 中的 transaction_name (事务名)只能引用最外层的transaction_name。引用内层或更多嵌套内层的事务名是不允许的。比如:内层begin transaction xx(事务名),内层rollback transaction 时不能引用xx,而只能引用最外层的事务名。
<wbr></wbr>
4。@@trancount记录事务的嵌套级数。每begin transaction一次 都会使@@trancount加1,每commit transaciton一次都会使@@trancount减1,而rollback transaction 则会直接使@@trancount为0,无论是嵌套事务还是单一事务。可通过检查@@trancount是否为0来确实是否还在事务中,大于0表明还在事务中。
<wbr></wbr>
由于以上几的特征的限制,使得嵌套的事务处理不那么容易。单一的事务存储过程是很好解决的。但如果需要调用已经存在的存储过程且自身已有事务处理了,那么问题就出现了(二层嵌套事务的存储过程执行结果):
原因在于:每个事务在开始与提交时@@trancount要对应,如果开始事务的@@trancount与commit时的@@trancount不对应,就会出现以上的问题。说白了就是开始事务时@@trancount为1 ,那么在commit时也要为1,commit之后才会使@@trancount减1。
要解决嵌套事务的存储过程,避免以上的问题,个人总结两个方式:
1。使用@@trancount<wbr> + 事务保存点 解决。 外层事务传递当前@@trancount值给内层事务,根据传递过来的@@trancount值是否大于0来选择是否保存事务点。大于0则保存,用于内层回滚到此事务点,等于0说明没开始事务,则直接开始内层的事务处理。内层事务处理中如果有错误,则根据传递过来的@@trancount值判断是回滚整个事务还是回滚到事务的保存点。</wbr>
<wbr></wbr>
2。使用事务保存点 + rollback transactin 事务保存点 + 强制内层事务提交 解决。 此方式与1差别不多大,只是不使用@@trancount全局函数。内层事务开始后立即保存事务点。出错后直接回滚到事务保存点,再交由外层的事务来决定最后的执行操作。
<wbr></wbr>
以下是第一种方式的sql脚本的详细过程:
<wbr></wbr>
--测试用的数据库test
use test;
GO
--删除测试用的表
if<wbr> EXISTS<br>
(<br><wbr>SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N'AgeValues')<br>
)</wbr></wbr>
begin
<wbr>DROP TABLE AgeValues;</wbr>
end
GO
--内层存储过程已存在则先删除再创建
IF exists
(
<wbr>SELECT * from sys.objects WHERE object_id =
object_id(N'InnerProcedure')<br>
)</wbr>
begin
<wbr>drop PROCEDURE InnerProcedure;</wbr>
end
--外层存储过程已存在则先删除再创建
IF exists
(
<wbr>SELECT * from sys.objects where object_id =
object_id(N'OuterProcedure')<br>
)</wbr>
begin
<wbr>drop PROCEDURE OuterProcedure;</wbr>
end
--创建测试用的表
CREATE table AgeValues(age int not null check (age <
200));
GO
--设置不返回受影响的行数
SET NOCOUNT on;
GO
<wbr></wbr>
<wbr></wbr>
<wbr></wbr>
--内层事务存储过程,可直接执行,也可被其它带有事务的存储过程调用
create procedure InnerProcedure
(
<wbr>@TranCount
int,<wbr><wbr> --当前@@trancount值<br><wbr><br><wbr>@in_AgeValue int, --年龄<br><wbr><br><wbr>@out_Result varchar(500) output
--结果(OK正确,其它错误)<br>
)</wbr></wbr></wbr></wbr></wbr></wbr></wbr>
as
begin
<wbr>begin try<wbr>--开始捕获异常<br><wbr><br><wbr><wbr>print ('开始内层事务前,@@trancount计数为:' + cast(@@trancount as varchar));<br><wbr><wbr><br><wbr><wbr>begin transaction InnerTran --开始事务<br><wbr><br><wbr><wbr>save transaction InnerTranPoint --保存事务点,用于回滚<br><wbr><wbr><br><wbr><wbr>print ('开始内层事务后,@@trancount计数为:' + cast(@@trancount as varchar));<br><wbr><wbr><br><wbr><wbr>print ('开始执行InnerProcedure存储过程的数据处理......');<br><wbr><wbr><br><wbr><wbr>insert INTO AgeValues VALUES(@in_AgeValue);<br><wbr><wbr><br><wbr><wbr>IF @@error <> 0 --如果执行有错误,则@@error显示错误码,无错误时显示0<br><wbr><wbr><br><wbr><wbr><wbr>begin<br><wbr><wbr><wbr><br><wbr><wbr><wbr><wbr>if @TranCount > 0<wbr><wbr><wbr><br><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><br><wbr><wbr><wbr><wbr><wbr>begin<br><wbr><wbr><wbr><wbr><wbr><br><wbr><wbr><wbr><wbr><wbr><wbr>set @out_Result = '执行出错,错误码为:' + cast(@@error AS VARCHAR);<br><wbr><wbr><wbr><wbr><wbr><wbr><br><wbr><wbr><wbr><wbr><wbr><wbr>print ('被其它事务存储过程调用时,在执行回滚事务前,@@trancount计数为:' + cast(@@trancount as varchar));<br><wbr><wbr><wbr><wbr><wbr><wbr><br><wbr><wbr><wbr><wbr><wbr><wbr>rollback transaction InnerTranPoint; --回滚到事务的保存点<br><wbr><wbr><wbr><wbr><wbr><wbr><br><wbr><wbr><wbr><wbr><wbr><wbr>print ('被其它事务存储过程调用时,在执行回滚事务后,@@trancount计数为:' + cast(@@trancount as varchar));<br><wbr><wbr><wbr><wbr><wbr><wbr><br><wbr><wbr><wbr><wbr><wbr><wbr>print ('被其它事务存储过程调用时,在执行提交事务前,@@trancount计数为:' + cast(@@trancount as varchar));<br><wbr><wbr><wbr><wbr><wbr><wbr><br><wbr><wbr><wbr><wbr><wbr><wbr>commit transaction InnerTran;<wbr>--把当前的事务提交<br><wbr><wbr><wbr><wbr><wbr><wbr><br><wbr><wbr><wbr><wbr><wbr><wbr>print ('被其它事务存储过程调用时,在执行提交事务后,@@trancount计数为:' + cast(@@trancount as varchar));<br><wbr><wbr><wbr><wbr><wbr><br><wbr><wbr><wbr><wbr><wbr>end<br><wbr><wbr><wbr><wbr><wbr><br><wbr><wbr><wbr><wbr>else if @TranCount = 0<br><wbr><wbr><wbr><wbr><br><wbr><wbr><wbr><wbr><wbr>begin<br><wbr><wbr><wbr><wbr><wbr><br><wbr><wbr><wbr><wbr><wbr><wbr>set @out_Result = '执行出错,错误码为:' + cast(@@error AS VARCHAR);<br><wbr><wbr><wbr><wbr><wbr><wbr><br><wbr><wbr><wbr><wbr><wbr><wbr>print ('直接调用时,在执行回滚事务前,@@trancount计数为:' + cast(@@trancount as varchar));<br><wbr><wbr><wbr><wbr><wbr><wbr><br><wbr><wbr><wbr><wbr><wbr><wbr>rollback transaction InnerTran;<br><wbr><wbr><wbr><wbr><wbr><wbr><br><wbr><wbr><wbr><wbr><wbr><wbr>print ('直接调用时,在执行回滚事务后,@@trancount计数为:' + cast(@@trancount as varchar));<br><wbr><wbr><wbr><wbr><wbr><br><wbr><wbr><wbr><wbr><wbr>end<br><wbr><wbr><wbr><br><wbr><wbr><wbr>end<br><wbr><wbr><wbr><br><wbr><wbr>else<wbr>--没有错误时则直接提交当前事务,并设置返回信息为1<br><wbr><wbr><br><wbr><wbr><wbr>begin<br><wbr><wbr><wbr><br></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>