【整理】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)

时间： 2024-04-12 14:41:24

【整理】MySQL 之 autocommit的相关文章

mysql禁用autocommit,以及遇到的问题（转）

MySQL的autocommit(自动提交)默认是开启,其对mysql的性能有一定影响,举个例子来说,如果你插入了1000条数据,mysql会commit1000次的,如果我们把autocommit关闭掉,通过程序来控制,只要一次commit就可以了.   1,我们可以通过set来设置autocommit 查看复制打印? mysql> set global init_connect="set autocommit=0";  //提示你用权限更高的财户来设置   ERROR 122

简单整理MySQL的日志操作命令_Mysql

1.首先确认你日志是否启用了 MySQL>show variables like 'log_bin'; 如果启用了,即ON那日志文件就在MySQL的安装目录的data目录下 2.怎样知道当前的日志 MySQL> show master status; 3.看二进制日志文件用MySQLbinlog shell>MySQLbinlog mail-bin.000001 或者 shell>MySQLbinlog mail-bin.000001 | tail 4.正确删除MySQL BIN-

整理MySql常用查询语句（23种)_MsSql

废话不多了,直接贴代码了 一查询数值型数据: SELECT * FROM tb_name WHERE sum > 100;  查询谓词:>,=,<,<>,!=,!>,!<,=>,=< 二查询字符串 SELECT * FROM tb_stu WHERE sname = '小刘' SELECT * FROM tb_stu WHERE sname like '刘%' SELECT * FROM tb_stu WHERE sname like '%程序员' S

整理MySql常用查询语句（23种)

废话不多了,直接贴代码了 一查询数值型数据: SELECT * FROM tb_name WHERE sum > 100; 查询谓词:>,=,<,<>,!=,!>,!<,=>,=< 二查询字符串 SELECT * FROM tb_stu WHERE sname = '小刘' SELECT * FROM tb_stu WHERE sname like '刘%' SELECT * FROM tb_stu WHERE sname like '%程序员' SE

汇总整理MYSQL相关操作命令_Mysql

测试环境:mysql 5.0.45 [注:可以在mysql中通过mysql> SELECT VERSION();来查看数据库版本] 一.连接MYSQL. 格式: mysql -h主机地址 -u用户名 -p用户密码 1.连接到本机上的MYSQL. 首先打开DOS窗口,然后进入目录mysql\bin,再键入命令mysql -u root -p,回车后提示你输密码.注意用户名前可以有空格也可以没有空格,但是密码前必须没有空格,否则让你重新输入密码. 如果刚安装好MYSQL,超级用户root是没有密码的

mysql autocommit对myisam,innodb的性能影响

前段时间把数据库的部分myisam表转变成了innodb了,感觉慢了好多.我知道autocommit对innodb性能有一定的影响,但不知道影响有这么大.如何关闭autocommit,请参考MySQL禁用autocommit,以及遇到的问题 ,为了解决这个问题,我做了一些测试,包括autocommit对myisam,innodb影响.   一,测试autocommit对myisam的影响 1,准备测试表和数据 Java代码   mysql>  CREATE TABLE `test_test` (

【Mysql】—— 报错：Can&#039;t call commit when autocommit=true

java.sql.SQLException: Can't call commit when autocommit=true at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:934) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:931) at com.mysql.jdbc.ConnectionImpl.commit(ConnectionImpl.jav

MySQL数据库之锁的详解

锁是计算机协调多个进程或线程并发访问某一资源的机制.在数据库中,除传统的计算资源(如CPU.RAM.I/O等)的争用以外,数据也是一种供许多用户共享的资源.如何保证数据并发访问的一致性.有效性是所有数据库必须解决的一个问题,锁冲突也是影响数据库并发访问性能的一个重要因素.从这个角度来说,锁对数据库而言显得尤其重要,也更加复杂.本章我们着重讨论MySQL锁机制的特点,常见的锁问题,以及解决MySQL锁问题的一些方法或建议. MySQL锁概述 相对其他数据库而言,MySQL的锁机制比较简单,其最显著

MySQL详解－－锁，事务

http://www.cnblogs.com/jukan/p/5670950.html http://blog.csdn.net/xifeijian/article/details/20313977 锁是计算机协调多个进程或线程并发访问某一资源的机制.在数据库中,除传统的计算资源(如CPU.RAM.I/O等)的争用以外,数据也是一种 供许多用户共享的资源.如何保证数据并发访问的一致性.有效性是所有数据库必须解决的一个问题,锁冲突也是影响数据库并发访问性能的一个重要因素.从这个 角度来说,锁对数据