Nested T-SQL Transactions

Recently, I had an issue with nested transaction in SQL Server, pretty simple thing but I realized that I was’nt taking care of this for most of the T-SQL code I write. Fortunately, never had a problem but I thought I shoud post about this so I could remeber this.
Try running the following T-SQL code in SQL Server
BEGIN TRANSACTION
BEGIN TRANSACTION
— Some Code
ROLLBACK TRANSACTION
ROLLBACK TRANSACTION
This code simpley throws an error ”
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION”. Hey thats strange, I have two BEGIN statements so a I need a couple of ROLLBACKS to Roll them back, it seems logical but actually its not.
Transaction are managed in SQL Server through a transaction count, each new BEGIN TRAN statement adds one to the TRAN Count, and a single Rollback rightly makes the TRAN Count zero, so everything is Rolled back with one Rollback.

I can definately take care of this in my own code, but what happens if I am using a third party proceudre which ROLLBACKS the transaction, how do I know I have to put a ROLLBACK in my own code or not. If an encrypted third party procedure calls Rollback before my code, my procedure will throw error when it calls its Rollback.
The Best Pattern to do this is to use the @@TRANCOUNT variable before EACH ROLLBACK, so the the way ROLLBACK should be called is
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK
END
Neat thing to learn !

Advertisements

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