MySQL的auto commit属性

设置测试环境

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属性的值，生效范围是本次连接。