我的书签
添加书签
移除书签描述
回滚一个事务指将事务的修改全部撤销。可以回滚当前整个未提交事务，也可以回滚到事务中任意一个保存点。如果要回滚到某个保存点，必须将 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: 0obclient> 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)
Oracle 模式下，回滚事务的全部修改。
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> 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: 0obclient> 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 模式
查看表当前记录。
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)
Oracle 模式
查看表当前记录。
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 | DE | NULL | 2020-04-02 17:58:31 |+----+------+-------+---------------------+5 rows in set (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-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 | DE | NULL | 2020-04-02 17:58:31 || 6 | FR | NULL | 2020-04-02 17:58:59 || 7 | RU | NULL | 2020-04-02 17:59:09 || 8 | CA | NULL | 2020-04-02 17:59:19 |+----+------+-------+---------------------+8 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-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 | DE | NULL | 2020-04-02 17:58:31 || 6 | FR | NULL | 2020-04-02 17:58:59 || 7 | RU | NULL | 2020-04-02 17:59:09 |+----+------+-------+---------------------+7 rows in set (0.00 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-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 | DE | NULL | 2020-04-02 17:58:31 || 6 | FR | NULL | 2020-04-02 17:58:59 || 7 | RU | NULL | 2020-04-02 17:59:09 |+----+------+-------+---------------------+7 rows in set (0.00 sec)
