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
SQL Server Database Mail configuration
Troubleshooting SQL Server Database mail failure
Send a SQL Server Query result in text format using database e-mail
5,594 total views, 1 views today