暂无图片
暂无图片
暂无图片
暂无图片
暂无图片

MySQL中的事务控制语句

原创 巩飞 2020-04-02
2388

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的修改只影响当前会话。

「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

文章被以下合辑收录

评论