[Arrangement] MySQL autocommit_MySQL

[Arrangement] MySQL autocommit bitsCN.com

[Arrangement] MySQL autocommit

By default, mysql enables auto commit. You can run the following command to view the settings at the session level and global level:

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>

So how can we disable autocommit if we don't want mysql to execute automatic submission? You can set it through init_connect available on Cmd-Line, Option file, and System Var.

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.

The above section means that when each client is connected, the server will execute an SQL string specified by init_connect. (Can it be considered session-based ?)

With this variable, you can disable autocommit as follows:

Method 1:

1

Mysql> set global init_connect = 'set autocommit = 0 ';

Method 2:

Set in the MySQL configuration file

1

[Mysqld]

2

Init_connect = 'set autocommit = 0'

Method 3:

Start mysql with the command line parameter-init_connect = 'set autocommit = 0'

It is worth noting that the setting of this parameter is invalid for users with super permissions. the specific reasons are as follows:

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.

The autocommit enabled by default will certainly have a certain impact on mysql performance, but since it is enabled by default, there must be a reason, so if you don't know what problems you will encounter, do not set this as a wonderful one. For example, we will illustrate the performance impact of enabling autocommit. if you insert 1000 pieces of data, mysql will commit 1000 times. if we close autocommit and use programs to control it, only one commit is required.

============ I am a split line ============

A. initial state + set the session-level autocommit to 0

01 mysql> 02 mysql> show binlog events; 03 + users + ----- + ------------- + ----------- + users + 04 | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | 05 + users + ----- + ------------- + ----------- + --------------- ------------------------------------- + 06 | mysql-bin.000001 | 4 | Format_desc | 1 | 120 | Server ver: 5.6.10-log, Binlog v Er: 4 | 07 + ---------------- + ----- + ------------- + ----------- + ------------- + accept + 081 row in set (0.00 sec) 0910 mysql> 11 mysql> show tables; 12 Empty set (0.00 sec) 1314 mysql> 15 mysql> select @ global. autocommit; 16 + ------------------- + 17 | @ global. autocommit | 18 + ------------------- + 19 | 1 | 20 + ------------------- + 211 row in set (0.00 sec) 2223 mysql> select @ session. a Utocommit; 24 + -------------------- + 25 | @ session. autocommit | 26 + -------------------- + 27 | 1 | 28 + -------------------- + 291 row in set (0.00 sec) 3031 mysql> 32 mysql> set autocommit = 0; 33 Query OK, 0 rows affected (0.00 sec) 3435 mysql> 36 mysql> select @ global. autocommit; 37 + ------------------- + 38 | @ global. autocommit | 39 + ------------------- + 40 | 1 | 41 + --------------------- + 421 row in set (0.00 sec) 4 344 mysql> select @ session. autocommit; 45 + -------------------- + 46 | @ session. autocommit | 47 + -------------------- + 48 | 0 | 49 + -------------------- + 501 row in set (0.00 sec) 5152 mysql> B. create a test table 01 mysql> 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; 06 Query OK, 0 rows affected (0.01 sec) 0708 mysql> 09my SQL> show tables; 10 + -------------- + 11 | Tables_in_test | 12 + ---------------- + 13 | t_autocommit | 14 + -------------- + 151 row in set (0.00 sec) 1617 mysql> 18 mysql> describe t_autocommit; 19 + -------- + --------- + ------ + ----- + --------- + ---------------- + 20 | Field | Type | Null | Key | Default | Extra | 21 + -------- + --------- + ------ + ----- + --------- + ---------------- + 22 | id | int (11) | NO | PRI | NULL | auto_incr Ement | 23 | amount | int (11) | NO | 0 | 24 + -------- + --------- + ------ + ----- + --------- + ---------------- + 252 rows in set (0.00 sec) 2627 mysql> 28 mysql> select * from t_autocommit; 29 Empty set (0.00 sec) 3031 mysql> c. insert data 01 mysql> 02 mysql> insert into t_autocommit set amount = 1; 03 Query OK, 1 row affected (0.00 sec) 0405 mysql> 06 mysql> select * from t_autocommit; 07 + ---- + -------- + 08 | id | amount | 09 + ---- + --- ----- + 10 | 1 | 1 | 11 + ---- + -------- + 121 row in set (0.00 sec) 1314 mysql> 15 mysql> update t_autocommit set amount = amount + 10; 16 Query OK, 1 row affected (0.00 sec) 17 Rows matched: 1 Changed: 1 Warnings: 01819 mysql> 20 mysql> select * from t_autocommit; 21 + ---- + -------- + 22 | id | amount | 23 + ---- + -------- + 24 | 1 | 11 | 25 + ---- + -------- + 261 row in set (0.00 sec) 2728 mysql> 29 mysql> show binlog events; 30 + ----------- ------- + ----- + ------------- + ----------- + 31 | Log_name | Pos | Event_type | Server_id | region | Info | 32 + Region + ----- + ------------- + ----------- + region --------------------------------------------------------------------------- ----------------------------------------------------------- + 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, 36 amount int not null default '0 ', 37 primary key (id) 38) engine = innodb | 39 + -------------------- + ----- + ------------- + ----------- + ------- ------ + Rows --------- + 402 rows in set (0.00 sec) 4142 mysql> The binlog only records the create table action, insert and update are not recorded in binlog because autocommit is 0. D. Disconnect mysql and try again. 01 mysql> 02 mysql> quit03Bye04 [root @ Betty ~] #05 [root @ Betty ~] # Mysql-u root-p06Enter password: 07 Welcome to the MySQL monitor. commands end with; or/g.08Your MySQL connection id is 309 Server version: 5.6.10-log Source distribution1011Copyright (c) 2000,201 1, 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. 1617 Type 'Help; 'or'/h' for help. type '/C' to clear the current input statement.1819mysql> 20 mysql> use test; 21 Reading table information for completion of table and column names22You can turn off this feature to get a quicker startup with-A2324Database changed25mysql> 26 mysql> show tables; 27 + ---------------- + 28 | Tables_in_test | 29 + ---------------- + 30 | t_autocommit | 31 + ---------------- + 321 row in set (0.0 0 sec) 3334 mysql> 35 mysql> select * from t_autocommit; 36 Empty set (0.00 sec) 3738 mysql> no data is found. Why? Because SQL statements are not submitted to the server for processing by themselves (the current session), they are only processed in the current connection. Repeat the experiment above, but keep the default value of autocommit (1 ). 001 mysql> 002 mysql> show binlog events; 003 + hour + ----- + ------------- + ----------- + hour + 004 | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | 005 + hour + ----- + ------------- + ----------- + --------------- ------------------------------------- + 006 | mysql-bin.000001 | 4 | Format_desc | 1 | 120 | Server ver: 5.6.10-log, B Inlog ver: 4 | 007 + ------------------ + ----- + ------------- + ----------- + ------------- + rows + 0081 row in set (0.00 sec) 009010 mysql> 011 mysql> show tables; 012 Empty set (0.01 sec) 013014 mysql> 015 mysql> select @ global. autocommit; 016 + ------------------- + 017 | @ global. autocommit | 018 + ------------------- + 019 | 1 | 020 + --------------------- + 0211 row in set (0.00 sec) 022023 Mysql> 024 mysql> select @ session. autocommit; 025 + -------------------- + 026 | @ session. autocommit | 027 + -------------------- + 028 | 1 | 029 + ---------------------- + 0301 row in set (0.00 sec) 031032 mysql> 033 mysql> 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; 038 Query OK, 0 rows affected (0.01 sec) 03904 0 mysql> 041 mysql> show tables; 042 + -------------- + 043 | Tables_in_test | 044 + ---------------- + 045 | t_autocommit | 046 + ---------------- + 0471 row in set (0.00 sec) 048049 mysql> 050 mysql> 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) 058059 mysql> 060 mysql> insert into t_autocommit set amount = 1; 061 Query OK, 1 row affected (0.00 sec) 062063 mysql> 064 mysql> select * from t_autocommit; 065 + ---- + -------- + 066 | id | amount | 067 + ---- + -------- + 068 | 1 | 1 | 069 + ---- + -------- + 0701 row in Set (0.00 sec) 071072 mysql> 073 mysql> update t_autocommit set amount = amount + 10; 074 Query OK, 1 row affected (0.00 sec) 075 Rows matched: 1 Changed: 1 Warnings: 0076077 mysql> 078 mysql> select * from t_autocommit; 079 + ---- + -------- + 080 | id | amount | 081 + ---- + -------- + 082 | 1 | 11 | 083 + ---- + -------- + 0841 row in set (0.00 sec) 085086 mysql> 087 mysql> show binlog events; 088 + ------------------ + ----- + ------------ -+ ----------- + ------------- + --------- + 089 | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | 090 + Region + ----- + ------------- + ----------- + ------------- + region --------------------------------------------------------------------------------------------------- ----------------------------------- + 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, 094 amount int not null default '0 ', 095 primary 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) 106107 mysql> 108 mysql> quit1_bye110 [root @ Betty ~] #111 [root @ Betty ~] # Mysql-u root-p0000enter password: 113 Welcome to the MySQL monitor. commands end with; or/g.114Your MySQL connection id is 4115 Server version: 5.6.10-log Source distribution1_117copyright (c) 2000,201 1, 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. 122123 Type 'help; 'or'/h' for help. type '/C' to clear the current input statement.124125mysql> 126 mysql> use test; 127 Reading table information for completion of table and column names128You can turn off this feature to get a quicker startup with-A129130Database changed1_mysql> 132 mysql> show tables; 133 + ---------------- + 134 | Tables_in_test | 135 + ---------------- + 136 | t_autocommit | 137 + ---------------- + 1381 row in set (0.00 sec) 139140 mysql> 141 mysql> select * from t_autocommit; 142 + ---- + -------- + 143 | id | amount | 144 + ---- + -------- + 145 | 1 | 11 | 146 + ---- + -------- + 1471 row in set (0.00 sec) 148149 mysql> 150 mysql>

This time, all of them will be available.

============ I am a split line ============

Internet saying:

Do not set autocommit to keep it in the default state of autocommit = 1. Generally, you need to obtain the latest data for queries/updates. you do not need to start a transaction at all, unless you need to enable the transaction in a specific situation, and then manually start transaction... commit/rollback.

This global setting does not make much sense in the production environment. Generally, the autocommit is set to ON/OFF in the application framework (such as the database in the connection pool). to put it bluntly, after the database connection is obtained, set autocommit on/off (or = 1/0)

BitsCN.com