Partial Rollback in SQL Server Transaction using SavePoints
In this article, I am going to discuss how to do Partial Rollback in SQL Server Transaction using SavePoints with some examples. Please read our previous article where we discussed the ACID Properties of a Transaction in SQL Server with an example.
How to do Partial Rollback in SQL Server Transaction?
In order to do Partial Rollback in SQL Server Transaction, we are provided with the SAVE TRANSACTION Command. The SAVE TRANSACTION in SQL Server is basically used for dividing (or) breaking a transaction into multiple units. Once you divide a transaction into multiple units, then the user has a chance of rollbacking the transaction up to a specified point.
In other words we can say that, the SQL Server SavePoints Transaction allows us to roll back a part of a transaction instead of the entire transaction. So, if we are rolling back the transactions up to a specific savepoint, only the statements after the savepoint and before the rollback command will be rolled back. The syntax to use the savepoint transaction is given below.
Let us understand this with a simple example. We are going to use the following product table in this demo.
Please use the below SQL Scripts to create and populate the Product table with test data.
-- Create the Product Table CREATE TABLE Product ( ProductID INT PRIMARY KEY, Name VARCHAR(40), Price INT, Quantity INT ) GO -- Populate the Product Table with some test data INSERT INTO Product VALUES(101, 'Laptop', 15000, 100) INSERT INTO Product VALUES(102, 'Desktop', 20000, 150) INSERT INTO Product VALUES(103, 'Mobile', 3000, 200) INSERT INTO Product VALUES(104, 'Tablet', 4000, 250) GO
Example: In the below example we create three save point as SavePoint1, SavePoint2, and SavePoint3.
BEGIN TRANSACTION SAVE TRANSACTION SavePoint1 DELETE FROM Product WHERE ProductID = 101 SAVE TRANSACTION SavePoint2 DELETE FROM Product WHERE ProductID = 102 SAVE TRANSACTION SavePoint3 DELETE FROM Product WHERE ProductID = 103
Note: As per the definition it will rollback the statements which are present after the savepoint and before the Rollback Statement.
CASE1: ROLLBACK TRANSACTION SavePoint1
When we execute the above statement, it will rollback the statement starting from SavePoint1 and ends with SavePoint3 statement so that the user has a chance to rollback 3 records.
CASE2: ROLLBACK TRANSACTION SavePoint2
When we execute the above Rollback transaction statement, the transaction will start from the SavePoint2 statement and ends with the SavePoint3 statement, so that the user has a chance to rollback 2 records.
CASE3: ROLLBACK TRANSACTION SavePoint3
When we execute the above Rollback Transaction statement, it will rollback the statement that starts and ends with SavePoint3 so that the user has a chance to rollback 1 record.
Note: The savepoint name in SQL Server can have a maximum of 32 characters. If a savepoint variable is used instead of a savepoint name and a string with more than 32 characters is assigned to it, only the first 32 characters will define the savepoint name and the remaining characters will be ignored.
What happens when we provide multiple savepoints with the same name in a SQL Server Transaction?
It is possible in SQL server to have multiple savepoints with the same name, but in the case of a rollback to the save point, the transaction will be rolled back to the latest SAVE TRANSACTION using that name.
Let us see an example for better understanding
-- First Truncate the product table TRUNCATE TABLE Product BEGIN TRANSACTION SAVE TRANSACTION InsertSavePoint INSERT INTO Product VALUES(105, 'Product-5', 15000, 100) SAVE TRANSACTION InsertSavePoint INSERT INTO Product VALUES(106, 'Product-6', 20000, 150) SAVE TRANSACTION InsertSavePoint3 INSERT INTO Product VALUES(107, 'Product-7', 20000, 150)
Here we created three save points but two save points with the same name
ROLLBACK TRANSACTION InsertSavePoint
When we execute the above Rollback Transaction statement, it will rollback the statement starting from the second InsertSavePoint and hence SELECT * FROM Product will give us the following result.
So, in short, we can say that SAVEPOINT command in SQL Server is used to temporarily save a transaction. As a result, we can rollback to that point whenever required.
In the next article, I am going to discuss the nested transaction in SQL Server with examples. Here, in this article, I try to explain the Partial Rollback in SQL Server Transaction using SavePoints step by step with some examples.