Release a savepoint

You can release a savepoint that is no longer needed.

Prerequisites

Savepoints are marked. For more information, see Mark a savepoint.

Release a savepoint

Execute the following statement to release a savepoint:

RELEASE SAVEPOINT pointname;

In the following example, after the ru savepoint is released, you cannot roll back the transaction to the ru savepoint or any other savepoints between the RELEASE SAVEPOINT statement and the ru savepoint.

1. Start a transaction and mark several 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 affected
   

2. View the data in the table.

   Before you commit the transaction, you can view all uncommitted data in 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 set
   

3. Release the ru savepoint.

   obclient [test]> RELEASE SAVEPOINT ru;
   Query OK, 0 rows affected
   

4. Try to roll back to the ru and ca savepoints. 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