MySQL中的事务控制语句

MySQL中的事务控制语句有START TRANSACTION、COMMIT、ROLLBACK、SET autocommit，它们协作起来实现对事务的管控。

• START TRANSACTION或BEGIN开启新事务。
• COMMIT提交当前事务，使更改持久生效。
• ROLLBACK回滚当前事务，取消更改。
• SET autocommit禁用或启用当前会话的默认自动提交模式。

MySQL 5.7中的语法如下：

START TRANSACTION
    [transaction_characteristic [, transaction_characteristic] ...]

transaction_characteristic: {
    WITH CONSISTENT SNAPSHOT
  | READ WRITE
  | READ ONLY
}

BEGIN [WORK]
COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
SET autocommit = {0 | 1}
复制

默认情况下，MySQL启用自动提交模式。这意味着，如果不是在事务内部，则每个语句都是原子的，就像它被START transaction和COMMIT包围一样。不能使用ROLLBACK撤消，除非语句执行期间发生错误自动回滚。

[root@database-one ~]# mysql -uroot -h127.0.0.1 -P3306 -p
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 338605
Server version: 5.7.16-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

root@database-one 00:35:  [(none)]> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

root@database-one 00:35:  [(none)]> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.01 sec)

root@database-one 00:35:  [(none)]> use gftest;
Database changed
root@database-one 00:37:  [gftest]> select * from emp;
+--------+------+---------+------------+--------+
| ename  | age  | sal     | hiredate   | deptno |
+--------+------+---------+------------+--------+
| 郭军   |   27 | 8400.00 | 2019-12-08 |     10 |
| 刘杰   |   30 | 9100.00 | 2018-04-09 |     10 |
| 王艳   |   24 | 6000.00 | 2020-01-05 |     20 |
| 马丽   |   26 | 7200.00 | 2018-07-06 |     30 |
| 肖伟   |   29 | 8700.00 | 2017-05-28 |     30 |
| 陈实   |   31 | 9000.00 | 2019-07-01 |     10 |
+--------+------+---------+------------+--------+
6 rows in set (0.01 sec)

root@database-one 00:37:  [gftest]> delete from emp where ename='肖伟';
Query OK, 1 row affected (0.01 sec)

root@database-one 00:38:  [gftest]> rollback;
Query OK, 0 rows affected (0.00 sec)

root@database-one 00:38:  [gftest]> select * from emp;
+--------+------+---------+------------+--------+
| ename  | age  | sal     | hiredate   | deptno |
+--------+------+---------+------------+--------+
| 郭军   |   27 | 8400.00 | 2019-12-08 |     10 |
| 刘杰   |   30 | 9100.00 | 2018-04-09 |     10 |
| 王艳   |   24 | 6000.00 | 2020-01-05 |     20 |
| 马丽   |   26 | 7200.00 | 2018-07-06 |     30 |
| 陈实   |   31 | 9000.00 | 2019-07-01 |     10 |
+--------+------+---------+------------+--------+
5 rows in set (0.00 sec)
复制

可以看到，会话默认情况下，确实是事务自动提交模式。当删除“肖伟”这条记录后，因为自动提交，数据已经真实的从表中删除了，所以紧接的rollback没有改变任何数据，只是自己执行了而已。

同样的动作，我们人为的用事务控制起来看看：

root@database-one 00:42:  [gftest]> start transaction;
Query OK, 0 rows affected (0.00 sec)

root@database-one 00:42:  [gftest]> select * from emp;
+--------+------+---------+------------+--------+
| ename  | age  | sal     | hiredate   | deptno |
+--------+------+---------+------------+--------+
| 郭军   |   27 | 8400.00 | 2019-12-08 |     10 |
| 刘杰   |   30 | 9100.00 | 2018-04-09 |     10 |
| 王艳   |   24 | 6000.00 | 2020-01-05 |     20 |
| 马丽   |   26 | 7200.00 | 2018-07-06 |     30 |
| 陈实   |   31 | 9000.00 | 2019-07-01 |     10 |
+--------+------+---------+------------+--------+
5 rows in set (0.00 sec)

root@database-one 00:43:  [gftest]> delete from emp where ename='马丽';
Query OK, 1 row affected (0.00 sec)

root@database-one 00:43:  [gftest]> rollback;
Query OK, 0 rows affected (0.00 sec)

root@database-one 00:43:  [gftest]> select * from emp;
+--------+------+---------+------------+--------+
| ename  | age  | sal     | hiredate   | deptno |
+--------+------+---------+------------+--------+
| 郭军   |   27 | 8400.00 | 2019-12-08 |     10 |
| 刘杰   |   30 | 9100.00 | 2018-04-09 |     10 |
| 王艳   |   24 | 6000.00 | 2020-01-05 |     20 |
| 马丽   |   26 | 7200.00 | 2018-07-06 |     30 |
| 陈实   |   31 | 9000.00 | 2019-07-01 |     10 |
+--------+------+---------+------------+--------+
5 rows in set (0.00 sec)
复制

可以看到，人为控制事务时，事务的范围就从每个语句自己是一个事务，变成了命令指定的范围，这时rollback或者commit就会对事务内的所有变化落地还是撤销产生影响。

START TRANSACTION允许使用选项来控制事务特性，要指定多个选项时，用逗号分隔。

• WITH CONSISTENT SNAPSHOT，启动一致读取，只适用于InnoDB。begin/start transaction命令并不是一个事务的起点，在执行到它们之后的第一个语句，事务才真正启动。如果你想要马上启动一个事务，可以使用 start
  transaction with consistent snapshot这个命令。其效果与发出一个start
  transaction，同时执行一个SELECT相同。
• READ WRITE和READ ONLY设置事务访问模式。它们允许或禁止更改事务中使用的表。READ ONLY限制事务修改或锁定其他事务可见的表，但事务仍然可以修改或锁定临时表。如果未指定访问模式，默认为READ WRITE。
• BEGIN和BEGIN WORK是START TRANSACTION的别名.

要禁用自动提交模式，使用SET autocommit=0;

root@database-one 01:11:  [gftest]> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

root@database-one 01:12:  [gftest]> SET autocommit=0;
Query OK, 0 rows affected (0.00 sec)

root@database-one 01:12:  [gftest]> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

root@database-one 01:12:  [gftest]> select * from emp;
+--------+------+---------+------------+--------+
| ename  | age  | sal     | hiredate   | deptno |
+--------+------+---------+------------+--------+
| 郭军   |   27 | 8400.00 | 2019-12-08 |     10 |
| 刘杰   |   30 | 9100.00 | 2018-04-09 |     10 |
| 王艳   |   24 | 6000.00 | 2020-01-05 |     20 |
| 马丽   |   26 | 7200.00 | 2018-07-06 |     30 |
| 陈实   |   31 | 9000.00 | 2019-07-01 |     10 |
+--------+------+---------+------------+--------+
5 rows in set (0.00 sec)

root@database-one 01:12:  [gftest]> delete from emp where ename='马丽';
Query OK, 1 row affected (0.00 sec)

root@database-one 01:13:  [gftest]> rollback;
Query OK, 0 rows affected (0.00 sec)

root@database-one 01:13:  [gftest]> select * from emp;
+--------+------+---------+------------+--------+
| ename  | age  | sal     | hiredate   | deptno |
+--------+------+---------+------------+--------+
| 郭军   |   27 | 8400.00 | 2019-12-08 |     10 |
| 刘杰   |   30 | 9100.00 | 2018-04-09 |     10 |
| 王艳   |   24 | 6000.00 | 2020-01-05 |     20 |
| 马丽   |   26 | 7200.00 | 2018-07-06 |     30 |
| 陈实   |   31 | 9000.00 | 2019-07-01 |     10 |
+--------+------+---------+------------+--------+
5 rows in set (0.00 sec)
复制

可以看到，在关闭自动提交后，和开篇一样的操作，rollback回滚了对数据的变化。

注意，autocommit是会话变量，每次对autocommit的修改只影响当前会话。