SAVEPOINT in MySQL

Back to: MySQL Tutorials for Beginners and Professionals

SAVEPOINT in MySQL with Examples

In this article, I am going to discuss SAVEPOINT in MySQL with Examples. Please read our previous article where we discussed Transaction Management in MySQL. At the end of this article, you will understand how to perform Partial Rollback in MySQL using SAVEPOINT with Examples.

What is SAVEPOINT in MySQL?

The SAVEPOINT in MySQL is used for dividing (or) breaking a transaction into multiple units so that the user has a chance of roll backing the transaction up to a specified point. That means using Save Point we can roll back a part of a transaction instead of the entire transaction.

Example to understand SAVEPOINT in MySQL

We are going to use the following Product table to understand SAVEPOINT in MySQL.

Please use the following SQL Script to create and populate the Product table with the required sample data.

CREATE DATABASE ShoppingCart;
USE ShoppingCart;

CREATE TABLE Product
(
 ProductId INT PRIMARY KEY, 
 ProductName VARCHAR(40), 
 Price INT,
 Quantity INT
 );
 
 -- Populate Product Table with test data
 INSERT INTO Product VALUES(1001, 'Product-1', 1000, 100);
 INSERT INTO Product VALUES(1002, 'Product-2', 2000, 150);
 INSERT INTO Product VALUES(1003, 'Product-3', 3000, 200);
 INSERT INTO Product VALUES(1004, 'Product-4', 4000, 250);

How to create SAVEPOINT in MySQL?

The SAVEPOINT statement in MySQL is used to save a transaction temporarily. We can save multiple SAVEPOINT in a single transaction. We can ROLLBACK the transaction to a given SAVEPOINT and the transaction after that SAVEPOINT are revert back to its previous state. We cannot ROLLBACK to a SAVEPOINT after the COMMIT statement.

In order to create a save point or you can say logical point, we need to use the SAVEPOINT command followed by the save point name i.e. SAVEPOINT SAVEPOINT_NAME and ROLLBACK to the SAVEPOINT are ROLLBACK TO SAVEPOINT_NAME.

In order to understand this better please have a look at the following image. As you can see in the following image, first we start a transaction and then create three Save Points (Logical Points and Check Point) using the SAVEPOINT command.

As per the definition, it will roll back the statements which are present after the SAVEPOINT and before the Rollback Statement. As we created three save points (SavePoint1, SavePoint2, and SavePoint3), let us understand what happens when we execute a particular savepoint with the rollback command.

CASE1:
ROLLBACK TO SavePoint1;
When we execute the above Rollback command, it will roll back the statements which are starting from SavePoint1, and before the rollback statement. That means in our example, it will roll back all the 6 Insert statements.

CASE2:
ROLLBACK TO SavePoint2;
When we execute the above Rollback statement, then it will roll back the statements which are starting from SavePoint2 and before the Rollback statement. That means, in this case, it will roll back 4 Insert statements.

CASE3:
ROLLBACK TO SavePoint3;
When we execute the above Rollback statement, it will roll back the statements which are present after the SavePoint3 and before the Rollback Command. That means in this case, it will roll back two insert statements.

Now, I hope you understand the concept of SAVEPOINT and also understand how to perform Partial Rollback in MySQL using SAVEPOINT.

Partial Rollback in MySQL using SAVEPOINT:

Whatever we discussed as of now, let us put all these things in an example. Let us understand Partial Rollback with examples. First, execute the following SQL statements. The following SQL statement will create a transaction with three save points.

START TRANSACTION; 
    SAVEPOINT SavePoint1;
        INSERT INTO Product VALUES(1005, 'Product-5', 5000, 500);
    	INSERT INTO Product VALUES(1006, 'Product-6', 6000, 600);
        
    SAVEPOINT SavePoint2;
        INSERT INTO Product VALUES(1007, 'Product-7', 7000, 700);
     	INSERT INTO Product VALUES(1008, 'Product-8', 8000, 800);
        
    SAVEPOINT SavePoint3;
        INSERT INTO Product VALUES(1009, 'Product-9', 9000, 900);
     	INSERT INTO Product VALUES(1010, 'Product-10', 10000, 1000);

Now, execute the following rollback statement which should roll back four records i.e. the INSERT statements starting from the second save point.

ROLLBACK TO SavePoint2;

Once you execute the above rollback statement, then commit the transaction by executing the below commit statement which will commit two records (ProductId 1005 and 1006) into the database.

COMMIT;

Now check the Product table and there should be the two records with ProductId 1005 and 1006 as shown in the below image.

In the next article, I am going to discuss Nested Transactions in MySQL using SAVEPOINT with Examples. Here, in this article, how to perform Partial Rollback in MySQL using SAVEPOINT with Examples. I hope you enjoy this article.