January 13, 2011

Rollback Nested Transactions in Stored Procedure - SQL Server




In a previous article Rollback Transaction in SQL Server , l explained how to use a Try..Catch block to commit and rollback transaction in stored procedure. In this article, we will use the same example to see how to rollback nested transactions in Stored Procedures.

CREATE TABLE TT (num int)
GO
INSERT INTO TT(num) VALUES (50)
GO

-- Code from SQLServerCurry.com
-- Create First Proc with Transactions
CREATE PROC SP1
AS
BEGIN TRY
BEGIN TRAN
INSERT INTO TT(num) VALUES (100)
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
END CATCH
GO

-- Create Second Proc with Transactions
-- Raise an Error in this Proc
CREATE PROC SP2
AS
BEGIN TRY
BEGIN TRAN
INSERT INTO TT(num) VALUES (200)
RAISERROR('Manually raised error', 17, 1)
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
END CATCH
GO

-- Create Main Procedure
CREATE PROC MainProc
AS
BEGIN
BEGIN TRY
BEGIN TRAN
INSERT INTO TT(num) VALUES (300)
EXEC SP1 -- Execute StoredProc 1
EXEC SP2 -- Execute StoredProc 2
COMMIT TRAN
END TRY
BEGIN CATCH
SELECT @@trancount
IF @@trancount > 0 ROLLBACK TRAN
-- Error Message
--DECLARE @Err nvarchar(1000)
--SET @Err = ERROR_MESSAGE()
RAISERROR ('Error in Proc',16,1)
END CATCH
END

GO

EXEC MainProc

SELECT * FROM TT

DROP TABLE TT
DROP PROC SP1
DROP PROC SP2
DROP PROC MAINProc

Shown above are two Stored Procedures (SP1 and SP2) with transaction processing enabled, nested within another stored procedure (Main Proc) which also has transaction processing. This leads to nested transaction. The proc SP2 manually raises an error, causing its transaction to rollback. On running the code above, you will get the following error

Transaction Error

which shows that an error in the nested stored procedure, causes a rollback, which in turn will roll back everything. Doing a SELECT * FROM TT shows that no new rows were inserted in the table.

Points to consider:

  • When you are using nested transactions, you must execute a COMMIT TRAN statement for each BEGIN TRAN statement issued, for the transaction to complete successfully
  • Although I haven’t done so, in complex nested transactions, you can check the value of @@trancount to see if it is active, before using another BEGIN TRAN. If it is active, you can use SAVE TRAN instead. If @@trancount is 0, you are no more in a transaction. You can print the value of @@@trancount in these stored procedures to see how its value changes.
  • If you are using SAVEPOINTS, I saw a very interesting point about nested transactions discussed in this forum. If a ROLLBACK TRANSACTION transaction_name statement using the name of the outer transaction is executed at any level of a set of nested transactions, all of the nested transactions are rolled back. If a ROLLBACK WORK or ROLLBACK TRANSACTION statement without a transaction_name parameter is executed at any level of a set of nested transaction, it rolls back all of the nested transactions, including the outermost transaction.

Note 1: Although the code example shown above runs, what I was originally attempting is to do is access the ERROR_MESSAGE() in the main procedure

However when I tried so, SQL Server fired an error

Msg 50000, Level 16, State 1, Procedure MainProc, Line 19
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0.

I tried a couple of solutions for this misleading error message, including SET XACT_ABORT ON , however nothing worked! Finally removing the ERROR_MESSAGE() solved it. If anyone knows a solution to this problem, please post it in the comments section.

If you want to see a simpler example of using a Try..Catch block to commit and rollback transaction, check my article Rollback Transaction in Stored Procedures in SQL Server


Did you like this post?
kick it on DotNetKicks.com
subscribe via rss subscribe via e-mail
print this post follow me on twitter


About The Author

Suprotim Agarwal, ASP.NET Architecture MVP works as an Architect Consultant and provides consultancy on how to design and develop Web applications.

Suprotim is also the founder and primary contributor to DevCurry, DotNetCurry and SQLServerCurry. He has also written an EBook 51 Recipes using jQuery with ASP.NET Controls.

Follow him on twitter @suprotimagarwal

 
  Feedback:

comments

3 Responses to "Rollback Nested Transactions in Stored Procedure - SQL Server"
  1. Anonymous said...
    October 16, 2011 at 12:11 PM

    So a "....ASP.NET Architecture MVP, MCSD, MCAD, MCDBA, MCSE, is the CEO of A2Z Knowledge Visuals Pvt..." doesn't know how to get rid of the error "Transaction count after EXECUTE". What is this then, a graphomania? :-)
    Re-throw the original message before rollback or use named transactions, right? LOL.

  2. Suprotim Agarwal said...
    October 16, 2011 at 7:35 PM

    Named Transactions is Ok for a DB with operations that are not logged frequently. But for me it is not an option here as the database I work on has operations that are bulk logged. So they would be blocked anyways and would lead to errors.

  3. myprogrammingexp said...
    September 11, 2013 at 11:58 AM

    SQL server does not support nested transactions.

    http://youtu.be/MGFfQyJMO9E

 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions