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> SELECT * FROM ordr;
   

   The query result is as follows:

   +----+------+-------+---------------------+
   | id | name | value | gmt_create          |
   +----+------+-------+---------------------+
   |  1 | CN   | 10001 | 2022-10-19 14:51:12 |
   |  2 | US   | 10002 | 2022-10-19 14:51:12 |
   |  3 | EN   | 10003 | 2022-10-19 14:51:12 |
   |  4 | JP   |  NULL | 2022-10-19 14:51:44 |
   |  5 | CN   |  NULL | 2022-10-19 14:53:56 |
   +----+------+-------+---------------------+
   5 rows in set
   

2. Start a transaction and set several savepoints.

   obclient> SET SESSION autocommit=0;
   obclient> BEGIN;
   obclient> INSERT INTO ordr(id, name) VALUES(6,'FR');
   obclient> SAVEPOINT fr;
   obclient> INSERT INTO ordr(id, name) VALUES(7,'RU');
   obclient> SAVEPOINT ru;
   obclient> INSERT INTO ordr(id, name) VALUES(8,'CA');
   obclient> SAVEPOINT ca;
   

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

   obclient> SELECT * FROM ordr;
   

   The query result is as follows:

   +----+------+-------+---------------------+
   | id | name | value | gmt_create          |
   +----+------+-------+---------------------+
   |  1 | CN   | 10001 | 2022-10-19 14:51:12 |
   |  2 | US   | 10002 | 2022-10-19 14:51:12 |
   |  3 | EN   |  1003 | 2022-10-19 14:51:12 |
   |  4 | JP   |  NULL | 2022-10-19 14:51:44 |
   |  5 | CN   |  NULL | 2022-10-19 14:53:56 |
   |  6 | FR   |  NULL | 2022-10-19 15:01:39 |
   |  7 | RU   |  NULL | 2022-10-19 15:01:50 |
   |  8 | CA   |  NULL | 2022-10-19 15:02:15 |
   +----+------+-------+---------------------+
   8 rows in set
   

4. Roll back the transaction to the savepoint ru.

   obclient> ROLLBACK TO SAVEPOINT ru;
   obclient> SELECT * FROM ordr;
   

   The query result is as follows:

   +----+------+-------+---------------------+
   | id | name | value | gmt_create          |
   +----+------+-------+---------------------+
   |  1 | CN   | 10001 | 2022-10-19 14:51:12 |
   |  2 | US   | 10002 | 2022-10-19 14:51:12 |
   |  3 | EN   |  1003 | 2022-10-19 14:51:12 |
   |  4 | JP   |  NULL | 2022-10-19 14:51:44 |
   |  5 | CN   |  NULL | 2022-10-19 14:53:56 |
   |  6 | FR   |  NULL | 2022-10-19 15:01:39 |
   |  7 | RU   |  NULL | 2022-10-19 15:01:50 |
   +----+------+-------+---------------------+
   7 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 of the table include those made before the savepoint ru.

   obclient> COMMIT;
   obclient> SELECT * FROM ordr;
   

   The query result is as follows:

   +----+------+-------+---------------------+
   | id | name | value | gmt_create          |
   +----+------+-------+---------------------+
   |  1 | CN   | 10001 | 2022-10-19 14:51:12 |
   |  2 | US   | 10002 | 2022-10-19 14:51:12 |
   |  3 | EN   |  1003 | 2022-10-19 14:51:12 |
   |  4 | JP   |  NULL | 2022-10-19 14:51:44 |
   |  5 | CN   |  NULL | 2022-10-19 14:53:56 |
   |  6 | FR   |  NULL | 2022-10-19 15:01:39 |
   |  7 | RU   |  NULL | 2022-10-19 15:01:50 |
   +----+------+-------+---------------------+
   7 rows in set
   
   

For more information about transaction rollback, see Roll back a transaction.

References

• Mark a savepoint

• Release a savepoint