【整理】MySQL之autocommit(一)

【整理】MySQL之autocommit

mysql 默认是开启 auto commit 的。可以通过如下命令查看 session 级别和 global 级别的设置：

01
mysql> select @@session.autocommit;
02
+----------------------+
03
| @@session.autocommit |
04
+----------------------+
05
|                    1 |
06
+----------------------+
07
1 row in set (0.00 sec)
08

09
mysql> select @@global.autocommit;      
10
+---------------------+
11
| @@global.autocommit |
12
+---------------------+
13
|                   1 |
14
+---------------------+
15
1 row in set (0.00 sec)
16

17
mysql>

那么如果我们不想让 mysql 执行自动提交时，应该如何禁用 autocommit 呢？可以通过 Cmd-Line、Option file、System Var 上都可用的 init_connect 来设置。

A string to be executed by the server for each client that connects. The string consists of one or more SQL statements. To specify multiple statements, separate them by semicolon characters.

上面这段话的意思是，每个 client 连接上来时都会由 server 执行一次由 init_connect 指定的 sql 字串。（是否可以认为是基于 session 的？）

利用这个变量，可以通过如下方式禁用 autocommit：

方法一：

1

mysql>SET GLOBAL init_connect='SET autocommit=0';

方法二：

在 MySQL 的配置文件中设置

1

[mysqld]

2

init_connect='SET autocommit=0'

方法三：

启动 mysql 时带上命令行参数 –init_connect='SET autocommit=0'

值得说明的一点是，这个参数的设置对拥有 super 权限的用户是无效的，具体原因说明如下：

Note that the content of init_connect is not executed for users that have the SUPER privilege. This is done so that an erroneous value for init_connect does not prevent all clients from connecting. For example, the value might contain a statement that has a syntax error, thus causing client connections to fail. Not executing init_connect for users that have the SUPER privilege enables them to open a connection and fix the init_connect value.

默认开启的 autocommit 肯定会对 mysql 的性能有一定影响，但既然默认开启必定是有原因的，所以如果你不知道自己到底会遇到什么问题的情况下还是不要改这个设置为妙。举个例子来说明开启 autocommit 会产生的性能影响，如果你插入了 1000 条数据，mysql 会 commit 1000 次，如果我们把 autocommit 关闭掉，通过程序来控制，只要一次commit 就可以了。

========= 我是分割线 =========

a. 初始状态+设置 session 级别的 autocommit 为 0

01
mysql>
02
mysql> show binlog events;
03
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
04
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                  |
05
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
06
| mysql-bin.000001 |   4 | Format_desc |         1 |         120 | Server ver: 5.6.10-log, Binlog ver: 4 |
07
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
08
1 row in set (0.00 sec)
09

10
mysql>
11
mysql> show tables;
12
Empty set (0.00 sec)
13

14
mysql>
15
mysql> select @@global.autocommit;
16
+---------------------+
17
| @@global.autocommit |
18
+---------------------+
19
|                   1 |
20
+---------------------+
21
1 row in set (0.00 sec)
22

23
mysql> select @@session.autocommit;     
24
+----------------------+
25
| @@session.autocommit |
26
+----------------------+
27
|                    1 |
28
+----------------------+
29
1 row in set (0.00 sec)
30

31
mysql>
32
mysql> set autocommit=0;
33
Query OK, 0 rows affected (0.00 sec)
34

35
mysql>
36
mysql> select @@global.autocommit;
37
+---------------------+
38
| @@global.autocommit |
39
+---------------------+
40
|                   1 |
4