Commit and Rollback Transactions in SQL Server is a huge topic in itself. In this article, I will explain how to use a Try..Catch block to commit and rollback transaction. In subsequent articles, we will explore how to rollback nested transactions too.
Consider this example, where we will first write a T-SQL code which commits the transaction and adds new record in a table
CREATE TABLE TT (num int)
CREATE PROC SP1
INSERT INTO TT(num) VALUES (630)
INSERT INTO TT(num) VALUES (890)
IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
-- Error Message
DECLARE @Err nvarchar(1000)
SET @Err = ERROR_MESSAGE()
Run the code shown above and query the table
SELECT * FROM TT
You should get the following output
Let us now manually raise an error to see if it does a transaction rollback. Change the T-SQL Code as shown below to manually raise an error in the TRY block, which will cause an error and the transaction to rollback:
On running this query, you get the following error
Doing a SELECT * FROM TT still returns only two records, since the transaction was rollback and the new row never got inserted
Points to Consider:
- 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
Rollback Nested Transactions
You can have nested transactions in SQL Server. For example you can have a stored procedure with a BEGIN TRANSACTION statement, which invokes a stored procedure also containing a BEGIN TRANSACTION statement and so on. Now if an error occurs inside a child stored procedure, what happens to the parent transaction?
Stay tuned for my next article to know the answer! Update: Check my next article Rollback Nested Transactions in Stored Procedure - SQL Server