如果回滚整个事务：

  • 事务会结束。

  • 所有的修改会被丢弃。

  • 清除所有保存点。

  • 释放事务持有的所有锁。

如果回滚到某个保存点：

  • 事务不会结束。

  • 保存点之前的修改被保留，保存点之后的修改被丢弃。

  • 释放保存点之后事务持有的所有锁。

回滚整个事务的示例如下：

  • MySQL 模式下，回滚事务的全部修改。

  • Oracle 模式下，回滚事务的全部修改。

    1. obclient> SELECT * FROM t_insert;
    2. +----+------+-------+---------------------+
    3. | ID | NAME | VALUE | GMT_CREATE          |
    4. +----+------+-------+---------------------+
    5. |  1 | CN   | 10001 | 2020-04-02 17:52:31 |
    6. |  2 | US   | 10002 | 2020-04-02 17:52:38 |
    7. |  3 | EN   | 10003 | 2020-04-02 17:52:38 |
    8. +----+------+-------+---------------------+
    9. 3 rows in set (0.00 sec)
    11. obclient> INSERT INTO t_insert(id, name, value) VALUES(4,'JP',10004);
    12. Query OK, 1 row affected (0.00 sec)
    14. obclient> INSERT INTO t_insert(id, name, value) VALUES(5,'FR',10005),(6,'RU',10006);
    15. Query OK, 2 rows affected (0.00 sec)
    16. Records: 2  Duplicates: 0  Warnings: 0
    18. obclient> SELECT * FROM t_insert;
    19. +----+------+-------+---------------------+
    20. | ID | NAME | VALUE | GMT_CREATE          |
    21. +----+------+-------+---------------------+
    22. |  1 | CN   | 10001 | 2020-04-02 17:52:31 |
    23. |  2 | US   | 10002 | 2020-04-02 17:52:38 |
    24. |  3 | EN   | 10003 | 2020-04-02 17:52:38 |
    25. |  4 | JP   |  NULL | 2020-04-02 17:53:34 |
    26. |  5 | FR   | 10005 | 2020-04-02 17:54:53 |
    27. |  6 | RU   | 10006 | 2020-04-02 17:54:53 |
    28. +----+------+-------+---------------------+
    29. 6 rows in set (0.00 sec)
    31. obclient> ROLLBACK;
    32. Query OK, 0 rows affected (0.00 sec)
    34. obclient> SELECT * FROM t_insert;
    35. +----+------+-------+---------------------+
    36. | ID | NAME | VALUE | GMT_CREATE          |
    37. +----+------+-------+---------------------+
    38. |  1 | CN   | 10001 | 2020-04-02 17:52:31 |
    39. |  2 | US   | 10002 | 2020-04-02 17:52:38 |
    40. |  3 | EN   | 10003 | 2020-04-02 17:52:38 |
    41. +----+------+-------+---------------------+
    42. 3 rows in set (0.00 sec)

以下示例展示了一个事务中包含多个 DML 语句和多个保存点，当回滚到其中一个保存点后，仅丢弃了保存点后面的那部分修改。

  • MySQL 模式

    1. 查看表当前记录。

      1. obclient> SELECT * FROM t_insert;
      2. +----+------+-------+---------------------+
      3. | id | name | value | gmt_create          |
      4. +----+------+-------+---------------------+
      5. |  2 | US   | 10002 | 2020-04-03 16:05:54 |
      6. |  3 | UK   | 10003 | 2020-04-03 16:05:54 |
      7. +----+------+-------+---------------------+
      8. 3 rows in set (0.00 sec)

    2. 开启一个事务，设置多个保存点信息。

      2. Query OK, 0 rows affected (0.00 sec)
      4. obclient> BEGIN;
      5. Query OK, 0 rows affected (0.00 sec)
      7. obclient> INSERT INTO t_insert(id, name) VALUES(6,'FR');
      8. Query OK, 1 row affected (0.00 sec)
      10. obclient> SAVEPOINT fr;
      11. Query OK, 0 rows affected (0.00 sec)
      13. obclient> INSERT INTO t_insert(id, name) VALUES(7,'RU');
      14. Query OK, 1 row affected (0.00 sec)
      16. obclient> SAVEPOINT ru;
      17. Query OK, 0 rows affected (0.00 sec)
      19. obclient> INSERT INTO t_insert(id, name) VALUES(8,'CA');
      20. Query OK, 1 row affected (0.00 sec)
      22. obclient> SAVEPOINT ca;
      23. Query OK, 0 rows affected (0.00 sec)

    4. 回滚事务到其中一个保存点。

      1. obclient> ROLLBACK TO SAVEPOINT ru;
      2. Query OK, 0 rows affected (0.00 sec)
      4. obclient> SELECT * FROM t_insert;
      5. +----+------+-------+---------------------+
      6. | id | name | value | gmt_create          |
      7. +----+------+-------+---------------------+
      8. |  1 | CN   | 10001 | 2020-04-03 16:05:45 |
      9. |  2 | US   | 10002 | 2020-04-03 16:05:54 |
      10. |  3 | UK   | 10003 | 2020-04-03 16:05:54 |
      11. |  6 | FR   |  NULL | 2020-04-03 16:26:22 |
      12. |  7 | RU   |  NULL | 2020-04-03 16:26:32 |
      13. +----+------+-------+---------------------+
      14. 5 rows in set (0.01 sec)

    5. 提交事务，确认表最新修改包含保存点之前的修改。

      1. obclient> COMMIT;
      2. Query OK, 0 rows affected (0.00 sec)
      4. obclient> SELECT * FROM t_insert;
      5. +----+------+-------+---------------------+
      6. | id | name | value | gmt_create          |
      7. +----+------+-------+---------------------+
      8. |  1 | CN   | 10001 | 2020-04-03 16:05:45 |
      9. |  2 | US   | 10002 | 2020-04-03 16:05:54 |
      10. |  3 | UK   | 10003 | 2020-04-03 16:05:54 |
      11. |  6 | FR   |  NULL | 2020-04-03 16:26:22 |
      12. |  7 | RU   |  NULL | 2020-04-03 16:26:32 |
      13. +----+------+-------+---------------------+
      14. 5 rows in set (0.00 sec)

  • Oracle 模式

    1. 开启一个事务，设置多个保存点信息。

    2. 当前会话中能看到事务未提交的所有修改。

      1. obclient> SELECT * FROM t_insert;
      2. +----+------+-------+---------------------+
      3. | ID | NAME | VALUE | GMT_CREATE          |
      4. +----+------+-------+---------------------+
      5. |  1 | CN   | 10001 | 2020-04-02 17:52:31 |
      6. |  2 | US   | 10002 | 2020-04-02 17:52:38 |
      7. |  3 | EN   | 10003 | 2020-04-02 17:52:38 |
      8. |  4 | JP   |  NULL | 2020-04-02 17:53:34 |
      9. |  5 | DE   |  NULL | 2020-04-02 17:58:31 |
      10. |  6 | FR   |  NULL | 2020-04-02 17:58:59 |
      11. |  7 | RU   |  NULL | 2020-04-02 17:59:09 |
      12. |  8 | CA   |  NULL | 2020-04-02 17:59:19 |
      13. +----+------+-------+---------------------+
      14. 8 rows in set (0.00 sec)

    3. 回滚事务到其中一个保存点。

      1. obclient> ROLLBACK TO SAVEPOINT ru;
      2. Query OK, 0 rows affected (0.00 sec)
      4. obclient> SELECT * FROM t_insert;
      5. +----+------+-------+---------------------+
      6. | ID | NAME | VALUE | GMT_CREATE          |
      7. +----+------+-------+---------------------+
      8. |  1 | CN   | 10001 | 2020-04-02 17:52:31 |
      9. |  2 | US   | 10002 | 2020-04-02 17:52:38 |
      10. |  3 | EN   | 10003 | 2020-04-02 17:52:38 |
      11. |  4 | JP   |  NULL | 2020-04-02 17:53:34 |
      12. |  5 | DE   |  NULL | 2020-04-02 17:58:31 |
      13. |  6 | FR   |  NULL | 2020-04-02 17:58:59 |
      14. |  7 | RU   |  NULL | 2020-04-02 17:59:09 |
      15. +----+------+-------+---------------------+
      16. 7 rows in set (0.00 sec)

    4. 提交事务，确认表最新修改包含保存点之前的修改。

      1. obclient> COMMIT;
      2. Query OK, 0 rows affected (0.00 sec)
      4. obclient> SELECT * FROM t_insert;
      5. +----+------+-------+---------------------+
      6. | ID | NAME | VALUE | GMT_CREATE          |
      7. +----+------+-------+---------------------+
      8. |  1 | CN   | 10001 | 2020-04-02 17:52:31 |
      9. |  2 | US   | 10002 | 2020-04-02 17:52:38 |
      10. |  3 | EN   | 10003 | 2020-04-02 17:52:38 |
      11. |  4 | JP   |  NULL | 2020-04-02 17:53:34 |
      12. |  5 | DE   |  NULL | 2020-04-02 17:58:31 |
      13. |  6 | FR   |  NULL | 2020-04-02 17:58:59 |
      14. |  7 | RU   |  NULL | 2020-04-02 17:59:09 |