mysql中事务开启语法_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的修改只影响当前会话。