sql server: managing transactions (begin, save, commit, rollback) across multiple stored procedures

Question

In T-Sql, rollback transaction rolls back all transactions except specified a save point name. To roll back only part of modifications, we use rollback transaction @save_point_name. That is a save transaction @save_point_name has to be earlier stated. If the referred save point has already been rolled back (removed form the transaction log), an error is raised. Likewise, if there isn't an active transaction, a begin transaction @transaction_name needs to be stated, and can be rolled back in the same manner. This enables fast discoveries of bugs or using try...catch mechanism.

Unlike rollback, commit transaction @transaction_name ignores its @transaction_name part completely and just performs a commit, either decreasing @@trancount or ending the transaction. So there is no way in knowing, or specifying, which (nested) transaction, or for that matter - a save point, is being (pseudo) committed. I know transactions weren't meant to be nested in the first place, hence the existence of save points.

A typical approach is to, in each procedure, check @@trancount to determine whether to create a save point or to begin a new transaction. Then later, confirm the determination, check transaction state and commit or roll back (or do nothing) accordingly.

This checking is a lot of boilerplate especially when you have a lot of procedures calling (multiple) procedures all of which only rollback their own actions if some thing went wrong. So my attempt was to abstract the transactioning so that one could simply write something like this.

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

where (spare me for the print-outs)

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

Well this didn't work. As I get a

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.

after the first call to CreateSavePoint. That is, it seems sql server doesn't like managing transactions across multiple procedures.

So. Is there any workaround for this, such as, a way to suppress this error? Or am I missing an important concept here?

Answer 1

The rollback issue can be resolved by using SET XACT_ABORT ON which is "auto rollback" (simply) and suppresses error 266.