How to handle Nested Transactions in TSQL (SQL Server 2005)

In SQL Server 2000, we used to handle the errors while performing the DML queries by means of global variable @@Error. These errors may occur due to many reasons like data type mismatch, referential integrity violation etc. The @@error variable tells us whether the last statement (immediate before the @@error variable is used) caused any error or executed successfully. We could check its value for the same. If it is more then 0, then there is some error. But using @error variable & handling the error this way was not handy in solving many problems/requirements.  For example if we have multiple DML statements being executed in a sequence and we want to take some action as soon as any of these statements causes any error. In that case after each & every transaction we have to check for @@error variable.

In SQL Server 2005, TRY-CATCH block was introduced to enhance the error handling process of TSQL. In this we can specify a TRY block and can catch the error(s) in CATCH block as caused by any of those queries defined in the TRY block. And this way we can either COMMIT or ROLLBACK them in TRY & CATCH respectively.

Now sometimes we need nested transactions to be configured. Nested transactions mean we may require having some inner transactions with one or more outer transaction. In that scenario, we must have to be very careful in Committing or rolling back the transaction.

Points to be remembered:

  1. A COMMIT statement in the Outer transaction will commit all the open inner transactions.
  2. A Commit statement in the Outer transaction will not commit the changes rolled back by the inner transactions.
  3. A Rollback statement in the Outer transaction will rollover all the inner transaction, irrespective of the Commit/Rollback fired in the inner transaction(s).

SELECT * FROM TESTTABLE

initial result

initial result

 

 

 

 

 

BEGIN TRANSACTION  –Outer TRAN

BEGIN TRY

BEGIN TRAN A   –INNER TRAN

   BEGIN TRY                  

       INSERT INTO TESTTABLE VALUES (11111)                   

       COMMIT TRAN A

    END TRY

    BEGIN CATCH

       ROLLBACK TRAN A

     END CATCH

   ROLLBACK TRAN  –Rollback all

END TRY

BEGIN CATCH

  ROLLBACK TRAN

END CATCH

Result: 

Final result

Final result

 

 

 

 

 

 

Though we have committed the inner transaction (‘A’) after insertion, all data got rolled back by the Rollback statement of the outer transaction.

Note: In general, only open transactions can be committed or rolled back. So check it XACT_STATE() <> 0 before commit or rollback.

Advertisements
This entry was posted in SQL Server. Bookmark the permalink.

7 Responses to How to handle Nested Transactions in TSQL (SQL Server 2005)

  1. Obiora Obi says:

    Nice one Munish, but the line
    ROLLBACK TRAN A
    will cause an error because there is no savepoint named A. A named transaction is not same as a savepoint. You can create a savepoint with:
    SAVE TRANSACTION {savepoint-name}

  2. Hey Obiora,

    It’s not like the case, In SQL Server 2005, we can also name the transactions as well.
    And the above SQL script runs very well.

    Thanks,
    Munish Bansal

  3. J.D. Mizer says:

    Munish,

    I have to disagree with you about the use of transaction naming an a SQL 2005 SPROC. I have the following Stored Procedure:

    CREATE PROCEDURE [dbo].[sp_LinkedDatetimeInformation_Insert]
    (
    @newDetailLinkTableName nvarchar(100),
    @newParentId nvarchar(50),
    @newParentRecordId bigint,
    @newDateTimeTypeCode nvarchar(50),
    @newLinkedDetailCode nvarchar(10),
    @newRecordedDateTime datetime,
    @newTimeQualifier nvarchar(10),
    @useTimeQualifierCode bit,
    @useTimeValue bit,
    @newDetailLinkId bigint = -1 OUTPUT
    )
    AS
    BEGIN
    SET NOCOUNT ON;
    BEGIN TRANSACTION trn_LinkedDatetimeInfo_Insert
    BEGIN TRY

    DECLARE @linkedDetailRecordId bigint

    exec sp_DatetimeInformation_Insert
    @newDateTimeTypeCode,
    @newLinkedDetailCode,
    @newRecordedDateTime,
    @newTimeQualifier,
    @useTimeQualifierCode,
    @useTimeValue,
    @linkedDetailRecordId OUTPUT

    INSERT INTO tbl_datetime_information_links
    (detailRecordId, tableName, parentRecordId, parentId, linkedDetailCode, recordStatus, linkedRecordHashValue)
    VALUES
    (@linkedDetailRecordId,
    @newDetailLinkTableName,
    @newParentRecordId,
    @newParentId,
    @newLinkedDetailCode,
    0x1)

    SET @newDetailLinkId = SCOPE_IDENTITY()

    IF(@@ROWCOUNT 0)
    ROLLBACK TRANSACTION trn_LinkedDatetimeInfo_Insert

    RAISERROR(@exception_message,@exception_level,@exception_state)

    END CATCH

    COMMIT TRANSACTION trn_LinkedDatetimeInfo_Insert

    END

    Which raises an error that the trn_LinkedDatetimeInfo_Insert cannot be rolled back because there is not a transaction of that name. I realize that we can name transactions, but I do not think that the naming works correctly with nested transactions. I haven’t looked into this much further than doing a google search, but I continue to get the error regardless of where I open the transaction (in the try, out of the try, etc..) Maybe I am missing something?

  4. J.D. Mizer says:

    For some reason the SQL that I pasted in was corrupted.
    I have repasted below, but I am not sure why it was corrupted to begin with…

    CREATE PROCEDURE [dbo].[sp_LinkedDatetimeInformation_Insert]
    (
    @newDetailLinkTableName nvarchar(100),
    @newParentId nvarchar(50),
    @newParentRecordId bigint,
    @newDateTimeTypeCode nvarchar(50),
    @newLinkedDetailCode nvarchar(10),
    @newRecordedDateTime datetime,
    @newTimeQualifier nvarchar(10),
    @useTimeQualifierCode bit,
    @useTimeValue bit,
    @newDetailLinkId bigint = -1 OUTPUT
    )
    AS
    BEGIN
    SET NOCOUNT ON;

    BEGIN TRANSACTION trn_LinkedDatetimeInfo_Insert
    BEGIN TRY

    DECLARE @linkedDetailRecordId bigint

    exec sp_DatetimeInformation_Insert
    @newDateTimeTypeCode,
    @newLinkedDetailCode,
    @newRecordedDateTime,
    @newTimeQualifier,
    @useTimeQualifierCode,
    @useTimeValue,
    @linkedDetailRecordId OUTPUT

    INSERT INTO tbl_datetime_information_links
    (detailRecordId, tableName, parentRecordId, parentId, linkedDetailCode, recordStatus)
    VALUES
    (@linkedDetailRecordId,
    @newDetailLinkTableName,
    @newParentRecordId,
    @newParentId,
    @newLinkedDetailCode,
    0x1)

    SET @newDetailLinkId = SCOPE_IDENTITY()

    IF(@@ROWCOUNT 0)
    ROLLBACK TRANSACTION trn_LinkedDatetimeInfo_Insert

    RAISERROR(@exception_message,@exception_level,@exception_state)

    END CATCH

    COMMIT TRANSACTION trn_LinkedDatetimeInfo_Insert

    END

  5. J.D. Mizer says:

    Did it again… sorry,

    Basically when an exception occurs in the procedure the CATCH block is not recognizing my Transaction and therefore will not roll it back without an error. (Seems to be getting rolled back by the outer transaction)

  6. Masood says:

    Please suggest me the result in following scenario:

    Begin Trans Temp
    while(i<10)
    Begin
    Insert into tbltemp values(a,b)
    RollBack Trans Temp
    End

    Commit Trans Temp

    Or

    Begin Trans Temp
    while(i<10)
    Begin
    Insert into tbltemp values(a,b)
    Commit Trans Temp
    End

    RollBack Trans Temp

  7. Pingback: Fix Transaction Error Handling Sql Server 2005 Windows XP, Vista, 7, 8 [Solved]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s