CODEWITHSUNDEEP
sql-serverstored-proceduresnested-transactions
barnonline
barnonline

Reputation: 327

Recommendations regarding nested transactions in SQL Server

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

Upvotes: 3

Views: 4431

Answers (1)

Hoots
Hoots

Reputation: 1976

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

Upvotes: 1

Related Questions