Roll back a transaction to a savepoint

After you mark savepoints for a transaction, you can use the ROLLBACK TO SAVEPOINT statement to roll back the transaction to a specified savepoint.

Prerequisites

Please confirm that you have marked savepoints. For specific operations, see Mark a savepoint.

Roll back a transaction to a savepoint

You can use the following statement to roll back a transaction to a savepoint:

ROLLBACK TO SAVEPOINT pointname;

Here, pointname specifies the name of the transaction savepoint.

The transaction in the following example contains several DML statements and several savepoints. After the transaction is rolled back to a specific savepoint, only the changes after this savepoint are discarded.

1. View the records in the table.

   obclient [SYS]> SELECT * FROM ordr;
   +----+------+-------+------------+
   | ID | NAME | VALUE | GMT_CREATE |
   +----+------+-------+------------+
   |  1 | CN   | 10001 | 01-JAN-70  |
   |  2 | US   | 10002 | 01-JAN-70  |
   |  3 | EN   | 10003 | 01-JAN-70  |
   +----+------+-------+------------+
   3 rows in set
   

2. Start a transaction and set several savepoints.

   obclient [SYS]> SET session autocommit=off;
   Query OK, 0 rows affected 
   
   obclient [SYS]> BEGIN;
   Query OK, 0 rows affected 
   
   obclient [SYS]> INSERT INTO ordr(id, name) VALUES(6,'FR');
   Query OK, 1 row affected 
   
   obclient [SYS]> SAVEPOINT fr;
   Query OK, 0 rows affected 
   
   obclient [SYS]> INSERT INTO ordr(id, name) VALUES(7,'RU');
   Query OK, 1 row affected 
   
   obclient [SYS]> SAVEPOINT ru;
   Query OK, 0 rows affected 
   
   obclient [SYS]> INSERT INTO ordr(id, name) VALUES(8,'CA');
   Query OK, 1 row affected 
   
   obclient [SYS]> SAVEPOINT ca;
   Query OK, 0 rows affected
   

3. The current session can see all uncommitted changes in the transaction.

   obclient [SYS]> SELECT * FROM ordr;
   +----+------+-------+------------+
   | ID | NAME | VALUE | GMT_CREATE |
   +----+------+-------+------------+
   |  1 | CN   | 10001 | 01-JAN-70  |
   |  2 | US   | 10002 | 01-JAN-70  |
   |  3 | EN   | 10003 | 01-JAN-70  |
   |  6 | FR   |  NULL | 01-JAN-70  |
   |  7 | RU   |  NULL | 01-JAN-70  |
   |  8 | CA   |  NULL | 01-JAN-70  |
   +----+------+-------+------------+
   6 rows in set
   

4. Roll back the transaction to one of its savepoints.

   obclient [SYS]> ROLLBACK TO SAVEPOINT ru;
   Query OK, 0 rows affected 
   
   obclient [SYS]> SELECT * FROM ordr;
   +----+------+-------+------------+
   | ID | NAME | VALUE | GMT_CREATE |
   +----+------+-------+------------+
   |  1 | CN   | 10001 | 01-JAN-70  |
   |  2 | US   | 10002 | 01-JAN-70  |
   |  3 | EN   | 10003 | 01-JAN-70  |
   |  6 | FR   |  NULL | 01-JAN-70  |
   |  7 | RU   |  NULL | 01-JAN-70  |
   +----+------+-------+------------+
   5 rows in set
   

   The query result shows that the record (8,'CA') was inserted after the savepoint ru. Therefore, this change is discarded after the rollback.

5. Commit the transaction and confirm that the latest table changes include those made before the savepoint.

   obclient [SYS]> COMMIT;
   Query OK, 0 rows affected 
   
   obclient [SYS]> SELECT * FROM ordr;
   +----+------+-------+------------+
   | ID | NAME | VALUE | GMT_CREATE |
   +----+------+-------+------------+
   |  1 | CN   | 10001 | 01-JAN-70  |
   |  2 | US   | 10002 | 01-JAN-70  |
   |  3 | EN   | 10003 | 01-JAN-70  |
   |  6 | FR   |  NULL | 01-JAN-70  |
   |  7 | RU   |  NULL | 01-JAN-70  |
   +----+------+-------+------------+
   5 rows in set
   

For more examples and descriptions of transaction rollback, see Roll back a transaction.