南大通用GBase8s 的事务保存点介绍

今天我们来说说事务保存点的那点事儿。事务是数据库操作的一个单元，要么全部成功，要么全部失败。保存点简单来说是在这个单元内部设置的一个标记，允许回滚到某个点而不是整个事务。比如，在一个复杂的事务里，可能有很多步骤，如果中间某一步出错，可能不需要回滚整个事务，而是回到某个保存点，这样之前的部分操作还能保留。这样可以更灵活地处理错误。以下是对保存点的详细介绍。

定义

保存点是事务内部的“检查点”，用于标记事务执行过程中的某个状态。通过回滚到保存点，可以撤销部分操作，而非整个事务。

作用

• 部分回滚：当事务中的部分操作失败时，仅回滚到保存点，保留之前的操作结果。
• 提高灵活性：在复杂事务中分段处理逻辑，增强错误恢复能力。
• 嵌套控制：支持多个保存点的嵌套设置，实现更细粒度的控制。

保存点操作相关语法

• 设置保存点：

SAVEPOINT savepoint_name [UNIQUE];

后面例子有涉及，这里先不举例。

• 回滚到保存点：

ROLLBACK TO SAVEPOINT savepoint_name;

后面例子有涉及，这里先不举例。 

• 释放保存点

RELEASE SAVEPOINT savepoint_name;

这个命令用于删除创建的保存点。在某个保存点被释放之后，就不能再利用rollback命令来撤销这个保存点之后的事务操作了。利用这个命令可以避免意外地回退到某个不再需要的保存点。

> create database db0 with log;
Database created.
> Create table t0(id int,c1 varchar(10));
Table created.
--开启事务
> Begin work;
Started transaction.
> insert into t0 values(1,'name1');
1 row(s) inserted.
> Savepoint  sp1;
Savepoint set.
> insert into t0 values(2,'name2');
1 row(s) inserted.
> Savepoint sp2;
Savepoint set.
> insert into t0 values(3,'name3');
1 row(s) inserted.
> Savepoint  sp3;
Savepoint set.
> select * from t0;

        id c1
         1 name1
         2 name2
         3 name3
3 row(s) retrieved.
> RELEASE SAVEPOINT sp2;
Transaction rolled back to savepoint.

> rollback to SAVEPOINT sp2;
26076: Unable to rollback to savepoint sp2.
32026: Savepoint not found.
Error in line 1
Near character position 24
    
> rollback to SAVEPOINT sp3;
26076: Unable to rollback to savepoint sp3.
32026: Savepoint not found.
Error in line 1
Near character position 25
Commit;

关键特性

• 嵌套支持

可设置多个保存点，形成层级结构。例如：

SAVEPOINT sp1;
-- 操作1
SAVEPOINT sp2;
-- 操作2
ROLLBACK TO sp1; -- 回滚到sp1时，sp2自动失效   

举例：

> Create database db1 with log;
Database created.
> Create table t1(id int,c1 varchar(10));
Table created.
--开启事务
> Begin work;
Started transaction.
> insert into t1 values(1,'name1');
1 row(s) inserted.
> Savepoint  sp1;
Savepoint set.

--查看事务
onstat -x |grep $(onstat -g ses $(onstat -g sql|grep db1 |cut -d ' ' -f1)|grep rstcb -A 1 | awk 'NR==2 {print $3}')
45b785b8         A-B-- 45b36da8         4      18:0x34f018       18:0x34f0bc       COMMIT  00:00:00 0

> update t1 set c1='rose';
1 row(s) updated.
> Savepoint sp2;
Savepoint set.

onstat -x |grep $(onstat -g ses $(onstat -g sql|grep db1 |cut -d ' ' -f1)|grep rstcb -A 1 | awk 'NR==2 {print $3}')
45b785b8         A-B-- 45b36da8         5      18:0x34f018       18:0x376064   
   COMMIT  00:00:00 0

> insert into t1 values(2,'name2');
1 row(s) inserted.
> Savepoint  sp3;
Savepoint set.

onstat -x |grep $(onstat -g ses $(onstat -g sql|grep db1 |cut -d ' ' -f1)|grep rstcb -A 1 | awk 'NR==2 {print $3}')
45b785b8         A-B-- 45b36da8         6      18:0x34f018       18:0x3760b0       COMMIT  00:00:00 0

> insert into t1 values(3,'name3');
1 row(s) inserted.
> Savepoint  sp4;
Savepoint set.

onstat -x |grep $(onstat -g ses $(onstat -g sql|grep db1 |cut -d ' ' -f1)|grep rstcb -A 1 | awk 'NR==2 {print $3}')
45b785b8         A-B-- 45b36da8         7      18:0x34f018       18:0x3760fc       COMMIT  00:00:00 0

> Rollback to savepoint  sp2;
Transaction rolled back to savepoint.

--事务日志将回滚到sp2时日志位置，sp2时，current logpos为18:0x376064，回滚后查看，current logpos为18:0x376064
onstat -x |grep $(onstat -g ses $(onstat -g sql|grep db1 |cut -d ' ' -f1)|grep rstcb -A 1 | awk 'NR==2 {print $3}')
45b785b8         A-B-- 45b36da8         7      18:0x34f018       18:0x376064       COMMIT  00:00:00 0

--回滚 sp2时，sp3， sp4自动失效
> Rollback to savepoint sp3;
26076: Unable to rollback to savepoint sp3.
32026: Savepoint not found.
Error in line 1
Near character position 22
Commit;

• 名称覆盖

如果保存点后面未加UNIQUE时，同一事务中，允许同名保存点，同名保存点会被覆盖，旧保存点失效。

举例：

> Create table t2(id int,c1 varchar(10));
Table created.
--开启事务
> Begin work;
Started transaction.
> insert into t2 values(1,'name1');
1 row(s) inserted.
> Savepoint  sp1;
Savepoint set.
> insert into t2 values(2,'name2');
1 row(s) inserted.
> Savepoint sp2;
Savepoint set.
> insert into t2 values(3,'name3');
1 row(s) inserted.
> Savepoint  sp2;
Savepoint set.
> select * from t2;

        id c1
         1 name1
         2 name2
         3 name3
3 row(s) retrieved.
> Rollback to savepoint  sp2;
Transaction rolled back to savepoint.
> select * from t2;

        id c1
         1 name1
         2 name2
         3 name3
3 row(s) retrieved.
第一个sp2保存点失效。
Commit;

保存点后加UNIQUE的举例：

> Begin work;
Started transaction.
> insert into t2 values(1,'name1');
1 row(s) inserted.
>  Savepoint  sp1 UNIQUE;
Savepoint set.
> insert into t2 values(2,'name2');
1 row(s) inserted.
> Savepoint sp1;
26074: Unable to set savepoint sp1.
32027: A savepoint with the same name exists and the UNIQUE option was specified.
Error in line 1
Near character position 12

• 事务边界

保存点仅在同一事务内有效，提交或回滚事务后，所有保存点自动释放。举例：
 

> Create table t3(id int,c1 varchar(10));
Table created.
--开启事务
> Begin work;
Started transaction.
> insert into t3 values(1,'name1');
1 row(s) inserted.
> Savepoint  sp1;
Savepoint set.
> insert into t3 values(2,'name2');
1 row(s) inserted.
> Savepoint sp2;
Savepoint set.
commit;
> begin work;
Started transaction.

> rollback to savepoint sp1;
26076: Unable to rollback to savepoint sp1.
32026: Savepoint not found.

保存点是事务管理的重要工具，通过允许部分回滚提高了复杂事务的健壮性。使用时需结合业务场景，合理设计保存点位置及错误处理逻辑，同时关注数据库实现细节和性能影响。后续将进一步研究保存点对性能的影响。