Shopping through STARKUPBD. Happy Shopping

Shopping through STARKUPBD. Happy Shopping

Important Note for Rollback Transaction

Category: SQL Post By: Admin Post Date: 15/3/2016

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.

1.
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   

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

3.
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.    

4.
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.    

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

6.
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

7.
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 Comment
avatar
Anonymous
Comments
X


Twitter