Transaction savepoints

This topic describes how to use transaction savepoints.

Syntax

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

• Syntax for marking a savepoint:

  SAVEPOINT pointname;
  

  Parameter	Required	Description	Examples
  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 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 | 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