MySQL的事务相关设置

一、设置提交模式

默认情况下, MySQL启用自动提交模式（变量autocommit为ON）

由于变量autocommit分会话系统变量与全局系统变量，所以查询的时候，最好区别是会话系统变量还是全局系统变量。

mysql> show session variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.00 sec)
 
mysql> show global variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.00 sec)
 
mysql> 

Value的值为ON，表示autocommit开启。OFF表示autocommit关闭

修改autocommit模式

mysql> set session autocommit=0;
Query OK, 0 rows affected (0.00 sec)
 
mysql> show session variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+
1 row in set (0.00 sec)
 
mysql> show global variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.00 sec)
 
mysql> 
 
mysql> set global autocommit=0;
Query OK, 0 rows affected (0.00 sec)
 
mysql> show global variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+
1 row in set (0.01 sec)
 
mysql> 

注意，上述SQL修改会话系统变量或全局系统变量，只对当前实例有效，如果MySQL服务重启的话，这些设置就会丢失，如果要永久生效，就必须在配置文件中修改系统变量。

 

 

[mysqld]

autocommit=0

 

autocommit与显性事务的关系

对于显性事务start transaction或begin， 在自动提交模式关闭（关闭隐式提交）的情况下，开启一个事务上下文。首先数据库会隐式提交之前的还未被提交的操作，同时开启一个新事务。

使用START TRANSACTION，自动提交将保持禁用状态，直到你使用COMMIT或ROLLBACK结束事务。 自动提交模式然后恢复到之前的状态（如果start transaction 前 autocommit = 1，则完成本次事务后 autocommit 还是 1。如果 start transaction 前 autocommit = 0，则完成本次事务后autocommit 还是 0）

 

二、设置事务隔离级别

1. 首先说一下autocommit

默认情况下autocommit的开关是打开的，也就是ON，查看方法

方法1. select @@[global/session].autocommit; 
方法2. show [global/session] variables like 'autocommit'; 
其中global代表的是全局，session代表的是当前。可以不写global和session，默认似乎就是session了。

autocommit的作用

顾名思义，自动提交，在命令行执行sql语句的时候，每输入一行语句并执行实质上就是完成了一次事务，因为他自动commit了，当把autocommit关闭后，则执行的语句并未真实提交，在不显示执行commit命令前，其实都还在当前事务中，如果另开一个窗口执行sql语句，如果该窗口的隔离级别是read committed那么将看不到之前那个窗口(会话)中未提交的对表修改。

2. 设置隔离级别(不区分大小写)

1）read uncommitted : 读取尚未提交的数据 ：哪个问题都不能解决
2）read committed：读取已经提交的数据 ：可以解决脏读 ---- oracle默认的
3）repeatable read：重读读取：可以解决脏读 和 不可重复读 ---mysql默认的
4）serializable：串行化：可以解决 脏读 不可重复读 和 虚读---相当于锁表

用户可以用SET TRANSACTION语句改变单个会话或者所有新进连接的隔离级别。它的语法如下：

SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}
注意：默认的行为（不带session和global）是为下一个（未开始）事务设置隔离级别。如果你使用GLOBAL关键字，语句在全局对从那点开始创建的所有新连接（除了不存在的连接）设置默认事务级别。你需要SUPER权限来做这个。使用SESSION 关键字为将来在当前连接上执行的事务设置默认事务级别。 任何客户端都能自由改变会话隔离级别（甚至在事务的中间），或者为下一个事务设置隔离级别。

你可以用下列语句查询全局和会话事务隔离级别：

SELECT @@global.tx_isolation; 
SELECT @@session.tx_isolation; 
SELECT @@tx_isolation;