SQL ROLLBACK Transactions

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

If a savepoint is specified, it rolls back the transaction to that specific 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 understand the functionality of the ROLLBACK statement, we will first update a single record in a table within a transaction. After executing the update, we will use the ROLLBACK statement to revert the changes made by the update statement within the transaction.

The following example demonstrates how to use the BEGIN TRANSACTION and ROLLBACK statements in T-SQL to revert data modifications made during the 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;

Let’s understand above the T-SQL statement executed within the BEGIN TRAN/TRANSACTION block. This is the point where the data referenced by the connection is both logically and physically consistent. If an error occurs, any data modifications made after the BEGIN TRAN can be rolled back, restoring the data to this consistent state.

As shown, the initial select statement statement retrieves the record for EmpId 3 from the table. This allows us to view the current value of JobTitle before updating it for EmpId 3.

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

Subsequently, the ROLLBACK TRAN statement reverts the data modification made by the UPDATE statement, reverting the JobTitle to its original value (before the update) within the transaction.

The final SELECT statement retrieves the current data for EmpId 3. As you can see, the JobTitle remains unchanged from its original value before the update, because the new value was rolled back by the ROLLBACK TRAN statement.

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

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 shown, we have used a TRY..CATCH block in this case. The purpose of the TRY…CATCH block is to handle errors that may occur during the transaction. If an error arises while processing the transaction in the TRY block, control is immediately transferred to the CATCH block.

In the CATCH block, errors can be managed by either returning a custom or system-generated message or by reverting changes using the ROLLBACK TRANSACTION statement, depending on the requirements.

Let’s review the T-SQL statements provided. The initial SELECT statement retrieves the current value of JobTitle, which is the value before any updates are made.

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

After the successful update, the COMMIT TRANSACTION statement permanently saves the changes made by the UPDATE statement to the database.

To verify the update, let’s fetch the data for EmpID = 3 to confirm whether the record has been updated.

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

In case an error occurs in the TRY block, let’s simulate a custom error (such as division by zero) right after the UPDATE statement and before the COMMIT TRANSACTION statement in the T-SQL script.

Due to this error, control will pass to the CATCH block. Within the CATCH block, the defined ROLLBACK TRANSACTION statement will revert the data modifications made during the transaction to their original state.

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

As shown, the initial SELECT statement retrieves the JobTitle value, which is ‘Sr. HR Manager’  before any updates.

Following the update statement, a division-by-zero error occurs, causing control to transfer to the CATCH block.

This is confirmed by the output from the fourth SELECT statement, which displays ‘Inside the catch block’ reflecting the message defined within the CATCH block.

The fifth SELECT statement retrieves the values for EmpID 3, showing the updated JobTitle as ‘Human Resource Manager’.

However, immediately following this, a ROLLBACK TRANSACTION statement is executed, which reverts all changes to their original state. As a result, the final SELECT statement returns the original JobTitle, ‘Sr. HR Manager’. since the changes have been rolled back.

Using a SAVE TRANSACTION

The SAVE TRANSACTION statement sets a savepoint within a transaction, marking a location to which the transaction can be rolled back if part of the transaction is cancelled or an error occurs.

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