Transaction savepoints

This topic describes how to use transaction savepoints.

Syntax

The SAVEPOINT statement marks a savepoint during a transaction, to which you can later roll back. Savepoints are optional. A transaction can have multiple savepoints.

• Syntax for marking a savepoint:

  SAVEPOINT pointname;
  

  Parameter	Required	Description	Example
  pointname	Yes	The name of the savepoint.	fr

• Syntax for rolling back to a specified savepoint:

  ROLLBACK TO SAVEPOINT pointname;
  

  For more information, see Roll back a transaction.

Example: Roll back a transaction to a 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> SELECT * FROM t_insert;
   +----+------+-------+------------+
   | 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> SET session autocommit=off;
   Query OK, 0 rows affected 
   
   obclient> BEGIN;
   Query OK, 0 rows affected 
   
   obclient> INSERT INTO t_insert(id, name) VALUES(6,'FR');
   Query OK, 1 row affected 
   
   obclient> SAVEPOINT fr;
   Query OK, 0 rows affected 
   
   obclient> INSERT INTO t_insert(id, name) VALUES(7,'RU');
   Query OK, 1 row affected 
   
   obclient> SAVEPOINT ru;
   Query OK, 0 rows affected 
   
   obclient> INSERT INTO t_insert(id, name) VALUES(8,'CA');
   Query OK, 1 row affected 
   
   obclient> SAVEPOINT ca;
   Query OK, 0 rows affected
   

3. View the uncommitted changes in the transaction of the current session.

   obclient> SELECT * FROM t_insert;
   +----+------+-------+------------+
   | 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> ROLLBACK TO SAVEPOINT ru;
   Query OK, 0 rows affected 
   
   obclient> SELECT * FROM t_insert;
   +----+------+-------+------------+
   | 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
   

5. Commit the transaction and make sure that the latest changes include those made before the savepoint.

   obclient> COMMIT;
   Query OK, 0 rows affected 
   obclient> SELECT * FROM t_insert;
   +----+------+-------+------------+
   | 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