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

Savepoints are marked. For more information, 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. View the uncommitted changes in the transaction of the current session.

   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') is inserted after the savepoint ru. Therefore, this change is discarded after the rollback.

5. Commit the transaction and make sure that the latest 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 information about transaction rollback, see Roll back a transaction.