关于 MySQL 四种事务隔离级别的设置与简要说明

1 摘要

关于 MySQL 的事务隔离级别，是面试中经常问到的问题(虽然大多数项目中都用不到)，作者在这里整理了一些笔记，仅供参考。

​

2 MySQL 事务隔离级别划分

数据库事务隔离级别分为四个等级,分别为:未提交读( read-uncommitted)、提交读(read committed)、可重复读(repeatable read)、串行序列化(serializable)

事务等级与可能出现的异常:

事务等级	脏读	不可重复读	幻读
read uncommitted	√	√	√
read committed	X	√	√
repeatable read	X	X	√
serializable	X	X	X

从上到下，事务的隔离级别逐渐增高；事务的隔离级别越高，程序执行效率越低。

MySQL 的默认事物隔离级别为 repeadable read

名词解释:

脏读: 在一个事务中读取到了另一个未提交事务的数据，即为脏读

不可重读读: 在一个事务处理过程中，另一个事物插入进来，并更新了数据，原先的事务前后两次相同的语句查询，结果不一样，即为不可重复读

幻读: 在一个事物处理过程中，另一个事务插入进来，并更新了数据，此时原来的事务也插入数据，则实际上会受到后一个事务数据的影响，即为幻读。

​

3 MySQL 事务隔离级别的查询与设置

-- 查询 mysql 版本
SELECT VERSION();

-- 查询 mysql 事务隔离级别(5.7 及以下)
SELECT @@tx_isolation;

-- 查询 mysql 事务隔离级别(5.7+)
SELECT @@transaction_isolation;

设置 MySQL 事务隔离级别:

SET {SESSION | GLOBAL} TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE};

SESSION: 为当前会话

GLOBAL: 为本次连接所有会话

示例:

-- 设置当前会话的事务隔离级别为提交读(`READ COMMITTED`)  
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

无论是 SESSION 还是 GLOBAL 级别，断开 MySQL 连接,下次再连接的时候,还是原来的事务隔离级别,因此彻底更新MySQL 事务隔离级别，需要修改 MySQL 配置文件

Linux 系统下 MySQL 配置文件位置为: /etc/my.conf

在 [mysqld] 标签下配置(在其他标签下设置无效,如果没有该标签则手动添加):

[mysqld]
transaction-isolation = READ-COMMITTED

在配置文件中设置的事务隔离级别为: READ-UNCOMMITTED、READ-COMMITTED、REPEATABLE-READ、SERIALEZABLE

配置文件修改之后需要重启 MySQL 服务

​

4 事务隔离演示准备

4.1 数据准备

数据库表

-- 用户信息表
DROP TABLE IF EXISTS `user_info`;
CREATE TABLE `user_info` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id 主键',
  `user_name` varchar(30) DEFAULT '' COMMENT '用户名',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户信息表';

创建测试数据

-- 批量插入用户信息
INSERT INTO `user_info`(`user_name`) VALUES ('张三'),
    ('李四'),
		('王五');    

​

4.2 MySQL 事务开启与回滚命令

-- 开启事务
start transaction;
-- do sometring

-- 提交事务
commit;

-- 开启事务
start transaction;
-- do sometring

-- 回滚事务
rollback;

​

5 不同事务隔离级别下异常演示

5.1 事务隔离级别为未提交读(read uncommitted)

会话1	会话2
设置当前会话事务隔离级别: set session transaction isolation level read uncommitted;
查询当前会话事务隔离级别: select @@tx_isolation; 结果为: READ-UNCOMMITTED
	设置当前会话事务隔离级别: set session transaction isolation level read uncommitted;
	查询当前会话事务隔离级别: select @@tx_isolation; 结果为: READ-UNCOMMITTED
开启事务: start transaction;
查询用户信息表所有数据: select * from user_info; 查询结果为:
	开启事务: start transaction;
	向用户信息表插入一条数据: insert into user_info(user_name) values('read Uncommitted1');
查询用户信息表所有数据: select * from user_info; 查询结果为:

此时，会话1中查出了会话2未提交的数据，即为脏读

​

5.2 事务隔离级别为提交读(read committed)

会话1	会话2
设置当前会话事务隔离级别: set session transaction isolation level read committed;
查询当前会话事务隔离级别: select @@tx_isolation; 结果为: READ-COMMITTED
	设置当前会话事务隔离级别: set session transaction isolation level read committed;
	查询当前会话事务隔离级别: select @@tx_isolation; 结果为: READ-COMMITTED
开启事务: start transaction;
查询用户信息表所有数据: select * from user_info; 查询结果为:
	开启事务: start transaction;
	向用户信息表插入一条数据: insert into user_info(user_name) values('read Committed1');
查询用户信息表所有数据: select * from user_info; 查询结果为:

此时会话1中没有查询到会话2中未提交的数据，即没有出现脏读。

继续试验

会话1	会话2
	提交事务: commit;
查询用户信息表所有数据: select * from user_info; 查询结果为:

此时，会话1中查询到了会话2中提交的数据，会话1同一个事务中前后进行了两次相同的查询，但是结果却不一致，即为不可重复读。

​

5.3 事务隔离级别为可重复读(repeatable read)

会话1	会话2
设置当前会话事务隔离级别: set session transaction isolation level repeatable read;
查询当前会话事务隔离级别: select @@tx_isolation; 结果为: REPEATABLE-READ
	设置当前会话事务隔离级别: set session transaction isolation level repeatable read;
	查询当前会话事务隔离级别: select @@tx_isolation; 结果为: REPEATABLE-READ
开启事务: start transaction;
查询用户信息表所有数据: select * from user_info; 查询结果为:
	开启事务: start transaction;
	向用户信息表插入一条数据: insert into user_info(id,user_name) values(6,'repeatable read1');
查询用户信息表所有数据: select * from user_info; 查询结果为:

此时会话1中没有查询到会话2中未提交的数据，即没有出现脏读。

继续试验

会话1	会话2
	提交事务: commit;
查询用户信息表所有数据: select * from user_info; 查询结果为:

此时，会话1中还是没有查询到会话2的事务提交的数据，即没有出现不可重复读的问题。

继续试验

会话1	会话2
向用户信息表插入一条数据: insert into user_info(id,user_name) values(6,'repeatable read1'); 提示错误: ERROR 1062 (23000): Duplicate entry '6' for key 'PRIMARY'

会话1中没有查询到 id 为 6 的数据，但是插入一条 id 为 6 的数据，却提示插入失败，是因为在会话 2 中已经插入了一条 id 为 6 的数据，这种现象即为幻读。

repeadable read 事务隔离级别虽然解决了可重复读的问题，但是其他事务已经插入的数据仍然会对当前事务造成影响，即会出现幻读问题。

​

5.4 事务隔离级别为序列化(serializable)

会话1	会话2
设置当前会话事务隔离级别: set session transaction isolation level serializable;
查询当前会话事务隔离级别: select @@tx_isolation; 结果为: SERIALIZABLE
	设置当前会话事务隔离级别: set session transaction isolation level serializable;
	查询当前会话事务隔离级别: select @@tx_isolation; 结果为: SERIALIZABLE
开启事务: start transaction;
查询用户信息表所有数据: select * from user_info; 查询结果为:
	开启事务: start transaction;
	向用户信息表插入一条数据: insert into user_info(id,user_name) values(7,'seiralizable1'); 结果为: 线程阻塞,等待提交 等待一段时间后提示: ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
提交事务: commit;
	向用户信息表插入一条数据: insert into user_info(id,user_name) values(7,'seiralizable1'); 提示插入成功
	提交事务: commit; 也提示成功

从示例中可以看出，当数据库事务隔离级别设置为序列化(serializable)时，可以同时开启多个事务，但是每次只能由一个事务进行提交，而且事务提交顺序遵循先进先出原则(谁先开启的事务，谁就能先提交，后开启的时候必须等待前边的事务提交之后才能提交)，如果后开启的事务等待超时，会导致提交失败，并重新开启事务

​

6 参考资料推荐

MySQL 四种事务隔离级的说明

MySQL的rollback–事务回滚