【整理】MySQL之autocommit_MySQL

bitsCN.com

【整理】MySQL之autocommit

 

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

01mysql> select @@session.autocommit;02+----------------------+03| @@session.autocommit |04+----------------------+05|                    1 |06+----------------------+071 row in set (0.00 sec)0809mysql> select @@global.autocommit;      10+---------------------+11| @@global.autocommit |12+---------------------+13|                   1 |14+---------------------+151 row in set (0.00 sec)1617mysql>

那么如果我们不想让 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

01mysql>02mysql> 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+------------------+-----+-------------+-----------+-------------+---------------------------------------+081 row in set (0.00 sec)0910mysql>11mysql> show tables;12Empty set (0.00 sec)1314mysql>15mysql> select @@global.autocommit;16+---------------------+17| @@global.autocommit |18+---------------------+19|                   1 |20+---------------------+211 row in set (0.00 sec)2223mysql> select @@session.autocommit;     24+----------------------+25| @@session.autocommit |26+----------------------+27|                    1 |28+----------------------+291 row in set (0.00 sec)3031mysql>32mysql> set autocommit=0;33Query OK, 0 rows affected (0.00 sec)3435mysql>36mysql> select @@global.autocommit;37+---------------------+38| @@global.autocommit |39+---------------------+40|                   1 |41+---------------------+421 row in set (0.00 sec)4344mysql> select @@session.autocommit;         45+----------------------+46| @@session.autocommit |47+----------------------+48|                    0 |49+----------------------+501 row in set (0.00 sec)5152mysql>b. 创建一个测试表 01mysql> create table t_autocommit(02    -> id int not null auto_increment,03    -> amount int not null default '0',04    -> primary key(id)05    -> )engine=innodb;06Query OK, 0 rows affected (0.01 sec)0708mysql>09mysql> show tables;10+----------------+11| Tables_in_test |12+----------------+13| t_autocommit   |14+----------------+151 row in set (0.00 sec)1617mysql>18mysql> describe t_autocommit;19+--------+---------+------+-----+---------+----------------+20| Field  | Type    | Null | Key | Default | Extra          |21+--------+---------+------+-----+---------+----------------+22| id     | int(11) | NO   | PRI | NULL    | auto_increment |23| amount | int(11) | NO   |     | 0       |                |24+--------+---------+------+-----+---------+----------------+252 rows in set (0.00 sec)2627mysql>28mysql> select * from t_autocommit;29Empty set (0.00 sec)3031mysql>c. 插入数据 01mysql>02mysql> insert into t_autocommit set amount=1;03Query OK, 1 row affected (0.00 sec)0405mysql>06mysql> select * from t_autocommit;          07+----+--------+08| id | amount |09+----+--------+10|  1 |      1 |11+----+--------+121 row in set (0.00 sec)1314mysql>15mysql> update t_autocommit set amount=amount+10;16Query OK, 1 row affected (0.00 sec)17Rows matched: 1  Changed: 1  Warnings: 01819mysql>20mysql> select * from t_autocommit;             21+----+--------+22| id | amount |23+----+--------+24|  1 |     11 |25+----+--------+261 row in set (0.00 sec)2728mysql>29mysql> show binlog events;30+------------------+-----+-------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------+31| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                                                                                                                   |32+------------------+-----+-------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------+33| mysql-bin.000001 |   4 | Format_desc |         1 |         120 | Server ver: 5.6.10-log, Binlog ver: 4                                                                                                  |34| mysql-bin.000001 | 120 | Query       |         1 |         316 | use `test`; create table t_autocommit(35id int not null auto_increment,36amount int not null default '0',37primary key(id)38)engine=innodb |39+------------------+-----+-------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------+402 rows in set (0.00 sec)4142mysql>       发现 binlog 中仅记录了 create table 动作，insert 和 update 由于 autocommit 为 0 的缘故没有被记录到 binlog 中。d.断开 mysql ，再重新连接。 01mysql>02mysql> quit03Bye04[root@Betty ~]#05[root@Betty ~]# mysql -u root -p06Enter password:07Welcome to the MySQL monitor.  Commands end with ; or /g.08Your MySQL connection id is 309Server version: 5.6.10-log Source distribution1011Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.1213Oracle is a registered trademark of Oracle Corporation and/or its14affiliates. Other names may be trademarks of their respective15owners.1617Type 'help;' or '/h' for help. Type '/c' to clear the current input statement.1819mysql>20mysql> use test;21Reading table information for completion of table and column names22You can turn off this feature to get a quicker startup with -A2324Database changed25mysql>26mysql> show tables;27+----------------+28| Tables_in_test |29+----------------+30| t_autocommit   |31+----------------+321 row in set (0.00 sec)3334mysql>35mysql> select * from t_autocommit;36Empty set (0.00 sec)3738mysql>       发现什么数据都没有。为什么呢？因为 SQL 语句并没有被自己（当前 session）提交给 server 端去处理，只是在当前连接中做了相应处理。 重复上面的实验，但是保持 autocommit 的默认值（1）。 001mysql>002mysql> show binlog events;003+------------------+-----+-------------+-----------+-------------+---------------------------------------+004| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                  |005+------------------+-----+-------------+-----------+-------------+---------------------------------------+006| mysql-bin.000001 |   4 | Format_desc |         1 |         120 | Server ver: 5.6.10-log, Binlog ver: 4 |007+------------------+-----+-------------+-----------+-------------+---------------------------------------+0081 row in set (0.00 sec)009010mysql>011mysql> show tables;012Empty set (0.01 sec)013014mysql>015mysql> select @@global.autocommit;                     016+---------------------+017| @@global.autocommit |018+---------------------+019|                   1 |020+---------------------+0211 row in set (0.00 sec)022023mysql>024mysql> select @@session.autocommit;                                   025+----------------------+026| @@session.autocommit |027+----------------------+028|                    1 |029+----------------------+0301 row in set (0.00 sec)031032mysql>033mysql> create table t_autocommit(034    -> id int not null auto_increment,035    -> amount int not null default '0',036    -> primary key(id)037    -> )engine=innodb;038Query OK, 0 rows affected (0.01 sec)039040mysql>041mysql> show tables;042+----------------+043| Tables_in_test |044+----------------+045| t_autocommit   |046+----------------+0471 row in set (0.00 sec)048049mysql>050mysql> describe t_autocommit;051+--------+---------+------+-----+---------+----------------+052| Field  | Type    | Null | Key | Default | Extra          |053+--------+---------+------+-----+---------+----------------+054| id     | int(11) | NO   | PRI | NULL    | auto_increment |055| amount | int(11) | NO   |     | 0       |                |056+--------+---------+------+-----+---------+----------------+0572 rows in set (0.00 sec)058059mysql>060mysql> insert into t_autocommit set amount=1;061Query OK, 1 row affected (0.00 sec)062063mysql>064mysql> select * from t_autocommit;065+----+--------+066| id | amount |067+----+--------+068|  1 |      1 |069+----+--------+0701 row in set (0.00 sec)071072mysql>073mysql> update t_autocommit set amount=amount+10;074Query OK, 1 row affected (0.00 sec)075Rows matched: 1  Changed: 1  Warnings: 0076077mysql>078mysql> select * from t_autocommit;             079+----+--------+080| id | amount |081+----+--------+082|  1 |     11 |083+----+--------+0841 row in set (0.00 sec)085086mysql>087mysql> show binlog events;088+------------------+-----+-------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------+089| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                                                                                                                   |090+------------------+-----+-------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------+091| mysql-bin.000001 |   4 | Format_desc |         1 |         120 | Server ver: 5.6.10-log, Binlog ver: 4                                                                                                  |092| mysql-bin.000001 | 120 | Query       |         1 |         316 | use `test`; create table t_autocommit(093id int not null auto_increment,094amount int not null default '0',095primary key(id)096)engine=innodb |097| mysql-bin.000001 | 316 | Query       |         1 |         395 | BEGIN                                                                                                                                  |098| mysql-bin.000001 | 395 | Intvar      |         1 |         427 | INSERT_ID=1                                                                                                                            |099| mysql-bin.000001 | 427 | Query       |         1 |         538 | use `test`; insert into t_autocommit set amount=1                                                                                      |100| mysql-bin.000001 | 538 | Xid         |         1 |         569 | COMMIT /* xid=62 */                                                                                                                    |101| mysql-bin.000001 | 569 | Query       |         1 |         648 | BEGIN                                                                                                                                  |102| mysql-bin.000001 | 648 | Query       |         1 |         762 | use `test`; update t_autocommit set amount=amount+10                                                                                   |103| mysql-bin.000001 | 762 | Xid         |         1 |         793 | COMMIT /* xid=64 */                                                                                                                    |104+------------------+-----+-------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------+1059 rows in set (0.00 sec)106107mysql>108mysql> quit109Bye110[root@Betty ~]#111[root@Betty ~]# mysql -u root -p112Enter password:113Welcome to the MySQL monitor.  Commands end with ; or /g.114Your MySQL connection id is 4115Server version: 5.6.10-log Source distribution116117Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.118119Oracle is a registered trademark of Oracle Corporation and/or its120affiliates. Other names may be trademarks of their respective121owners.122123Type 'help;' or '/h' for help. Type '/c' to clear the current input statement.124125mysql>126mysql> use test;127Reading table information for completion of table and column names128You can turn off this feature to get a quicker startup with -A129130Database changed131mysql>132mysql> show tables;133+----------------+134| Tables_in_test |135+----------------+136| t_autocommit   |137+----------------+1381 row in set (0.00 sec)139140mysql>141mysql> select * from t_autocommit;142+----+--------+143| id | amount |144+----+--------+145|  1 |     11 |146+----+--------+1471 row in set (0.00 sec)148149mysql>150mysql>

 

这回该有的都有了。 

 

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

 

网友说法： 

不要设定 autocommit 这个开关，让它保持 autocommit=1 这个默认状态。 平常有查询/更新都是需要得到最新的数据，根本不需要启动一个事务，除非有特定状况才需要开启事务，再手工用 start transaction ... commit /rollback 。

这种全局设置，在生产环境中没有多大意义。一般都是在应用程序框架（如连接池的库）中设置 autocommit 是否为 ON/OFF, 说白了，就是得到数据库连接以后，显示的调用一次 set autocommit on/off (or =1/0)

 

 

bitsCN.com