This Blog Has Been Moved !
This Blog Has been moved to http://aleemkhan.wordpress.com
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
-- Some Code
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
Neat thing to learn !
RSS Feed for this Site
Archives by Date