关闭全局mysql自动提交_Mysql 全局关闭自动提交

SESSION 1:

mysql> select * from t1;

+------+

| id |

+------+

| 1 |

+------+

1 row in set (0.08 sec)

mysql> insert into t1 values(2);

Query OK, 1 row affected (0.60 sec)

SESSION 2:

mysql> select * from t1;

+------+

| id |

+------+

| 1 |

+------+

1 row in set (0.00 sec)

mysql> select * from t1;

+------+

| id |

+------+

| 1 |

| 2 |

+------+

2 rows in set (0.00 sec)

此时是自动提交:

添加参数:

autocommit =0

[mysql@master ~]$ mysql -uroot -p123456

Warning: Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 2

Server version: 5.6.16-log Source distribution

Copyright (c) 2000, 2014, 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> show variables like 'autocommit';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| autocommit | OFF |

+---------------+-------+

1 row in set (0.00 sec)

[mysql@master ~]$ mysql -utest -p123456

Warning: Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 3

Server version: 5.6.16-log Source distribution

Copyright (c) 2000, 2014, 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> show variables like 'autocommit';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| autocommit | OFF |

+---------------+-------+

1 row in set (0.00 sec)

参数已经生效:

SESSION 1:

mysql> select * from t1;

+------+

| id |

+------+

| 1 |

| 2 |

+------+

2 rows in set (0.00 sec)

mysql> insert into t1 values(3);

Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;

+------+

| id |

+------+

| 1 |

| 2 |

| 3 |

+------+

3 rows in set (0.00 sec)

SESSION 2:

mysql> select * from t1;

+------+

| id |

+------+

| 1 |

| 2 |

+------+

2 rows in set (0.00 sec)

更新测试:

SESSION 1：

mysql> update t1 set id=100 where id=1;

Query OK, 1 row affected (0.22 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from t1;

+------+

| id |

+------+

| 100 |

| 2 |

+------+

2 rows in set (0.00 sec)

SESSION 2：

mysql> select * from t1;

+------+

| id |

+------+

| 1 |

| 2 |

+------+

2 rows in set (0.00 sec)