Recommendations regarding nested transactions in SQL Server

Question

I have some "base operation" stored procedures, like BookAVehicle and UnBookAVehicle. They are both in a transaction.

But now I need to have a somewhat more complex stored procedure: RescheduleBooking. It also needs to be transactional.

Now, from within ResceduleBooking I want to call BookAVehicle, and in this case I don't want the inner transaction to rollback.

But when I call BookAVehicle directly, I want to keep the rollback.

Any suggestion on how to do this elegantly?

I was thinking of something along the lines of having a "wrapper" stored procedure that as a parameter takes the name of a stored procedure and only contains a transaction and a call to the parameter stored procedure.

So when I call it "directly" I call:

TransactionWrapper(BookAVehicleWithoutTrans)

and when I call it from another transaction I call:

RescheduleBooking -> BookAVehicleWithoutTrans

It's a little dirty secret: SQL Server *appears* to support nested transactions - but it really does not. It's a bit of "eye-wash" on the developers - if you roll back inside a nested transaction, **all** transactions currently running will be rolled back. See [Paul Randal's SQL Server DBA Myth-a-day post](http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-2630-nested-transactions-are-real/) on that topic. — marc_s, Jun 17 '13 at 14:16
possible duplicate of [Nested stored procedures containing TRY CATCH ROLLBACK pattern?](http://stackoverflow.com/questions/2073737/nested-stored-procedures-containing-try-catch-rollback-pattern) This uses the link from @marc_s and deals with nested procs that maintain the transaction consistency no matter how nested — gbn, Jun 17 '13 at 14:27
marc_s I know of rollback of the nested transaction, I'm sorry if that was not evident from my post — barnonline, Jun 18 '13 at 07:34

Hoots · Answer 1 · 2014-05-23T13:52:58.337

When you do a BEGIN TRANSACTION an internal counter is incremented @@TRANCOUNT. ROLLBACK TRANSACTION will rollback all BEGIN TRANSACTIONS setting @@TRANCOUNT to 0. Doing a commit transaction will only decrement @@TRANCOUNT, it will do a full commit when @@TRANCOUNT is 1 before setting it to 0.

With that in mind, Assuming you have paired BEGIN and COMMIT TRANSACTIONS in your Book and UnBook procedures I would do the RescheduleBooking procedure something like the following which will maintain the first book even if the unbook fails...

CREATE PROCEDURE RescheduleBooking ...
AS
BEGIN
      BEGIN TRY
         BEGIN TRANSACTION
         EXEC BookAVehicle ...
         COMMIT TRANSACTION
      END TRY
      BEGIN CATCH
         IF @@TRANCOUNT > 0
         BEGIN
             ROLLBACK TRANSACTION
         END
         RETURN
      END CATCH;

   -- If the unbook fails the booking above will still stay.
      BEGIN TRY
         BEGIN TRANSACTION
         EXEC UnBookAVehicle ...
         COMMIT TRANSACTION
      END TRY
      BEGIN CATCH
         IF @@TRANCOUNT > 0
         BEGIN
             ROLLBACK TRANSACTION
         END
         RETURN
      END CATCH;
END

Recommendations regarding nested transactions in SQL Server

1 Answers1