Rollback Transaction in SQL Server

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)
GO
CREATE PROC SP1
AS
BEGIN TRY
BEGIN TRANSACTION
INSERT INTO TT(num) VALUES (630)
INSERT INTO TT(num) VALUES (890)
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
-- Error Message
DECLARE @Err nvarchar(1000)
SET @Err = ERROR_MESSAGE()
RAISERROR (@Err,16,1)
END CATCH
GO
EXEC SP1

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

About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook