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 releasing a savepoint:

  Notice

  After the RELEASE SAVEPOINT statement is successfully executed, you cannot roll back the transaction to the released savepoint, or any other savepoints between this statement and this savepoint.

  RELEASE SAVEPOINT pointname;
  

• 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  1970-01-01 16:05:45 
     2  US    10002  1970-01-01 16:05:54 
     3  UK    10003  1970-01-01 16:05:54 
   +----+------+-------+---------------------+
   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  1970-01-01 16:05:45 
     2  US    10002  1970-01-01 16:05:54 
     3  UK    10003  1970-01-01 16:05:54 
     6  FR     NULL  1970-01-01 16:26:22 
     7  RU     NULL  1970-01-01 16:26:32 
     8  CA     NULL  1970-01-01 16:26:42 
   +----+------+-------+---------------------+
   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  1970-01-01 16:05:45 
     2  US    10002  1970-01-01 16:05:54 
     3  UK    10003  1970-01-01 16:05:54 
     6  FR     NULL  1970-01-01 16:26:22 
     7  RU     NULL  1970-01-01 16:26:32 
   +----+------+-------+---------------------+
   5 rows in set 
   

5. Commit the transaction and make sure 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  1970-01-01 16:05:45 
     2  US    10002  1970-01-01 16:05:54 
     3  UK    10003  1970-01-01 16:05:54 
     6  FR     NULL  1970-01-01 16:26:22 
     7  RU     NULL  1970-01-01 16:26:32 
   +----+------+-------+---------------------+
   5 rows in set