SQL Server嵌套事务

浏览数：42 / 时间：2015年06月12日

一、@@TRANCOUNT

在将事务前，我们先来了解一下@@TRANCOUNT ，@@trancount返回上传执行begin transaction语句的事务计数。

1、每执行一次begin transaction语句@@trancount 将增加1。

2、执行rollback transaction 语句@@trancount将变为0，但执行rollback transaction savepoint_name语句@@trancount不会有影响。

declare @currntTranCount VARCHAR
set @currntTranCount=@@TRANCOUNT
print ‘未执行事务前全局@@TRANCOUNT:‘+@currntTranCount

begin transaction
set @currntTranCount=@@TRANCOUNT
print ‘执行事务后全局@@TRANCOUNT:‘+@currntTranCount

rollback transaction
set @currntTranCount=@@TRANCOUNT
print ‘回滚事务后全局@@TRANCOUNT:‘+@currntTranCount

输出：

　　未执行事务前全局@@TRANCOUNT:0
　　执行事务后全局@@TRANCOUNT:1
　　回滚事务后全局@@TRANCOUNT:0

declare @currntTranCount VARCHAR
set @currntTranCount=@@TRANCOUNT
print ‘未执行事务前全局@@TRANCOUNT:‘+@currntTranCount

begin transaction
set @currntTranCount=@@TRANCOUNT
print ‘执行事务后全局@@TRANCOUNT:‘+@currntTranCount

save transaction savePoint_Tran1
set @currntTranCount=@@TRANCOUNT
print ‘保存事务点后全局@@TRANCOUNT:‘+@currntTranCount

rollback transaction savePoint_Tran1    --@@TRANCOUNT没受影响
set @currntTranCount=@@TRANCOUNT
print ‘回滚事务保存点后全局@@TRANCOUNT:‘+@currntTranCount

rollback transaction
set @currntTranCount=@@TRANCOUNT
print ‘回滚事务后全局@@TRANCOUNT:‘+@currntTranCount

输出：

　　未执行事务前全局@@TRANCOUNT:0
　　执行事务后全局@@TRANCOUNT:1
　　保存事务点后全局@@TRANCOUNT:1
　　回滚事务保存点后全局@@TRANCOUNT:1
　　回滚事务后全局@@TRANCOUNT:0

3、执行commit transaction或commit work语句@@trancount将递减1。

declare @currntTranCount VARCHAR
set @currntTranCount=@@TRANCOUNT
print ‘未执行事务前全局@@TRANCOUNT:‘+@currntTranCount

begin transaction
set @currntTranCount=@@TRANCOUNT
print ‘执行事务后全局@@TRANCOUNT:‘+@currntTranCount

commit transaction
set @currntTranCount=@@TRANCOUNT
print ‘提交事务后全局@@TRANCOUNT:‘+@currntTranCount

输出：

　　未执行事务前全局@@TRANCOUNT:0
　　执行事务后全局@@TRANCOUNT:1
　　提交事务后全局@@TRANCOUNT:0

4、ROLLBACK TRANSACTION (Transact-SQL)：https://msdn.microsoft.com/zh-cn/library/ms181299.aspx

5、COMMIT TRANSACTION (Transact-SQL)：https://msdn.microsoft.com/zh-cn/library/ms190295.aspx

二、嵌套事务

1、回滚嵌套事务：rollback transaction只能回滚最外面的事务名称或rollback transaction不指定某个事务名进行回滚；

如果执行rollback transaction transaction_innerTran，SQL会提示“无法回滚 transaction_innerTran。找不到该名称的事务或保存点。”，原因是transaction_innerTran不是最外层的事务；也就是说：回滚事务一回滚就所有事务都被回滚。

rollback transaction可以回滚某个事务保存点(SAVE TRANSACTION savePoint_Tran), 如ROLLBACK TRAN savePoint_Tran，但是回滚事务保存点不会使事务计数@@TRANCOUNT减少。

1）内层回滚事务存储过程

--嵌套事务：内层事务
CREATE PROCEDURE pro_InnerTransactionTest 
AS
BEGIN
    declare @currntTranCount VARCHAR(50)
    set @currntTranCount=@@TRANCOUNT
    print ‘未执行内层事务前全局@@TRANCOUNT:‘+@currntTranCount
    
    begin transaction transaction_innerTran
    set @currntTranCount=@@TRANCOUNT
    print ‘执行内层事务后全局@@TRANCOUNT:‘+@currntTranCount
    
    rollback transaction transaction_innerTran
    --rollback transaction
    --rollback transaction transaction_outerTran    --rollback都报错
    set @currntTranCount=@@TRANCOUNT
    print ‘回滚内层事务后全局@@TRANCOUNT:‘+@currntTranCount
    
    return 0;
    --return 1;
END
GO

2）外层开启事务执行

--嵌套事务：外层执行
declare @currntTranCount varchar(50);
declare @result int;
set @currntTranCount=@@TRANCOUNT;
print ‘未执行外层事务前全局@@TRANCOUNT:‘+@currntTranCount;
    
begin transaction transaction_outerTran;
set @currntTranCount=@@TRANCOUNT;
print ‘执行外层事务后全局@@TRANCOUNT:‘+@currntTranCount;

execute @result = pro_InnerTransactionTest;

if(@result <= 0) 
begin
    rollback transaction transaction_outerTran;
    set @currntTranCount=@@TRANCOUNT;
    print ‘回滚外层事务后全局@@TRANCOUNT:‘+@currntTranCount;
                
    return;
end 
commit transaction transaction_outerTran 
set @currntTranCount=@@TRANCOUNT;
print ‘提交外层事务后全局@@TRANCOUNT:‘+@currntTranCount;

输出：

未执行外层事务前全局@@TRANCOUNT:0
执行外层事务后全局@@TRANCOUNT:1
未执行内层事务前全局@@TRANCOUNT:1
执行内层事务后全局@@TRANCOUNT:2
消息 6401，级别 16，状态 1，过程 pro_InnerTransactionTest，第 13 行
无法回滚 transaction_innerTran。找不到该名称的事务或保存点。
回滚内层事务后全局@@TRANCOUNT:2
消息 266，级别 16，状态 2，过程 pro_InnerTransactionTest，第 0 行
EXECUTE 后的事务计数指示 BEGIN 和 COMMIT 语句的数目不匹配。上一计数 = 1，当前计数 = 2。
回滚外层事务后全局@@TRANCOUNT:0

2、提交嵌套事务：commit transaction 可以单独指定某个事务名，如transaction_outerTran，transaction_innerTran进行提交，但即使transaction_innerTran提交成功了，只要最外面的事务transaction_outerTran回滚，transaction_innerTran提交的数据也会被回滚的。

1）内层提交事务存储过程

--嵌套事务：内层事务
CREATE PROCEDURE pro_InnerTransactionTest 
AS
BEGIN
    declare @currntTranCount VARCHAR(50)
    set @currntTranCount=@@TRANCOUNT
    print ‘未执行内层事务前全局@@TRANCOUNT:‘+@currntTranCount
    
    begin transaction transaction_innerTran
    set @currntTranCount=@@TRANCOUNT
    print ‘执行内层事务后全局@@TRANCOUNT:‘+@currntTranCount
    
    --rollback transaction transaction_innerTran
    --rollback transaction
    --rollback transaction transaction_outerTran    --rollback都报错
    --set @currntTranCount=@@TRANCOUNT
    --print ‘回滚内层事务后全局@@TRANCOUNT:‘+@currntTranCount
    
    commit transaction transaction_innerTran    --提交内层事务，外层回滚或提交事务都没报错
    
    return 0;
    --return 1;
END
GO

2）外层开启事务执行后输出：

未执行外层事务前全局@@TRANCOUNT:0
执行外层事务后全局@@TRANCOUNT:1
未执行内层事务前全局@@TRANCOUNT:1
执行内层事务后全局@@TRANCOUNT:2
回滚外层事务后全局@@TRANCOUNT:0 （或：提交外层事务后全局@@TRANCOUNT:0）

三、解决办法：

1）内层存储过程

--嵌套事务：内层事务
CREATE PROCEDURE pro_InnerTransactionTest 
AS
BEGIN
    declare @currntTranCount varchar(50)
    declare @sumError int=0
    declare @isSingleTran bit=1    --是否单个事务而非嵌套事务
    
    set @currntTranCount=@@TRANCOUNT
    print ‘未执行内层事务前全局@@TRANCOUNT:‘+@currntTranCount
    
    SET XACT_ABORT ON    --设置事务回滚到原点
    --开始事务
    if (@currntTranCount=0)
     begin
        begin transaction transaction_innerTran
        set @isSingleTran=1;        
        set @currntTranCount=@@TRANCOUNT
        print ‘执行内层事务后全局@@TRANCOUNT:‘+@currntTranCount
     end
    else
     begin
        save transaction savepoint_innerTran    --保存事务点
        set @isSingleTran=0;        
        set @currntTranCount=@@TRANCOUNT
        print ‘保存内层事务点全局@@TRANCOUNT:‘+@currntTranCount
     end
    
    --UPDATE [dbo].[FinanceInfo] SET [Balance] = [Balance]+1000 WHERE [UserId] = 10001
    --set @sumError = @sumError + @@error
    --UPDATE [dbo].[FinanceInfo] SET [Balance] = [Balance]-1000 WHERE [UserId] = 10002
    --set @sumError = @sumError + @@error
    
    set @sumError=1; --手动测试
    
    if(@sumError = 0)
     begin
        if(@isSingleTran = 1) 
         begin
            commit transaction transaction_innerTran
            set @currntTranCount=@@TRANCOUNT
            print ‘提交内层事务点全局@@TRANCOUNT:‘+@currntTranCount
         end
        else
         begin
            set @currntTranCount=@@TRANCOUNT
            print ‘返回内层全局@@TRANCOUNT:‘+@currntTranCount
            return 1;            --成功
         end        
     end
    else
     begin
        if(@isSingleTran = 1) 
         begin
            rollback transaction    --发生错误,回滚事务
            set @currntTranCount=@@TRANCOUNT
            print ‘回滚内层事务全局@@TRANCOUNT:‘+@currntTranCount
         end
        else
         begin
            rollback transaction savepoint_innerTran
            set @currntTranCount=@@TRANCOUNT
            print ‘回滚内层事务点全局@@TRANCOUNT:‘+@currntTranCount
            return 0;                --失败
         end
     end
END
GO

2）外层开启事务执行存储过程

--嵌套事务：外层执行
declare @currntTranCount varchar(50);
declare @result int;
set @currntTranCount=@@TRANCOUNT;
print ‘未执行外层事务前全局@@TRANCOUNT:‘+@currntTranCount;
    
begin transaction transaction_outerTran;
set @currntTranCount=@@TRANCOUNT;
print ‘执行外层事务后全局@@TRANCOUNT:‘+@currntTranCount;

execute @result = pro_InnerTransactionTest;

if(@result <= 0) 
begin
    rollback transaction transaction_outerTran;
    set @currntTranCount=@@TRANCOUNT;
    print ‘回滚外层事务后全局@@TRANCOUNT:‘+@currntTranCount;
                
    return;
end 
commit transaction transaction_outerTran 
set @currntTranCount=@@TRANCOUNT;
print ‘提交外层事务后全局@@TRANCOUNT:‘+@currntTranCount;


--单个事务执行
declare @result INT
execute @result = pro_InnerTransactionTest;
select @result