After marking a savepoint, you can release it if it is no longer needed.
Prerequisites
Ensure that a savepoint has been marked. For more information, see Mark a savepoint.
Release a savepoint
You can use the following statement to release a savepoint.
RELEASE SAVEPOINT pointname;
Notice
After the RELEASE SAVEPOINT statement is executed, you cannot roll back the transaction to the released savepoint or to any other savepoints between this savepoint and the RELEASE SAVEPOINT statement.
The following example shows that after the savepoint ru is released, you cannot roll back to the ru savepoint or to any other savepoints between the RELEASE SAVEPOINT statement and the ru savepoint.
Start a transaction and mark multiple savepoints.
obclient [test]> SET SESSION autocommit=0; Query OK, 0 rows affected obclient [test]> BEGIN; 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 | 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 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 affectedQuery data from the table.
Before committing the transaction, you can view all uncommitted data within the transaction.
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 setRelease the savepoint
ru.obclient [test]> RELEASE SAVEPOINT ru; Query OK, 0 rows affectedTry to roll back to the savepoints
ruandca. The system returns an error indicating that the savepoints do not exist.obclient [test]> rollback to savepoint ru; ERROR 1305 (42000): savepoint does not exist obclient [test]> rollback to savepoint ca; ERROR 1305 (42000): savepoint does not exist