sql server:跨多个存储过程管理事务(begin、save、commit、rollback)

Question

在T-Sql中，rollback transaction回滚除指定的存储点名以外的所有事务。为了只回滚部分修改，我们使用rollback transaction @save_point_name。这就是说，save transaction @save_point_name必须在前面声明。如果引用的存储点已回滚(从事务日志中删除)，则会引发错误。同样，如果没有活动的事务，则需要声明begin transaction @transaction_name，并且可以以相同的方式回滚。这使得能够快速发现错误或使用try...catch机制。

与rollback不同，commit transaction @transaction_name完全忽略了它的@transaction_name部分，只执行一次提交，要么减少@@trancount，要么结束事务。因此，无法知道或指定哪个(嵌套的)事务或正在(伪)提交的存储点。我知道事务本来就不是嵌套的，因此才有了保存点。

一种典型的方法是在每个过程中检查@@trancount，以确定是创建存储点还是开始新事务。然后，确认确定，检查事务状态，并相应地提交或回滚(或不执行任何操作)。

这种检查是大量的样板，特别是当你有许多过程调用(多个)过程时，所有这些过程都只在发生错误时回滚自己的操作。所以我的尝试是对事务进行抽象，这样人们就可以简单地编写类似这样的代码。

create procedure RollBackOnlyMyActions
as
declare @SavePointName nvarchar(32) = N'RollBackToHere';
begin try
    exec CreateSavePoint @SavePointName;    

    --do stuff

    --call other procedures that may roll back only its actions

    --do other stuff

    exec CommitSavePoint @SavePointName;
end try
begin catch
    exec RollBackSavePoint @SavePointName;
end catch

在哪里(让我先打印出来)

create procedure dbo.CreateSavePoint
    @SavePointName nvarchar(32)
as
declare @Msg nvarchar(255);
print N'Preparing to create save point ['+@SavePointName+N'].';
print N'Checking not in an uncommitable transaction.';
if xact_state() != -1 
begin 
    print N'Not in an uncommitable transaction. Checking for transaction existence.';
    if @@TranCount = 0
    begin
        print N'No active transaction. Starting a new one.';
        begin transaction @SavePointName;
    end
    else
    begin
        print N'In active transaction. Saving transaction point.';
        save transaction @SavePointName;
    end
end
else
begin
    print N'In an uncommitable transaction. No use saving. Throwing exeption.';
    set @Msg = N'Uncommitable transaction.';
    throw 50000,@Msg,1;
end
go

create procedure dbo.CommitSavePoint
    @SavePointName nvarchar(32)
as
declare @Msg nvarchar(255);
print N'Preparing to commit save point ['+@SavePointName+N'].';
print N'Checking not in an uncommitable transaction.';
if xact_state() != -1
begin
    print N'Not in an uncommitable transaction. Checking transaction count.';
    if @@trancount > 1
    begin
        print N'In nested transaction of '+convert(nvarchar(255),@@trancount)+N'. Committing once.';
    end
    else if @@trancount = 1
    begin
        print N'In outter transaction. Committing.';        
    end
    else
    begin
        print N'No active transaction. Throw exception.';
        set @Msg = N'No transaction to commit.';
        throw 50000,@Msg,1;
    end
    commit transaction;
end
else
begin
    print N'In an uncommitable transaction. Cannot commit. Throwing exeption.';
    set @Msg = N'Uncommitable transaction.';
    throw 50000,@Msg,1;
end
go

create procedure dbo.RollbackSavePoint
    @SavePointName nvarchar(32)
as
declare @Msg nvarchar(255);
print N'Prepare to rollback savepoint ['+@SavePointName+N']';
print N'Checking not in an uncommitable transaction.';
if xact_state() != -1 
begin 
    print N'Not in an uncommitable transaction. Trying rollback';
    begin try
        rollback transaction @SavePointName;
    end try
    begin catch
        print N'Something went wrong. Rethrowing exception.';
        throw;
    end catch
end
else
begin
    print N'In an uncommitable transaction. No use rolling back. Throwing exeption.';
    set @Msg = N'Uncommitable transaction.';
    throw 50000,@Msg,1;
end
go

好吧，这不管用。当我得到一个

Msg 266, Level 16, State 2, Procedure CreateSavePoint, Line 0
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.

在第一次调用CreateSavePoint之后。也就是说，sql server似乎不喜欢跨多个过程管理事务。

所以。是否有解决此问题的方法，例如，抑制此错误的方法？还是我在这里错过了一个重要的概念？

Answer 1

回滚问题可以通过使用SET XACT_ABORT来解决，该设置为"auto rollback“(简单地)并抑制错误266。