Important Note for Rollback Transaction

Commit and Rollback Transactions in SQL Server is a huge topic in itself. Somethings mentioned here that can be noted for working with rollback transaction.

You can set a savepoint/marker within a transaction using SAVE TRANSACTIONS. The savepoint defines a location in your code, to which a transaction can rollback if part of the transaction is conditionally canceled   

If there are no savepoints defined, then in case of an error, a ROLLBACK TRANSACTION rolls back to the beginning of the transaction.    

You can find out if a transaction is still active using SELECT @@trancount. The @@trancount function is used to monitor the current status of a transaction. When @@trancount > 0, this means that the transaction is still open and in progress. That is why we check the value of @@trancount > 0 in the catch block to make sure it is open and we can roll back the transaction.    

The value of @@trancount is initially 0 to start with. When BEGIN TRANSACTION is executed, @@trancount gets incremented. When COMMIT TRANSACTION is executed, @@trancount gets decremented. When ROLLBACK TRANSACTION is executed, the transaction is canceled and @@trancount returns to 0.    

A transaction cannot be rolled back once the COMMIT TRANSACTION statement is execute.

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

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.

Leave a Reply

Your email address will not be published. Required fields are marked *