理解 Mysql 事务隔离级别

多个事务并发的情况下，如果没有任何限制，一个事务的操作可能会影响另一个事务的执行结果，从而造成脏读、不可重复读、幻读等并发一致性问题。

并发一致性问题

脏读

脏读是指一个事务读取到另一个事务未提交的记录。比如：

1. 事务 A 对数据库的一条记录进行了修改，但是 还未提交

2. 此时事务 B 从数据库查询该条记录，查到了 事务 A 修改后的数据

3. 然后事务 A 因为某种原因回滚，导致修改未成功提交，该条记录还是原来的值

上述情况下，第 2 步事务 B 就存在脏读

不可重复读

不可重复读是指一个事务执行过程中，存在另外一个事务对数据进行了修改并提交，结果导致前一事务中两次读取到的值不一样。例如单条记录的情况：

1. 事务 A 读取了一条记录的值是 before

2. 事务 B 修改了该记录的值为 after，并且提交成功

3. 事务 A 再次读该记录，得到的值是 after

上述情况下，事务 A 在同一次事务中两次读取同一记录的值不一样，这就是不可重复读

幻读

幻读是指在一个事务的执行过程中，存在另外一个事务对数据集进行了修改（新增或删除了记录），结果导致这两个事务查询到的数据集不一致。比如说

1. 事务 A 根据主键 ID 统计 1 至 15 之间的记录数量，得到一个结果 15

2. 事务 B 成功删除了主键 ID 为 13 的记录，统计 1 至 15 之间的记录数量，结果为14

3. 此时事务 A 再次进行统计，结果还是 15

上述情况下，事务 A 第二次统计的结果不正确，在此基础上进行后续操作可能会出错

事务隔离

事务隔离用于解决事务的并发一致性问题，Mysql 提供了 4 种隔离级别，用于解决上文提到的问题。不同的隔离级别与一致性问题的关系如下表所示：

从上图可见级别从低到高为：未提交读 -> 提交读 -> 可重复读 -> 可串行化

未提交读（read uncommitted）

未提交读是最低的隔离级别，存在脏读情况，可以通过一个简单的例子验证下。先准备下测试用的数据

准备工作

当前使用的 mysql 版本如下

1
2

➜  ~ mysql --version
mysql  Ver 8.0.12 for osx10.14 on x86_64 (Homebrew)

首先创建一个测试用表 USER，存储引擎选择 InnoDB，注意不要使用 MyISAM 引擎，因为它不支持事务，测了也白测。

1
2
3
4
5
6

CREATE TABLE `USER` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `USERNAME` varchar(20) DEFAULT NULL,
  `USERID` int(11) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

新增两条测试数据，下面就操作这些数据验证并发问题

1
2
3
4

INSERT INTO `USER` (`ID`, `USERNAME`, `USERID`)
VALUES
	(1, 'admin', 1),
	(2, 'test', 2);

测试过程

按如下步骤操作，测试 read uncommitted 隔离级别下脏读问题。为方便说明问题，这里使用了表格，可按照序号往下看，主要关注事务1和事务2的执行命令

第 4 步 事务1 修改了 id = 2 的记录，尚未提交事务。

第 7 步 事务2 可以查询到 事务1 尚未提交的修改。第 8 步 事务1 回滚，此时 事务2 再次查询看到的是修改前的结果。

提交读（read committed）

脏读问题

read committed 事务隔离级别解决了脏读的问题，也就是不会读到其他事务尚未提交的对单条记录的修改。同样是上面的操作顺序，区别是隔离级别不同，下面看下执行结果

注意第 7 步，这次 事务2 不能看到 事务1 未提交的修改了。因为事务1 还未提交，所以 事务2 查询到的还是修改前的数据

不可重复读问题

read committed 事务隔离级别下还存在不可重复读问题。

下面在 事务1 中两次读取同一记录，在这之间通过 事务2 对该记录进行修改并提交

事务1 在第 3 步 和第 9 步分别查询了 id = 2 的记录，在这中间 事务2 修改了该条记录并提交成功，导致前者在一次事务中两次查询同一记录的结果不一致。

可重复读（repeatable read）

不可重复读问题

将事务隔离级别修改为 repeatable read，重复上次操作，再看下 事务1 的两次查询结果

注意第 3 步和第 9 步查询结果一致，这说明已经不存在「不可重复读」问题了

幻读问题

事务1 根据查询结果新增数据，在查询前 事务2 新增了一条记录

事务1 执行查询发现不存在 id = 3 的记录，然后执行新增操作，结果却失败了。

可串行化（serializable）

可串行化（serializable）级别下不存在幻读的问题。对于上面的情况，其结果是 事务2 在执行了第6步后进入等待，需 事务1 执行完后才能继续，也就是串行执行。

mysql命令

1. 查看系统变量

   1	show global variables \G

   找到事务隔离级别变量，当前隔离级别为可重复读

   1 2 3

   *************************** 518. row *************************** Variable_name: transaction_isolation Value: REPEATABLE-READ

2. 查看全局事务隔离级别

   1	select @@global.transaction_isolation;

   输出结果

   1 2 3 4 5 6

   +--------------------------------+ | @@global.transaction_isolation | +--------------------------------+ | REPEATABLE-READ | +--------------------------------+ 1 row in set (0.02 sec)

3. 查看 session 事务隔离级别

   1	select @@session.transaction_isolation;

   输出结果

   1 2 3 4 5 6

   +---------------------------------+ | @@session.transaction_isolation | +---------------------------------+ | READ-UNCOMMITTED | +---------------------------------+ 1 row in set (0.00 sec)

4. 设置事务隔离级别

   1	set session transaction isolation level read uncommitted;

参考

https://segmentfault.com/a/1190000016566788

BaronScbwartz, PeterZaitsev, VadimTkacbenko, 等. 高性能 MySQL