SAVEPOINT in Oracle

Back to: Oracle Tutorials for Beginners and Professionals

SAVEPOINT in Oracle with Examples

In this article, I am going to discuss how to perform Partial Rollback using SAVEPOINT in Oracle with Examples. Please read our previous article where we discussed Transaction Control Language (TCL) Commands in Oracle. At the end of this article, you will understand how to perform Partial Rollback in Oracle using SAVEPOINT with Examples.

What is SAVEPOINT in Oracle?

The SAVEPOINT in Oracle 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. Whenever a user creates SAVEPOINT within the transaction then internally system is allocating a special memory for a SAVEPOINT and storing the transaction information which we want to roll back (cancel).

Example to understand SAVEPOINT in Oracle

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

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

CREATE TABLE Product
(
 ProductId INT PRIMARY KEY, 
 ProductName VARCHAR2(20), 
 Price INT,
 Quantity INT
);
 
 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 Oracle?

The SAVEPOINT statement in Oracle 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.

How to create a SAVEPOINT in Oracle?

Syntax: SAVEPOINT <pointer name>;

How to rollback a SAVEPOINT?

Syntax: rollback to <pointer 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 Oracle using SAVEPOINT.

Partial Rollback in Oracle using SAVEPOINT:

Let us understand Partial Rollback in Oracle using SAVEPOINT with an example. First, execute the following SQL statements. The following SQL statement will create a transaction with three save points.

SET TRANSACTION READ WRITE;
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 new records with ProductId 1005 and 1006 inserted as shown in the below image.

In the next article, I am going to discuss Sub Queries in Oracle with Examples. Here, in this article, I try to explain how to perform Partial Rollback in Oracle using SAVEPOINT with Examples. I hope you enjoy this SAVEPOINT command in the Oracle article.