开发者社区> 摩云飞> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

【整理】MySQL 之 autocommit

简介:
+关注继续查看
mysql 默认是开启 auto commit 的。可以通过如下命令查看 session 级别和 global 级别的设置: 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql> select @@session.autocommit;
+----------------------+
| @@session.autocommit |
+----------------------+
|                    1 |
+----------------------+
1 row in set (0.00 sec)
 
mysql> select @@global.autocommit;      
+---------------------+
| @@global.autocommit |
+---------------------+
|                   1 |
+---------------------+
1 row in set (0.00 sec)
 
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
2
[mysqld]
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 就可以了。 

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

      另外一篇博客《Innodb表类型中autocommit的设置》中展示了设置 autocommit 为 0 的效果。

a. 初始状态+设置 session 级别的 autocommit 为 0 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
mysql>
mysql> show binlog events;
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                  |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| mysql-bin.000001 |   4 | Format_desc |         1 |         120 | Server ver: 5.6.10-log, Binlog ver: 4 |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
1 row in set (0.00 sec)
 
mysql>
mysql> show tables;
Empty set (0.00 sec)
 
mysql>
mysql> select @@global.autocommit;
+---------------------+
| @@global.autocommit |
+---------------------+
|                   1 |
+---------------------+
1 row in set (0.00 sec)
 
mysql> select @@session.autocommit;     
+----------------------+
| @@session.autocommit |
+----------------------+
|                    1 |
+----------------------+
1 row in set (0.00 sec)
 
mysql>
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
 
mysql>
mysql> select @@global.autocommit;
+---------------------+
| @@global.autocommit |
+---------------------+
|                   1 |
+---------------------+
1 row in set (0.00 sec)
 
mysql> select @@session.autocommit;         
+----------------------+
| @@session.autocommit |
+----------------------+
|                    0 |
+----------------------+
1 row in set (0.00 sec)
 
mysql>
b. 创建一个测试表 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
mysql> create table t_autocommit(
    -> id int not null auto_increment,
    -> amount int not null default '0',
    -> primary key(id)
    -> )engine=innodb;
Query OK, 0 rows affected (0.01 sec)
 
mysql>
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t_autocommit   |
+----------------+
1 row in set (0.00 sec)
 
mysql>
mysql> describe t_autocommit;
+--------+---------+------+-----+---------+----------------+
| Field  | Type    | Null | Key | Default | Extra          |
+--------+---------+------+-----+---------+----------------+
| id     | int(11) | NO   | PRI | NULL    | auto_increment |
| amount | int(11) | NO   |     | 0       |                |
+--------+---------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
 
mysql>
mysql> select * from t_autocommit;
Empty set (0.00 sec)
 
mysql>
c. 插入数据 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
mysql>
mysql> insert into t_autocommit set amount=1;
Query OK, 1 row affected (0.00 sec)
 
mysql>
mysql> select * from t_autocommit;          
+----+--------+
| id | amount |
+----+--------+
|  1 |      1 |
+----+--------+
1 row in set (0.00 sec)
 
mysql>
mysql> update t_autocommit set amount=amount+10;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql>
mysql> select * from t_autocommit;             
+----+--------+
| id | amount |
+----+--------+
|  1 |     11 |
+----+--------+
1 row in set (0.00 sec)
 
mysql>
mysql> show binlog events;
+------------------+-----+-------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------+
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                                                                                                                   |
+------------------+-----+-------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------+
| mysql-bin.000001 |   4 | Format_desc |         1 |         120 | Server ver: 5.6.10-log, Binlog ver: 4                                                                                                  |
| mysql-bin.000001 | 120 | Query       |         1 |         316 | use `test`; create table t_autocommit(
id int not null auto_increment,
amount int not null default '0',
primary key(id)
)engine=innodb |
+------------------+-----+-------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
 
mysql>
       发现 binlog 中仅记录了 create table 动作,insert 和 update 由于 autocommit 为 0 的缘故没有被记录到 binlog 中。

d.断开 mysql ,再重新连接。 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
mysql>
mysql> quit
Bye
[root@Betty ~]#
[root@Betty ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.10-log Source distribution
 
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
mysql>
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
 
Database changed
mysql>
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t_autocommit   |
+----------------+
1 row in set (0.00 sec)
 
mysql>
mysql> select * from t_autocommit;
Empty set (0.00 sec)
 
mysql>
       发现什么数据都没有。为什么呢?因为 SQL 语句并没有被自己(当前 session)提交给 server 端去处理,只是在当前连接中做了相应处理。 


重复上面的实验,但是保持 autocommit 的默认值(1)。 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
mysql>
mysql> show binlog events;
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                  |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| mysql-bin.000001 |   4 | Format_desc |         1 |         120 | Server ver: 5.6.10-log, Binlog ver: 4 |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
1 row in set (0.00 sec)
 
mysql>
mysql> show tables;
Empty set (0.01 sec)
 
mysql>
mysql> select @@global.autocommit;                     
+---------------------+
| @@global.autocommit |
+---------------------+
|                   1 |
+---------------------+
1 row in set (0.00 sec)
 
mysql>
mysql> select @@session.autocommit;                                   
+----------------------+
| @@session.autocommit |
+----------------------+
|                    1 |
+----------------------+
1 row in set (0.00 sec)
 
mysql>
mysql> create table t_autocommit(
    -> id int not null auto_increment,
    -> amount int not null default '0',
    -> primary key(id)
    -> )engine=innodb;
Query OK, 0 rows affected (0.01 sec)
 
mysql>
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t_autocommit   |
+----------------+
1 row in set (0.00 sec)
 
mysql>
mysql> describe t_autocommit;
+--------+---------+------+-----+---------+----------------+
| Field  | Type    | Null | Key | Default | Extra          |
+--------+---------+------+-----+---------+----------------+
| id     | int(11) | NO   | PRI | NULL    | auto_increment |
| amount | int(11) | NO   |     | 0       |                |
+--------+---------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
 
mysql>
mysql> insert into t_autocommit set amount=1;
Query OK, 1 row affected (0.00 sec)
 
mysql>
mysql> select * from t_autocommit;
+----+--------+
| id | amount |
+----+--------+
|  1 |      1 |
+----+--------+
1 row in set (0.00 sec)
 
mysql>
mysql> update t_autocommit set amount=amount+10;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql>
mysql> select * from t_autocommit;             
+----+--------+
| id | amount |
+----+--------+
|  1 |     11 |
+----+--------+
1 row in set (0.00 sec)
 
mysql>
mysql> show binlog events;
+------------------+-----+-------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------+
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                                                                                                                   |
+------------------+-----+-------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------+
| mysql-bin.000001 |   4 | Format_desc |         1 |         120 | Server ver: 5.6.10-log, Binlog ver: 4                                                                                                  |
| mysql-bin.000001 | 120 | Query       |         1 |         316 | use `test`; create table t_autocommit(
id int not null auto_increment,
amount int not null default '0',
primary key(id)
)engine=innodb |
| mysql-bin.000001 | 316 | Query       |         1 |         395 | BEGIN                                                                                                                                  |
| mysql-bin.000001 | 395 | Intvar      |         1 |         427 | INSERT_ID=1                                                                                                                            |
| mysql-bin.000001 | 427 | Query       |         1 |         538 | use `test`; insert into t_autocommit set amount=1                                                                                      |
| mysql-bin.000001 | 538 | Xid         |         1 |         569 | COMMIT /* xid=62 */                                                                                                                    |
| mysql-bin.000001 | 569 | Query       |         1 |         648 | BEGIN                                                                                                                                  |
| mysql-bin.000001 | 648 | Query       |         1 |         762 | use `test`; update t_autocommit set amount=amount+10                                                                                   |
| mysql-bin.000001 | 762 | Xid         |         1 |         793 | COMMIT /* xid=64 */                                                                                                                    |
+------------------+-----+-------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------+
9 rows in set (0.00 sec)
 
mysql>
mysql> quit
Bye
[root@Betty ~]#
[root@Betty ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.10-log Source distribution
 
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
mysql>
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
 
Database changed
mysql>
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t_autocommit   |
+----------------+
1 row in set (0.00 sec)
 
mysql>
mysql> select * from t_autocommit;
+----+--------+
| id | amount |
+----+--------+
|  1 |     11 |
+----+--------+
1 row in set (0.00 sec)
 
mysql>
mysql>
这回该有的都有了。 

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

网友说法: 
不要设定 autocommit 这个开关,让它保持 autocommit=1 这个默认状态。 平常有查询\更新都是需要得到最新的数据,根本不需要启动一个事务,除非有特定状况才需要开启事务,再手工用 start transaction ... commit /rollback 。
这种全局设置,在生产环境中没有多大意义。一般都是在应用程序框架(如连接池的库)中设置 autocommit 是否为 ON/OFF, 说白了,就是得到数据库连接以后,显示的调用一次 set autocommit on/off (or =1/0)

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
一次面试,关于mysql事务的那些事
一次面试,关于mysql事务的那些事
0 0
MySQL复习资料(八)——MySQL-事务
MySQL复习资料(八)——MySQL-事务
0 0
【MySQL】浅谈MySQL事务原理
国庆假期后的第一个工作日,简析MySQL事务实现原理,助你跟面试官对线不落下风~
0 0
MySQL进阶:MySQL事务(实践二)
MySQL 事务主要用于处理操作量大,复杂度高的数据。比方我想要删除一个用户(销户)以及这个用户的个人信息、订单信息以及其他信息,这里会涉及到很多SQL语句的执行来满足我们的业务需求,我们要一次性删除这些数据,这些数据库操作语句就构成了一个事务。 那么在MySQL数据库中,我们如何查看存储引擎是否支持事务呢?下面我就从我实验机器上的MySQL来一探究竟。
0 0
理解完这些基本上能解决面试中MySql的事务问题
在面试中,基本上都会问到关于数据库的事务问题,如果啥都不会或者只回答到表面的上知识点的话,那面试基本上是没戏了,为了能顺利通过面试,那MySql的事务问题就需要了解,所以就根据网上的资料总结一版Mysql事务的知识点,巩固一下事务的知识。
0 0
【MySQL】我这样分析MySQL中的事务,面试官对我刮目相看!!
相信大部分小伙伴在面试过程中,只会针对面试官提出的表面问题来进行回答。其实不然,面试官问的每一个问题都是经过深思熟虑的,面试的时间相对来说也是短暂的,面试官不可能在很短的时间内就对你非常了解,他想通过几个问题来考察你所掌握的知识的深度和广度,如果你只是回答面试官表面问你的问题,向挤牙膏一样,问一点,答一点,结果不用说,肯定是凉凉了。
0 0
Mysql-Gtid复制实战&问题总结
title: Mysql-Gtid复制实战&问题总结 date: 2019-07-23 10:15:45 categories: Mysql 实践一遍MYSQL的GTID复制,记录总结整个过程,包括: 手动配置GTID主备的方法,重要参数 GTID正常复制场景 GTID异常复制场景 如何修复GTID复制错误 GTID与备份恢复
1628 0
浅谈MySQL中的事务隔离级别(r11笔记第86天)
   之前写了一篇浅谈事务(一),算是对事务的一个基本认识,今天来简单总结一下事务的隔离级别,虽然是老掉牙的知识点,重温一下还是值得的。    在MySQL中基本有这两种事务隔离级别的设置,默认的RR(Repeatable-Read)和实际中常见的RC(Read-Committed)。
729 0
MySQL的一些常用的SQL语句整理
安装MySQL有两种的方式,一种是解压版本,但是需要配置环境变量,相对而言比较麻烦。所以我们一般采取第二种方式,那就是到MySQL的官网上下载安装版。这样就会省去很多麻烦,在这里我就不再详细的介绍具体的安装过程了,百度一下,Google,上面有手把手的教程。
635 0
+关注
摩云飞
十年磨一剑,我还差几年~~
文章
问答
文章排行榜
最热
最新
相关电子书
更多
PostgresChina2018_张启程_为什么我们抛弃MongoDB和MySQL,选择PgSQL
立即下载
MySQL表和索引优化实战
立即下载
MySQL查询优化实战
立即下载