Partial Rollback in SQL Server Transaction

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.

Partial Rollback in SQL Server using SavePoints

Let us understand this with a simple example. We are going to use the following product table in this demo.

Understanding savePoint Transaction in SQL Server

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.

Understanding Multiple SavePoints with same name in SQL Server Transaction

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. 

Leave a Reply

Your email address will not be published. Required fields are marked *