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
Ensure that a savepoint has been 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 specific savepoint:
ROLLBACK TO SAVEPOINT pointname;
Here, pointname is the name of the transaction savepoint.
The following example contains multiple DML statements and savepoints. After the transaction is rolled back to a specific savepoint, only the modifications made after that savepoint are discarded.
Query the current record in the table.
obclient [test]> SELECT * FROM ordr; +----+------+-------+---------------------+ | 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 setStart a transaction and set multiple savepoints.
obclient [test]> SET SESSION autocommit=0; Query OK, 0 rows affected obclient [test]> BEGIN; Query OK, 0 rows affected obclient [test]> INSERT INTO ordr(id, name) VALUES(6,'FR'); Query OK, 1 row affected obclient [test]> SAVEPOINT fr; Query OK, 0 rows affected obclient [test]> INSERT INTO ordr(id, name) VALUES(7,'RU'); Query OK, 1 row affected obclient [test]> SAVEPOINT ru; Query OK, 0 rows affected obclient [test]> INSERT INTO ordr(id, name) VALUES(8,'CA'); Query OK, 1 row affected obclient [test]> SAVEPOINT ca; Query OK, 0 rows affectedView the uncommitted modifications in the transaction of the current session.
obclient [test]> SELECT * FROM ordr; +----+------+-------+---------------------+ | 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 setRoll back the transaction to one of the savepoints,
ru.obclient [test]> ROLLBACK TO SAVEPOINT ru; Query OK, 0 rows affected obclient [test]> SELECT * FROM ordr; +----+------+-------+---------------------+ | 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 setThe query result shows that since
(8,'CA')was inserted after savepointru, this modification is discarded after the rollback.Commit the transaction and confirm that the latest modifications to the table include those made before savepoint
ru.obclient [test]> COMMIT; Query OK, 0 rows affected obclient [test]> SELECT * FROM ordr; +----+------+-------+---------------------+ | 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 examples and descriptions of rolling back transactions, see Roll back a transaction.