January 11, 2011

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

CommitTransaction

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:

ROllBack transaction

On running this query, you get the following error

ROllBack transaction

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


Did you like this post?
kick it on DotNetKicks.com
subscribe via rss subscribe via e-mail
print this post follow me on twitter


About The Author

Suprotim Agarwal, ASP.NET Architecture MVP works as an Architect Consultant and provides consultancy on how to design and develop Web applications.

Suprotim is also the founder and primary contributor to DevCurry, DotNetCurry and SQLServerCurry. He has also written an EBook 51 Recipes using jQuery with ASP.NET Controls.

Follow him on twitter @suprotimagarwal

 
  Feedback:

comments

0 Responses to "Rollback Transaction in SQL Server"
 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions