autocommit
一、概念
- 事务自动提交设置,默认为1,即除非显示声明一个事务的开始,否则每一个查询都会被当做独立的事务被处理
- 默认情况下,数据库处于自动提交模式。每一条语句处于一个单独的事务中,在这条语句执行完毕时,如果执行成功则隐式的提交事务,如果执行失败则隐式的回滚事务
- 关闭自动提交的原因,事务作为一个独立处理最小单元,往往需要处理一系列连续的数据操作,这些操作被视为一个整体,需要同时成功或同时失败,而不能其中某个操作成功;比如:A汇款给B,B接收到歀;即 A的账户扣款,而B的账户增加;必须同时成功或同时失败;如果成功则commit否则整体回滚;批量数据提交,如果提交1000条数据操作,需要处理1000次提交commit,而如果关闭autocommit等1000条数据处理完毕后统一一次提交
- 存储引擎需支持事务,如InnoDB,而MyISAM不支持事务
- 事务是否开启:如果没有显示的开启事务,autocommit 才会生效;
// 示例
// descrpiton:在显示开启事务的情况下,无论是否开启自动提交,下面示例均会发生
// 结论:在显示开启事务的情况下,autocommit 不生效
// console 1
mysql> begin ;
Query OK, 0 rows affected
mysql> select * from t_order ;
+----+-------+--------------+--------+
| id | name | order_no | status |
+----+-------+--------------+--------+
| 1 | 订单1 | DD_000000001 | 1 |
| 2 | 订单2 | DD_000000002 | 2 |
| 3 | 订单3 | DD_000000003 | 3 |
+----+-------+--------------+--------+
mysql> update t_order set order_no = "DD_000000033" where id = 3 ;
Query OK, 1 row affected
mysql> select * from t_order ; // 修改结果当前会话中的当前事务可见
+----+-------+--------------+--------+
| id | name | order_no | status |
+----+-------+--------------+--------+
| 1 | 订单1 | DD_000000001 | 1 |
| 2 | 订单2 | DD_000000002 | 2 |
| 3 | 订单3 | DD_000000033 | 3 |
+----+-------+--------------+--------+
3 rows in set
// console 2
mysql> select * from t_order ; // 不可见
+----+-------+--------------+--------+
| id | name | order_no | status |
+----+-------+--------------+--------+
| 1 | 订单1 | DD_000000001 | 1 |
| 2 | 订单2 | DD_000000002 | 2 |
| 3 | 订单3 | DD_000000003 | 3 |
+----+-------+--------------+--------+
3 rows in set
// console 1
mysql> commit ;
Query OK, 0 rows affected
// console 2
mysql> select * from t_order ;
+----+-------+--------------+--------+
| id | name | order_no | status |
+----+-------+--------------+--------+
| 1 | 订单1 | DD_000000001 | 1 |
| 2 | 订单2 | DD_000000002 | 2 |
| 3 | 订单3 | DD_000000033 | 3 |
+----+-------+--------------+--------+
3 rows in set
二、基本操作
// 查询
mysql> SELECT @@autocommit ;
+--------------+
| @@autocommit |
+--------------+
| 1 |
+--------------+
1 row in set
mysql> SHOW VARIABLES LIKE "%AUTOCOMMIT%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
1 row in set
// 修改
mysql> SET autocommit = 0 ;
Query OK, 0 rows affected
// 在当前session关闭autocommit:
mysql> set @@session.autocommit=0;
Query OK, 0 rows affected (0.00 sec)
// 在global级别关闭autocommit:
mysql> set @@global.autocommit=0;
Query OK, 0 rows affected (0.01 sec)
三、产生的问题
示例:
// 系统准备
// MySQL:Server version: 5.7.17 ;
// 查询MySQL版本:CMD 命令,输入mysql,回车进入命令行,根据系统提示,即可查看当前安装版本
// 数据连接工具:Navicat for MySQL
// F6命令打开两个console窗口,模拟两个不同的connection
----------
// 示例
// 1.在 autocommit = 1 ; 时,下面示例运行正常。即 console 1 与 console 2 中查询结果一致;
// 2.在 autocommit = 0 ; 时,在 console 1 中对数据进行修改,在 console 1 中 查询到的数据时更新后的数据;但在 console 2 中读取到的数据依然是旧的;
// T1 时间 console 1
mysql> SET AUTOCOMMIT = 0 ;
Query OK, 0 rows affected
mysql> UPDATE T_ORDER SET STATUS = 4 WHERE ID = 3 ;
Query OK, 1 row affected
// T2 时间 console 2
mysql> SET AUTOCOMMIT = 0 ;
Query OK, 0 rows affected
mysql> SELECT * FROM T_ORDER WHERE ID = 3 ;
+----+-------+--------------+--------+
| id | name | order_no | status |
+----+-------+--------------+--------+
| 3 | 订单3 | DD_000000003 | 3 |
+----+-------+--------------+--------+
1 row in set
// T3 时间 console 1
mysql> SELECT * FROM T_ORDER WHERE ID = 3 ;
+----+-------+--------------+--------+
| id | name | order_no | status |
+----+-------+--------------+--------+
| 3 | 订单3 | DD_000000003 | 4 |
+----+-------+--------------+--------+
1 row in set
mysql> COMMIT ;
Query OK, 0 rows affected
// T4 时间 console 2
mysql> SELECT * FROM T_ORDER WHERE ID = 3 ;
// 在 console 1 提交事务后查询结果依旧没有改变
+----+-------+--------------+--------+
| id | name | order_no | status |
+----+-------+--------------+--------+
| 3 | 订单3 | DD_000000003 | 3 |
+----+-------+--------------+--------+
1 row in set
// T5 时间 console 2
// 查询到最新数据的方式
// 1.执行 commit ; 当前事务结束;否则再次查询仍然与上次的查询在一个事务中,查询是上次查询结果的快照;
// 2.SET autocommit = 1 ; 每次操作都是一个独立的原子性的操作,自动提交,每次都是一个独立的事务
结论:
- 使用时应注意,在需要关闭自动提交的操作前将 autocommit = 0 操作完毕后,再将其改为 autocommit = 1 ;
- 在关闭AutoCommit的条件下,console 1 在T1和T2两个时间点执行的SQL语句其实在一个事务里,因此每次读到的其实只是一个快照,即一致性非阻塞读:InnoDB通过MVCC机制表示数据库某一时刻的查询快照,查询可以看该时刻之前提交的事务所做的改变,但是不能看到该时刻之后或者未提交事务所做的改变。但是,查询可以看到同一事务中之前语句所做的改变
未起作用
- 连接mysql用户的权限不能大于启动mysql的用户的权限,不然init_connect=’SET autocommit=0’根本不会启作用,也不会报任何错误
- 显示开启事务
注意:
1、不能将”关闭autocommit”作为缺省设置,否则在 innodb 表上执行的查询操作也将因为没有执行 commit 或者 rollback 而一直锁表!因此只能在需要时局部关闭 autocommit,并在操作完成后开启 autocommit
2、连接mysql用户的权限不能大于启动mysql的用户的权限,不然init_connect=’SET autocommit=0’根本不会启作用,也不会报任何错误
If a user has SUPER privilege, init_connect will not execute,(otherwise if init_connect will a wrong query no one can connect to server).
Note, if init_connect is a wrong query, the connection is closing without any errors and next command will clause ‘lost connection’ error.