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.

Are you asking whether same savepoint name can be used in another session — Pரதீப், Feb 28 '16 at 15:15

score 4 · Accepted Answer · edited Apr 13 '17 at 12:42

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:

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.
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.
I do not believe XACT_STATE() can report a -1 outside of a TRY / CATCH construct.
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.

Do you have situations in which outer layers might continue and eventually COMMIT even if there were errors happening in sub-proc calls? Yes. The caller proc gets back a return value from the sub-proc and Rolls back if needed. I'm assuming that for unhandled sql server exception the whole translation is rolled back, isn't it? In any case I always report to my client application with return code. let me evaluate your answer a bit more please... — zig, Feb 28 '16 at 17:32
@zig It sounds like you do then have a good reason to do both the TRY / CATCH _and_ your "undo" label, which I guess would come at the end of the `TRY` block. Without a TRY / CATCH construct things are a bit difficult to manage since some errors abort just the statement and some abort the batch. If you use `SET XACT_ABORT ON`, then _all_ errors abort the entire batch and return `-1` from `XACT_STATE()` within a `CATCH` block; however, I am not a fan of using `XACT_ABORT ON`. — Solomon Rutzky, Feb 28 '16 at 17:49
Thanks @srutzky, you gave me a lot of good info. I might need to rethink my template, because I always return a value for representing a managed error (it is usually a validation error). and never use TRY/CATCH thinking `XACT_ABORT ON` is always on :/ — zig, Feb 29 '16 at 07:14
@zig I have a question in your above code. Is this statement - " IF '@tranCount' = 0 COMMIT TRAN RETURN" correct. I mean if Trancount is zero then COMMIT TRAN will give error. Please correct me if I am wrong here or missing something — sam, Feb 29 '16 at 08:15
@sam, `@tranCount = 0` (local variable initially set to `@@TRANCOUNT`) means that transaction started in procedure and there are no top level transactions (`BEGIN TRAN` was called first) — zig, Feb 29 '16 at 09:54

Using SAVE TRANSACTION SavePointName in a Stored Procedure

1 Answers1