SAVEPOINT 语句可以在事务过程中标记一个“保存点”,事务可以选择回滚到这个点。保存点是可选的,一个事务过程中也可以有多个保存点。

示例:将一个事务回滚到一个保存点

下面示例展示了一个事务中包含多个 DML 语句和多个保存点,然后回滚到其中一个保存点,只丢弃了保存点后面的那部份修改。

  • 查看表当前记录
  1. obclient> select * from t_insert;
  2. +----+------+-------+---------------------+
  3. | id | name | value | gmt_create |
  4. +----+------+-------+---------------------+
  5. | 1 | CN | 10001 | 2020-04-03 16:05:45 |
  6. | 2 | US | 10002 | 2020-04-03 16:05:54 |
  7. | 3 | UK | 10003 | 2020-04-03 16:05:54 |
  8. | 4 | JP | NULL | 2020-04-03 16:16:46 |
  9. | 5 | DE | NULL | 2020-04-03 16:16:46 |
  10. +----+------+-------+---------------------+
  11. 5 rows in set (0.00 sec)
  • 开启一个事务,设置多个保存点信息。
  1. obclient> set session autocommit=off;
  2. Query OK, 0 rows affected (0.00 sec)
  3. obclient> insert into t_insert(id, name) values(6,'FR');
  4. Query OK, 1 row affected (0.00 sec)
  5. obclient> savepoint fr;
  6. Query OK, 0 rows affected (0.00 sec)
  7. obclient> insert into t_insert(id, name) values(7,'RU');
  8. Query OK, 1 row affected (0.00 sec)
  9. obclient> savepoint ru;
  10. Query OK, 0 rows affected (0.00 sec)
  11. obclient> insert into t_insert(id, name) values(8,'CA');
  12. Query OK, 1 row affected (0.00 sec)
  13. obclient> savepoint ca;
  14. Query OK, 0 rows affected (0.00 sec)
  • 当前会话能看到事务未提交的所有修改。
  1. obclient> select * from t_insert;
  2. +----+------+-------+---------------------+
  3. | id | name | value | gmt_create |
  4. +----+------+-------+---------------------+
  5. | 1 | CN | 10001 | 2020-04-03 16:05:45 |
  6. | 2 | US | 10002 | 2020-04-03 16:05:54 |
  7. | 3 | UK | 10003 | 2020-04-03 16:05:54 |
  8. | 4 | JP | NULL | 2020-04-03 16:16:46 |
  9. | 5 | DE | NULL | 2020-04-03 16:16:46 |
  10. | 6 | FR | NULL | 2020-04-03 16:26:22 |
  11. | 7 | RU | NULL | 2020-04-03 16:26:32 |
  12. | 8 | CA | NULL | 2020-04-03 16:26:42 |
  13. +----+------+-------+---------------------+
  14. 8 rows in set (0.00 sec)
  • 回滚事务到其中一个保存点。
  1. obclient> rollback to savepoint ru;
  2. Query OK, 0 rows affected (0.00 sec)
  3. obclient> select * from t_insert;
  4. +----+------+-------+---------------------+
  5. | id | name | value | gmt_create |
  6. +----+------+-------+---------------------+
  7. | 1 | CN | 10001 | 2020-04-03 16:05:45 |
  8. | 2 | US | 10002 | 2020-04-03 16:05:54 |
  9. | 3 | UK | 10003 | 2020-04-03 16:05:54 |
  10. | 4 | JP | NULL | 2020-04-03 16:16:46 |
  11. | 5 | DE | NULL | 2020-04-03 16:16:46 |
  12. | 6 | FR | NULL | 2020-04-03 16:26:22 |
  13. | 7 | RU | NULL | 2020-04-03 16:26:32 |
  14. +----+------+-------+---------------------+
  15. 7 rows in set (0.01 sec)
  • 提交事务,确认表最新修改包含保存点之前的修改。
  1. obclient> commit;
  2. Query OK, 0 rows affected (0.00 sec)
  3. obclient> select * from t_insert;
  4. +----+------+-------+---------------------+
  5. | id | name | value | gmt_create |
  6. +----+------+-------+---------------------+
  7. | 1 | CN | 10001 | 2020-04-03 16:05:45 |
  8. | 2 | US | 10002 | 2020-04-03 16:05:54 |
  9. | 3 | UK | 10003 | 2020-04-03 16:05:54 |
  10. | 4 | JP | NULL | 2020-04-03 16:16:46 |
  11. | 5 | DE | NULL | 2020-04-03 16:16:46 |
  12. | 6 | FR | NULL | 2020-04-03 16:26:22 |
  13. | 7 | RU | NULL | 2020-04-03 16:26:32 |
  14. +----+------+-------+---------------------+
  15. 7 rows in set (0.00 sec)
  16. obclient>