How to rollback when 3 stored procedures are started from one stored procedure

Question

I have a stored procedure that only executes 3 stored procedures inside them. I am only using 1 parameter to store if the master SP is successful.

If the first stored procedure works fine in the master stored procedure, but the 2nd stored procedure fails, then will it automatically roll back all the SP's in the master SP or do I have to make some command?

Here is my procedure:

CREATE PROCEDURE [dbo].[spSavesomename] 
    -- Add the parameters for the stored procedure here

    @successful bit = null output
AS
BEGIN
begin transaction createSavebillinginvoice
    begin Try
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

   BEGIN 

   EXEC [dbo].[spNewBilling1]

   END

   BEGIN 

   EXEC [dbo].[spNewBilling2]

   END

   BEGIN 

   EXEC [dbo].[spNewBilling3]

   END 

   set @successful  = 1

   end Try

    begin Catch
        rollback transaction createSavesomename
        insert into dbo.tblErrorMessage(spName, errorMessage, systemDate) 
             values ('spSavesomename', ERROR_MESSAGE(), getdate())

        return
    end Catch
commit transaction createSavesomename
return
END

GO

Answer 1

Given only the code shown in the question, and assuming that none of the three sub-procs have any explicit transaction handling, then yes, an error in any of the three sub-procs will be caught and the ROLLBACK in the CATCH block will roll back all of the work.

BUT here are some things to note about transactions (at least in SQL Server):

• There is only ever one real transaction (the first one), no matter how many times you call BEGIN TRAN

  • You can name a transaction (as you have done here) and that name will appear in the logs, but naming only has meaning for the first / outer-most transaction (because again, the first one is the transaction).
  • Each time you called BEGIN TRAN, whether or not it is named, the transaction counter is incremented by 1.
  • You can see the current level by doing SELECT @@TRANCOUNT;
  • Any COMMIT commands issued when @@TRANCOUNT is at 2 or above do nothing more than reduce, one at a time, the transaction counter.
  • Nothing is ever committed until a COMMIT is issued when the @@TRANCOUNT is at 1
  • Just in case the information above does not indicate clearly: regardless of the transaction level, there is no actual nesting of transactions.

• Save points allow for creating a subset of work within the transaction that can be undone.

  • Save points are created/marked via the SAVE TRAN {save_point_name} command
  • Save points mark the beginning of the subset of work that can be undone without rolling back the entire transaction.
  • Save point names do not need to be unique, but using the same name more than once still creates distinct save points.
  • Save points can be nested.
  • Save points cannot be committed.
  • Save points can be undone via ROLLBACK {save_point_name}. (more on this below)
  • Rolling back a save point will undo any work that happened after the most recent call to SAVE TRAN {save_point_name}, including any save points created after the one being rolled-back was created (hence the "nesting").
  • Rolling back a save point has not effect on the transaction count/level
  • Any work done prior to the initial SAVE TRAN cannot be undone except by issuing a full ROLLBACK of the entire transaction.
  • Just to be clear: issuing a COMMIT when @@TRANCOUNT is at 2 or above, has no effect on save points (because again, transaction levels above 1 don't exist outside of that counter).

• You cannot commit specific named transactions. The transaction "name", if provided along with the COMMIT, is ignored and only exists for readability.

• A ROLLBACK issued without a name will always rollback ALL transactions.

• A ROLLBACK issued with a name must correspond to either:

  • The first transaction, assuming it was named:
    Assuming no SAVE TRAN has been called with the same transaction name, this will rollback ALL transactions.
  • A "save point" (described above):
    This behavior will "undo" all changed made since the most recent SAVE TRAN {save_point_name} was called.
  • If the first transaction was a) named and b) has had SAVE TRAN commands issued with its name, then each ROLLBACK of that transaction name will undo each save point until there are none left of that name. After that, a ROLLBACK issued of that name will rollback ALL transactions.

  • For example, assume the following commands were run in the order shown:

    BEGIN TRAN A -- @@TRANCOUNT is now 1
    -- DML Query 1
    SAVE TRAN A
    -- DML Query 2
    SAVE TRAN A
    -- DML Query 3
    
    BEGIN TRAN B -- @@TRANCOUNT is now 2
    SAVE TRAN B
    -- DML Query 4
    

    Now, if you issue (each of the following scenarios is independent of each other):

    • ROLLBACK TRAN B once: It will undo "DML Query 4". @@TRANCOUNT is still 2.
    • ROLLBACK TRAN B twice: It will undo "DML Query 4" and then error as there is no corresponding save point for "B". @@TRANCOUNT is still 2.
    • ROLLBACK TRAN A once: It will undo "DML Query 4" and "DML Query 3". @@TRANCOUNT is still 2.
    • ROLLBACK TRAN A twice: It will undo "DML Query 4", "DML Query 3", and "DML Query 2". @@TRANCOUNT is still 2.
    • ROLLBACK TRAN A thrice: It will undo "DML Query 4", "DML Query 3", and "DML Query 2". Then it will rollback the entire transaction (all that was left was "DML Query 1"). @@TRANCOUNT is now 0.
    • COMMIT once: @@TRANCOUNT goes down to 1.
    • COMMIT once and then ROLLBACK TRAN B once: @@TRANCOUNT goes down to 1. Then it will undo "DML Query 4" (proving that COMMIT didn't do anything) . @@TRANCOUNT is still 1.

• Transaction names and save point names:

  • can have up to 32 characters
  • are treated as having a binary Collation (not case-sensitive as the documentation currently states), regardless of the Instance-level or Database-level Collations.
  • For details, please see the Transaction Names section of the following post: What’s in a Name?: Inside the Wacky World of T-SQL Identifiers

• A stored procedure is not, in itself, an implicit transaction. Each query if no explicit transaction has been started, is an implicit transaction. This is why explicit transactions around single queries are not necessary unless there can be a programmatic reason to do a ROLLBACK, else any error in the query is an automatic rollback of that query.

• When calling a stored procedure, it must exit with the value of @@TRANCOUNT being the same as when it was called. Meaning, you cannot:

  • Start a BEGIN TRAN in the proc without committing it, expecting to commit in the calling/parent process.
  • You cannot issue a ROLLBACK if an explicit transaction was started prior to the proc being called as it will return @@TRANCOUNT to 0.

  If you exit a stored procedure with a transaction count that is either higher or lower than when it stared, you will get an error similar to:

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

• Table Variables, just like regular variables, are not bound by transactions.

---

Regarding having transaction handling in procs that can either be called independently (and hence need transaction handling) or call from other procs (hence not needing transaction handling): this can be accomplished in a couple different ways.

The way that I have been handling it for several years now that seems to work well is to only BEGIN / COMMIT / ROLLBACK at the outer-most layer. Sub-proc calls just skip the transaction commands. I have outlined below what I put into each proc (well, each one that needs transaction handling).

• At the top of each proc, DECLARE @InNestedTransaction BIT;

• In place of simple BEGIN TRAN, do:

  IF (@@TRANCOUNT = 0)
  BEGIN
     SET @InNestedTransaction = 0;
     BEGIN TRAN; -- only start a transaction if not already in one
  END;
  ELSE
  BEGIN
     SET @InNestedTransaction = 1;
  END;
  

• In place of simple COMMIT, do:

  IF (@@TRANCOUNT > 0 AND @InNestedTransaction = 0)
  BEGIN
     COMMIT;
  END;
  

• In place of simple ROLLBACK, do:

  IF (@@TRANCOUNT > 0 AND @InNestedTransaction = 0)
  BEGIN
     ROLLBACK;
  END;
  

This method should work the same regardless of whether the transaction was started within SQL Server or if it was started at the app layer.

For the full template of this Transaction handling within the TRY...CATCH construct, please see my answer to the following DBA.SE question: Are we required to handle Transaction in C# Code as well as in stored procedure.

---

Moving beyond the "basics", there are some additional nuances of transactions to be aware of:

• By default, Transactions are, most of the time, not automatically rolled-back / cancelled when an error occurs. This is usually not a problem as long as you have proper error handling and call ROLLBACK yourself. However, sometimes things get complicated, such as in the case of batch-aborting errors, or when using OPENQUERY (or Linked Servers in general) and an error occurs on the remote system. While most errors can be trapped using TRY...CATCH, there are two that cannot be trapped that way (can't remember which ones at the moment, though--researching). In these cases, you must use SET XACT_ABORT ON to properly rollback the Transaction.

  SET XACT_ABORT ON causes SQL Server to immediately roll-back any Transaction (if one is active) and abort the batch if any error occurs. This setting existed prior to SQL Server 2005, which introduced the TRY...CATCH construct. For the most part, TRY...CATCH handles most situations and so mostly obsoletes the need for XACT_ABORT ON. However, when using OPENQUERY (and possibly one other scenario that I can't remember at the moment), then you will still need to use SET XACT_ABORT ON;.

• Inside of a Trigger, XACT_ABORT is implicitly set to ON. This causes any error within the Trigger to cancel the entire DML statement that fired the Trigger.

• You should always have proper error handling, especially when using Transactions. The TRY...CATCH construct, introduced in SQL Server 2005, provides a means of handling nearly all situations, a welcome improvement over testing for @@ERROR after each statement, which didn't help much with batch-aborting errors.

  TRY...CATCH introduced a new "state", however. When not using the TRY...CATCH construct, if you have an active Transaction and an error occurs, then there are several paths that can be taken:

  • XACT_ABORT OFF and statement-aborting error: Transaction is still active and processing continues with the next statement, if any.
  • XACT_ABORT OFF and most batch-aborting errors: Transaction is still active and processing continues with the next batch, if any.
  • XACT_ABORT OFF and certain batch-aborting errors: Transaction is rolled-back and processing continues with the next batch, if any.
  • XACT_ABORT ON and any error: Transaction is rolled-back and processing continues with the next batch, if any.

  
  HOWEVER, when using TRY...CATCH, batch-aborting errors do not abort the batch, but instead transfer control to the CATCH block. When XACT_ABORT is OFF, the Transaction will still be active the vast majority of the time, and you will need to COMMIT, or most likely, ROLLBACK. But when encountering certain batch-aborting errors (such as with OPENQUERY), or when XACT_ABORT is ON, the Transaction will be in a new state, "uncommitable". In this state you cannot COMMIT, nor can you do any DML operations. All you can do is ROLLBACK and SELECT statements. However, in this "uncomittable" state, the Transaction was rolled-back upon the error occurring, and issuing the ROLLBACK is just a formality, but one that must be done.

  A function, XACT_STATE, can be used to determine if a Transaction is active, uncommitable, or doesn't exist. It is recommended (by some, at least) to check this function in the CATCH block to determine if the result is -1 (i.e. uncommitable) instead of testing if @@TRANCOUNT > 0. But with XACT_ABORT ON, that should be the only possible state to be in, so it seems that testing for @@TRANCOUNT > 0 and XACT_STATE() <> 0 are equivalent. On the other hand, when XACT_ABORT is OFF and there is an active Transaction, then it is possible to have a state of either 1 or -1 in the CATCH block, which allows for the possibility of issuing COMMIT instead of ROLLBACK (although, I cannot think of a case for when someone would want to COMMIT if the Transaction is commitable). More information and research on using XACT_STATE() within a CATCH block with XACT_ABORT ON can be found in my answer to the following DBA.SE question: In what cases a transaction can be committed from inside the CATCH block when XACT_ABORT is set to ON?. Please note that there is a minor bug with XACT_STATE() that causes it to falsely return 1 in certain scenarios: XACT_STATE() returns 1 when used in SELECT with some system variables but without FROM clause

---

Notes about the original code:

• You can remove the name given to the transaction as it is not helping any.
• You don't need the BEGIN and END around each EXEC call

Answer 2

Yes, if due to any error rollback code in your master stored procedure's catch statement will execute, it will rollback all the operations performed by any direct statement or through any of your nested stored procedures in it.

Even if you have not applied any explicit transaction in your nested stored procedures still these stored procedure will use implicit transaction and will commit on completion BUT either you have committed through explicit or implicit transaction in nested stored stored procedures SQL Server engine will ignore it and will rollback all actions by these nested stored procedures if master stored procedure is failed and transaction is roll-backed.

Every time the transaction is either committed or rolled back based on the action taken at the end of the outermost transaction. If the outer transaction is committed, the inner nested transactions are also committed. If the outer transaction is rolled back, then all inner transactions are also rolled back, regardless of whether or not the inner transactions were individually committed.

For reference http://technet.microsoft.com/en-us/library/ms189336(v=sql.105).aspx