事务保存点 · k8s原理与实践

# 事务保存点 SAVEPOINT 语句可以在事务过程中标记一个“保存点”，事务可以选择回滚到这个点。保存点是可选的，一个事务过程中也可以有多个保存点。 ## 示例：将一个事务回滚到一个保存点下面示例展示了一个事务中包含多个 DML 语句和多个保存点，然后回滚到其中一个保存点，只丢弃了保存点后面的那部份修改。 * 查看表当前记录 ~~~ obclient> select * from t_insert; +----+------+-------+---------------------+ | id | name | value | gmt_create | +----+------+-------+---------------------+ | 1 | CN | 10001 | 2020-04-03 16:05:45 | | 2 | US | 10002 | 2020-04-03 16:05:54 | | 3 | UK | 10003 | 2020-04-03 16:05:54 | +----+------+-------+---------------------+ 3 rows in set (0.00 sec) ~~~ * 开启一个事务，设置多个保存点信息。 ~~~ obclient> set session autocommit=off; Query OK, 0 rows affected (0.00 sec) obclient> begin; Query OK, 0 rows affected (0.00 sec) obclient> insert into t_insert(id, name) values(6,'FR'); Query OK, 1 row affected (0.00 sec) obclient> savepoint fr; Query OK, 0 rows affected (0.00 sec) obclient> insert into t_insert(id, name) values(7,'RU'); Query OK, 1 row affected (0.00 sec) obclient> savepoint ru; Query OK, 0 rows affected (0.00 sec) obclient> insert into t_insert(id, name) values(8,'CA'); Query OK, 1 row affected (0.00 sec) obclient> savepoint ca; Query OK, 0 rows affected (0.00 sec) ~~~ * 当前会话能看到事务未提交的所有修改。 ~~~ obclient> select * from t_insert; +----+------+-------+---------------------+ | id | name | value | gmt_create | +----+------+-------+---------------------+ | 1 | CN | 10001 | 2020-04-03 16:05:45 | | 2 | US | 10002 | 2020-04-03 16:05:54 | | 3 | UK | 10003 | 2020-04-03 16:05:54 | | 6 | FR | NULL | 2020-04-03 16:26:22 | | 7 | RU | NULL | 2020-04-03 16:26:32 | | 8 | CA | NULL | 2020-04-03 16:26:42 | +----+------+-------+---------------------+ 6 rows in set (0.00 sec) ~~~ * 回滚事务到其中一个保存点。 ~~~ obclient> rollback to savepoint ru; Query OK, 0 rows affected (0.00 sec) obclient> select * from t_insert; +----+------+-------+---------------------+ | id | name | value | gmt_create | +----+------+-------+---------------------+ | 1 | CN | 10001 | 2020-04-03 16:05:45 | | 2 | US | 10002 | 2020-04-03 16:05:54 | | 3 | UK | 10003 | 2020-04-03 16:05:54 | | 6 | FR | NULL | 2020-04-03 16:26:22 | | 7 | RU | NULL | 2020-04-03 16:26:32 | +----+------+-------+---------------------+ 5 rows in set (0.01 sec) ~~~ * 提交事务，确认表最新修改包含保存点之前的修改。 ~~~ obclient> commit; Query OK, 0 rows affected (0.00 sec) obclient> select * from t_insert; +----+------+-------+---------------------+ | id | name | value | gmt_create | +----+------+-------+---------------------+ | 1 | CN | 10001 | 2020-04-03 16:05:45 | | 2 | US | 10002 | 2020-04-03 16:05:54 | | 3 | UK | 10003 | 2020-04-03 16:05:54 | | 6 | FR | NULL | 2020-04-03 16:26:22 | | 7 | RU | NULL | 2020-04-03 16:26:32 | +----+------+-------+---------------------+ 5 rows in set (0.00 sec) obclient> ~~~