SQL ROLLBACK Transactions

SQL ROLLBACK transaction statement rolls back an explicit or implicit transaction to the beginning of the transaction.

If savepoint is specified then it rolls back the transaction to a savepoint within the transaction.

Basically, it removes all the modification that is made from the beginning of the transaction or to specified savepoint.

SYNTAX

ROLLBACK { TRAN | TRANSACTION }
[ transaction_name | @tran_name_variable | savepoint_name | @savepoint_variable ]

transaction_name
Is the name specified to the transaction on BEGIN TRANSACTION. When nesting transactions, transaction_name must be the name from the outermost BEGIN TRANSACTION statement. transaction_name is always case-sensitive, even when the instance of SQL Server is not case-sensitive.

@ tran_name_variable
Is the name of a user-defined variable containing a valid transaction name.

savepoint_name
Is savepoint_name from a SAVE TRANSACTION statement. It determines when a conditional rollback should affect only part of the transaction.

@ savepoint_variable
Is name of a variable which contains a name of savepoint.

Lets look at an example using a ROLLBACK transaction in SQL.

First we create a table named EmpJobTitle and insert some dummy records into table.

CREATE TABLE dbo.EmpJobTitle
(
EmpId INT IDENTITY(1,1),
FirstName VARCHAR(150),
LastName VARCHAR(150),
JobTitle VARCHAR(50)
)

INSERT INTO dbo.EmpJobTitle(FirstName, LastName, JobTitle)
VALUES
('Subham', 'Jindal', 'CEO'),
('Ramesh', 'Khatri', 'AVP'),
('Rohit', 'Kumar', 'HR Manager'), 
('Sujoy', 'Ghosh', 'Software Developer'), 
('Lokesh', 'Kumar', 'Team Lead') ,
('Mandeep', 'Kaur', 'Software Developer'), 
('Rajat', 'Prakash', 'Software Developer'),
('Manhor', 'Aggarwal', 'Program Manager')

Now we have a table EmpJobTitle in SQL Server database as shown below.

SELECT * FROM dbo.EmpJobTitle

Using ROLLBACK Transaction

To see the ability of ROLLBACK statement, first we will update a single record in table under the transaction and after that execute the rollback transaction statement in order to roll back the change that is made by update statement within transaction.

Following statement demonstrate how the T-SQL statement is run under the begin transaction and rollback the data modification made within transaction.

BEGIN TRAN T
SELECT * FROM dbo.EmpJobTitle 
WHERE EmpId =3;

UPDATE dbo.EmpJobTitle SET JobTitle ='Sr. HR Manager'
WHERE EmpId =3;

SELECT * FROM dbo.EmpJobTitle 
WHERE EmpId =3;

ROLLBACK TRAN T

SELECT * FROM dbo.EmpJobTitle 
WHERE EmpId =3;

Lets understand the above T-SQL statement that is run under the BEGIN TRAN/TRANSACTION, which is the point at which the data referenced by a connection is logically and physically consistent, and in case when any error occurred, all data modifications made after the BEGIN TRAN can be rolled back to return the data to this state of consistency.

As you can see, first select statement fetches the record for EmpId =3  from table, the purpose of that is to see the actual value of JobTitle before updating the value of JobTitle for the EmpId =3.

After that, the update statement update the value of JobTitle from ‘Hr Manager’ to ‘Sr. HR Manager’ for EmpId =3.

Next, the ROLLBACK TRAN statement , rolls back the data modification that is made by update statement to actual values (values which were before updating) within transaction.

The last select statement fetches the current data for EmpId =3, and you can see still the JobTitle is same as was in the starting that is before updating the value of JobTitle because the new updated value is rolled backed by ROLLBACK transaction statement.

Using COMMIT TRANSACTION and ROLLBACK TRANSACTION with TRY.. CATCH Block

When transaction is successful then to save the data modification permanently to the database, you need to commit the transaction, which is done using the COMMIT TRANSACTION statement. It marks the end of a successful implicit or explicit transaction.

Lets see the example when transaction is successfully committed after updating a value.

BEGIN TRY
 BEGIN TRAN T
   SELECT * FROM dbo.EmpJobTitle 
   WHERE EmpId =3 ;

   UPDATE dbo.EmpJobTitle SET JobTitle ='Sr. HR Manager'
   WHERE EmpId =3;

 COMMIT TRAN T;

   SELECT * FROM dbo.EmpJobTitle 
   WHERE EmpId=3;

END TRY

END TRY
BEGIN CATCH
     SELECT * FROM dbo.EmpJobTitle 
     WHERE EmpId =3;

     ROLLBACK TRAN T

     SELECT * FROM dbo.EmpJobTitle 
     WHERE EmpId =3;

END CATCH

As you can see, This time we have used TRY..CATCH block, the reason of using try catch block is that if any error occurred while the transaction is processed in TRY BLOCK the control is immediately passed to the CATCH BLOCK there error can be handled either by returning a custom message/system messages or reverting the changes using ROLLBACK transaction statement depends on requirement.

Lets understand the above T-SQL statement, first select statement fetches the actual value of JobTitle which is before updating the value.

Next the update statement, updates the JobTitle value to ‘Sr. HR Manager‘.

After that on successful update the Commit transaction statement commits the changes made by update statement to the database permanently.

You can verify whether the record is updated or not, lets fetch the data for EmpId=3.

And you can see, the JobTitle value is updated to ‘Sr. HR Manager’.

Just in case if any error occurred in TRY BLOCK for this Lets produce a custom error (divided by zero) in TRY BLOCK, just right after the update statement and before commit transaction statement in above T-SQL script, and because of that error a control is passed to CATCH BLOCK and within CATCH BLOCK a defined ROLLBACK statement, rolls back the data modification made within transactions to the original state.

You can see the output of the above T-SQL as shown below.

As you can see, before updating the value, the first select statement fetches the JobTitle value that is ‘Sr. HR Manager’.

When a value of JobTitle is updated by update statement, immediately after that a divided by zero error occurred which throw the control to CATCH BLOCK.

Which can be seen in the output return by 4th select statement that is ‘Inside the catch block’ which is from the statement written within catch block.

Now the 5th select statement, fetches the values for EmpID =3 and you can see that is a new updated value  ‘Human Resource Manager’.

But Just right after a ROLLBACK Transaction statement  defined which executes and rolls back all the changes to their original state that means revert back to actual values. That’s why the last select statement returns the old value that is ‘Sr. HR Manager’.

Using a SAVE TRANSACTION 

SAVE TRANSACTION sets a savepoint within a transaction which defines a location to which a transaction can rolled back if part of the transaction is conditionally canceled or any error occurred.

Following T-SQL uses a transaction savepoint to roll back only the deleted data when any error occurred.

You can see, within transaction a value is updated for EmpId =3 and a record for EmpID =7 is deleted.

Also you will notice that save point SaveUpdates is defined after update statement that means if any error occurred then transaction is rolled back only to the specified save point so in this case the updated values is not rolled back, while the delete statement is specified after save point that can be rolled back in case of any error occurred.

BEGIN TRY
 BEGIN TRAN T
    SELECT * FROM dbo.EmpJobTitle 
    WHERE EmpId in (3,7);

    UPDATE dbo.EmpJobTitle SET JobTitle ='Human Resource Manager'
    WHERE EmpId =3;

 SAVE TRANSACTION SaveUpdates --- Define a save point

    DELETE FROM dbo.EmpJobTitle WHERE EmpId = 7

    SELECT 1/0; ----------- Error Divide by Zero

 COMMIT TRAN T;

    SELECT * FROM dbo.EmpJobTitle 
    WHERE EmpId in(3,7);

END TRY
BEGIN CATCH

    SELECT 'Inside the Catch Block'
    SELECT * FROM dbo.EmpJobTitle 
    WHERE EmpId in (3,7);

ROLLBACK TRAN SaveUpdates ----------Rollback to Save Point

    SELECT * FROM dbo.EmpJobTitle 
    WHERE EmpId in (3,7);

END CATCH

Lets see the output of above statement.

Also read

Dirty Reads problem in concurrent Transactions

TRY..CATCH block

SQL Server Database Mail configuration

Troubleshooting SQL Server Database mail failure

Send a SQL Server Query result in text format using database e-mail

SQL Basics Tutorial	SQL Advance Tutorial	SSRS	Interview Q & A
SQL Create table	SQL Server Stored Procedure	Create a New SSRS Project	List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLE	SQL Server Merge	Create a Shared Data Source in SSRS	SQL Server Question & Answer Quiz
SQL Drop	SQL Server Pivot	Create a SSRS Tabular Report / Detail Report
..... More	.... More	....More
Power BI Tutorial	Azure Tutorial	Python Tutorial	SQL Server Tips & Tricks
Download and Install Power BI Desktop	Create an Azure storage account	Learn Python & ML Step by step	Enable Dark theme in SQL Server Management studio
Connect Power BI to SQL Server	Upload files to Azure storage container		SQL Server Template Explorer
Create Report ToolTip Pages in Power BI	Create Azure SQL Database Server		Displaying line numbers in Query Editor Window
....More	....More		....More