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.
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 toCOMMIT
simply decrements the counter. Meaning, if you issueSAVE TRANSACTION ProcSavePoint;
5 times, and then callROLLBACK TRANSACTION ProcSavePoint;
2 times, you will still be left at the state things were in after callingSAVE 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 executeROLLBACK 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 callingRAISERROR()
to go immediately to theCATCH
block. Or if you don't want to handle that as an error, you can still do yourGOTO undo
method in addition to theTRY
/CATCH
.I do not believe
XACT_STATE()
can report a-1
outside of aTRY
/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 evenSAVE TRAN
called, and only the outer later (even if it is app code), will do theCOMMIT
orROLLBACK
.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 are0
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/orINSERT INTO ... EXEC
increment even if there is an active transaction). In either case, my use of aBIT
variable for@InNestedTransaction
is functionally / logically equivalent to storing@@TRANCOUNT
in anINT
variable sinceSAVE TRAN
does not increment@@TRANCOUNT
.