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