设置测试环境
MySQL默认的引擎时InnoDB,默认auto commit属性为ON
创建测试数据表,test.auto_commit
mysql> create database if not exists test;
Query OK, 1 row affected (0.09 sec)
mysql> drop table if exists test.auto_commit;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> create table test.auto_commit(`index` int(11) key NOT NULL, val int(11) NOT NULL) ENGINE=InnoDB;
Query OK, 0 rows affected (0.03 sec)
检查auto commit属性为打开
mysql> show variables like "%autocommit%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
1 row in set (0.00 sec)
工作模式
不使用事务
当不使用事务提交SQL语句,且auto commit属性是打开时,默认将每一条语句都认为是一个独立的事务。当提交SQL语句后,服务器在执行语句后,自动执行commit操作,而不需要额外加上commit语句。
在session A里面执行
mysql> replace into test.auto_commit values(1,0);
Query OK, 1 row affected (0.00 sec)
在session B里面执行
mysql> select * from test.auto_commit;
+-------+-----+
| index | val |
+-------+-----+
| 1 | 0 |
+-------+-----+
1 row in set (0.00 sec)
可以看到数据被立即提交了
现在临时取消auto commit进行测试
在session A里面执行
mysql> set session autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like "%autocommit%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | OFF |
+---------------+-------+
mysql> replace into test.auto_commit values(1, 1);
Query OK, 1 row affected (0.01 sec)
mysql> select * from test.auto_commit;
+-------+-----+
| index | val |
+-------+-----+
| 1 | 1 |
+-------+-----+
1 row in set (0.00 sec)
在session B里面执行
mysql> select * from test.auto_commit;
+-------+-----+
| index | val |
+-------+-----+
| 1 | 0 |
+-------+-----+
1 row in set (0.00 sec)
可以看到session B并不能访问到最新数据
在session A里面提交
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
在session B里面查询
+-------+-----+
| index | val |
+-------+-----+
| 1 | 1 |
+-------+-----+
1 row in set (0.00 sec)
当取消auto commit属性时,在session内部提交SQL语句相当于使用事务执行。
使用事务
当用户使用事务进行提交的时候,自动会取消auto commit属性
在session A中执行
mysql> set session autocommit=1;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like "%autocommit%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
1 row in set (0.16 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> replace into test.auto_commit values(1, 2);
Query OK, 2 rows affected (0.00 sec)
mysql> select * from test.auto_commit;
+-------+-----+
| index | val |
+-------+-----+
| 1 | 2 |
+-------+-----+
1 row in set (0.00 sec)
A 开启了auto commit属性 同时也开启了事务
在Session B中执行
mysql> select * from test.auto_commit;
+-------+-----+
| index | val |
+-------+-----+
| 1 | 1 |
+-------+-----+
1 row in set (0.00 sec)
session B查询不到最新的数据
在session A中执行
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
在session B中执行
mysql> select * from test.auto_commit;
+-------+-----+
| index | val |
+-------+-----+
| 1 | 2 |
+-------+-----+
1 row in set (0.00 sec)
可以看到在事务中就等同于取消了auto commit属性
批量SQL语句加速
关闭auto commit属性可以对SQL进行加速。也就是说在使用事务模式下,或者在非事务模式下关闭auto commit属性在处理批量SQL可以提高SQL执行速度。
更改配置
更改配置文件
更改配置文件可以保证重启以后仍然生效,缺点是第一次生效需要重新启动MySQL。可以配合热更改全局变量相互补充。
[mysqld]
init_connect='SET autocommit=0'
或者
[mysqld]
autocommit=0
重启mysql
service mysqld restart
更改全局变量
热更改配置可以让配置立即生效,缺点是重启后配置即还原为文件配置。可以配合更改配置文件相互补充。
mysql> set global init_connect="set autocommit=0";
Query OK, 0 rows affected (0.01 sec)
或者
set global autocommit=0;
通过init_connect修改,则在下一次建立新连接生效。
通过autocommit更改则立即生效。
客户端临时更改
mysql> set session autocommit=0;
Query OK, 0 rows affected (0.00 sec)
连接断开后,则失效。用于临时更改。
比如python的mysql api则可以在建立连接的时候指定auto commit属性的值,生效范围是本次连接。