Using SAVE TRANSACTION SavePointName in a Stored Procedure

Question

It is unclear to me if I need to used a different save point names for each SP I use SAVE TRANSACTION.

Could I always use e.g. SAVE TRANSACTION ProcedureSavePoint and ROLLBACK TRANSACTION ProcedureSavePoint even if a higher level transaction used the same save point name?

My SP(s) signature is as follow:

ALTER PROCEDURE [dbo].[usp_MyTask]()
AS
BEGIN
    DECLARE @iReturn int = 0

    DECLARE @tranCount int = @@TRANCOUNT;
    IF @tranCount > 0
        SAVE TRANSACTION ProcSavePoint;
    ELSE
        BEGIN TRAN    
    ...

    IF <some condition>
    BEGIN
        @iReturn = 1
        GOTO Undo
    END

    ...

    IF @tranCount = 0 
        COMMIT TRAN 
    RETURN

Undo:
    IF @tranCount = 0 -- transaction started in procedure. Roll back complete transaction.
        ROLLBACK TRAN;
    ELSE
        IF XACT_STATE() <> -1 ROLLBACK TRANSACTION ProcSavePoint;

    RETURN @iReturn
END

Hope my question is clear.

Answer 1

Technically, yes, you can re-use the same Save Point name, and they will get stacked up just like multiple calls to BEGIN TRAN where each call to COMMIT simply decrements the counter. Meaning, if you issue SAVE TRANSACTION ProcSavePoint; 5 times, and then call ROLLBACK TRANSACTION ProcSavePoint; 2 times, you will still be left at the state things were in after calling SAVE TRAN the third time and prior to calling it the fourth time.

However, this code is problematic on a few levels:

1. Due to the behavior just mentioned, in a nested scenario, depending on the condition(s) for calling GOTO Undo, if you have a situation where you call nested procs 5 levels deep, and then level 5 completes successfully, and then level 4 completes successfully, but then level 3 decides to go to "undo", it will execute ROLLBACK TRANSACTION ProcSavePoint; which will only roll-back that fifth level. This leaves you in a bad state because the intention was to roll-back to the state things were in when level 3 started.

   Using unique Save Point names would correct for this.

2. You are oddly not using the TRY / CATCH construct. You really should. If you have logic that will decide to cancel an operation based on a particular condition that is not a SQL Server error, you can still force that by calling RAISERROR() to go immediately to the CATCH block. Or if you don't want to handle that as an error, you can still do your GOTO undo method in addition to the TRY / CATCH.

3. I do not believe XACT_STATE() can report a -1 outside of a TRY / CATCH construct.

4. Why are you using Save Points in the first place? Do you have situations in which outer layers might continue and eventually COMMIT even if there were errors happening in sub-proc calls?

   The template I use most often is shown in my answer to this question on DBA.StackExchange: Are we required to handle Transaction in C# Code as well as in Store procedure. That template simply checks for an active transaction at the beginning (similar to your method), but then does nothing if there is an active transaction. So there is never an additional BEGIN TRAN or even SAVE TRAN called, and only the outer later (even if it is app code), will do the COMMIT or ROLLBACK.

   And just to have this pointed out because it looks like a functional difference between your code and what I posted in that linked answer, but really isn't: there is no specific need to trap the actual value of @@TRANCOUNT since the only options are 0 or > 0, and unless @@TRANCOUNT is already > 1 upon entering your template, the max it will ever get is 1 anyway (possibly 2 if Triggers and/or INSERT INTO ... EXEC increment even if there is an active transaction). In either case, my use of a BIT variable for @InNestedTransaction is functionally / logically equivalent to storing @@TRANCOUNT in an INT variable since SAVE TRAN does not increment @@TRANCOUNT.