OceanBase Cloud快速入门第85期：如何释放保存点?

欢迎访问 OceanBase 官网获取更多信息：https://www.oceanbase.com/

标记保存点后，如果不再需要某个保存点，也可以释放保存点。

前提条件

请确认已标记保存点，具体操作请参见 标记保存点。

释放保存点

可以使用以下语句，释放保存点。

RELEASE SAVEPOINT pointname;

以下示例展示了释放回滚点 ru 后，被释放的保存点以及 RELEASE SAVEPOINT 语句与该保存点之间的任何其他保存点均不再能回滚。

1. 开启一个事务，并标记多个保存点信息。

   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. 查看表中的数据。

   事务未提交前，可以看到事务中未提交的所有数据。

   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. 释放保存点 ru。

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

4. 分别回滚到保存 ru 和 ca，发现保存点均不存在。

   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

欢迎访问 OceanBase 官网获取更多信息：https://www.oceanbase.com/