回滾事務 · k8s原理與實踐

[TOC] # 回滾事務回滾一個事務指將事務的修改全部撤銷。可以回滾當前整個未提交事務，也可以回滾到事務中任意一個保存點。如果要回滾到某個保存點，必須將`ROLLBACK`和`TO SAVEPOINT`語句結合使用。回滾整個事務： * 事務會結束。 * 所有的修改會被丟棄。 * 清除所有保存點。 * 釋放事務持有的所有鎖。回滾到某個保存點： * 事務不會結束。 * 保存點之前的修改被保留，保存點之后的修改被丟棄。 * 清除保存點之后的保存點（不包括保存點自身）。 * 釋放保存點之后事務持有的所有鎖。 ## 回滾整個事務回滾整個事務的示例如下： * MySQL 模式下，回滾事務的全部修改。 ~~~ obclient> SELECT * FROM t_insert; +----+------+-------+---------------------+ | ID | NAME | VALUE | GMT_CREATE | +----+------+-------+---------------------+ | 1 | CN | 10001 | 2020-04-02 17:52:31 | | 2 | US | 10002 | 2020-04-02 17:52:38 | | 3 | EN | 10003 | 2020-04-02 17:52:38 | +----+------+-------+---------------------+ 3 rows in set (0.00 sec) obclient> BEGIN; Query OK, 0 rows affected (0.00 sec) obclient> INSERT INTO t_insert(id, name, value) VALUES(4,'JP',10004); Query OK, 1 row affected (0.00 sec) obclient> INSERT INTO t_insert(id, name, value) VALUES(5,'FR',10005),(6,'RU',10006); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 obclient> SELECT * FROM t_insert; +----+------+-------+---------------------+ | ID | NAME | VALUE | GMT_CREATE | +----+------+-------+---------------------+ | 1 | CN | 10001 | 2020-04-02 17:52:31 | | 2 | US | 10002 | 2020-04-02 17:52:38 | | 3 | EN | 10003 | 2020-04-02 17:52:38 | | 4 | JP | NULL | 2020-04-02 17:53:34 | | 5 | FR | 10005 | 2020-04-02 17:54:53 | | 6 | RU | 10006 | 2020-04-02 17:54:53 | +----+------+-------+---------------------+ 6 rows in set (0.00 sec) obclient> ROLLBACK; Query OK, 0 rows affected (0.00 sec) obclient> SELECT * FROM t_insert; +----+------+-------+---------------------+ | ID | NAME | VALUE | GMT_CREATE | +----+------+-------+---------------------+ | 1 | CN | 10001 | 2020-04-02 17:52:31 | | 2 | US | 10002 | 2020-04-02 17:52:38 | | 3 | EN | 10003 | 2020-04-02 17:52:38 | +----+------+-------+---------------------+ 3 rows in set (0.00 sec) ~~~ ## 回滾到某個保存點以下示例展示了一個事務中包含多個 DML 語句和多個保存點，當回滾到其中一個保存點后，僅丟棄了保存點后面的那部份修改。 * MySQL 模式 1. 查看表當前記錄。 ~~~ 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) ~~~ 2. 開啟一個事務，設置多個保存點信息。 ~~~ 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) ~~~ 3. 查看當前會話中，事務未提交的所有修改。 ~~~ 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) ~~~ 4. 回滾事務到其中一個保存點。 ~~~ 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) ~~~ 5. 提交事務，確認表最新修改包含保存點之前的修改。 ~~~ 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) ~~~