实践MySQL事务隔离级别

本文通过实践，来学习下MySQL的事务隔离级别。

0. 测试表准备

首先创建一张用于测试的表tb_test：

CREATE TABLE `tb_test` (
  `id` int(11) NOT NULL,
  `text` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

然后插入3条测试记录：

INSERT INTO `tb_test`(`text`) VALUES ('first row');
INSERT INTO `tb_test`(`text`) VALUES ('second row');
INSERT INTO `tb_test`(`text`) VALUES ('third row');

1. READ UNCOMMITTED级别

开启终端A，将session的隔离级别设置为READ UNCOMMITTED级别，使用BEGIN语句开启一个事务，然后读取id=1的记录：

-- 终端A
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN;
select * from tb_test where id = 1;

查询结果：

然后开启终端B，使用BEGIN语句开启一个事务，并更新id=1的记录的text字段：

-- 终端B
BEGIN;
UPDATE TB_TEST SET TEXT = '1 row' WHERE ID = 1;

可看到执行成功：

切换回终端A，然后重新查询id=1的记录：

-- 终端A
-- SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- BEGIN;
-- select * from tb_test where id = 1;
select * from tb_test where id = 1;

查询结果如下：

可以看到，虽然终端B中的事务尚未最终提交，但是也查询到了新的字段值，这就是脏读。这是非常危险的一种模式，因为终端B中的事务有可能会滚，这时就存在数据不一致性。

扫尾工作，将终端A和终端B的事务都正式提交：

-- 终端A
-- SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- BEGIN;
-- select * from tb_test where id = 1;
-- select * from tb_test where id = 1;
COMMIT;

-- 终端B
-- BEGIN;
-- UPDATE TB_TEST SET TEXT = '1 row' WHERE ID = 1;
COMMIT;

总结：

为了解决脏读问题，我们可以将隔离级别提高到READ COMITTED级别。

2. READ COMMITTED级别

在进行该隔离级别测试之前，我们先看下当前表中的数据现状：

下面开始进行测试验证。

打开终端A，将该session的隔离级别设置为READ COMMITTED，然后执行查询：

-- 终端A
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT * FROM tb_test;

查询结果如下：

然后打开终端B，更新id=1的记录的字段text的值：

-- 终端B
BEGIN;
UPDATE TB_TEST SET TEXT = 'first row' WHERE ID = 1;

可以看到执行成功：

切换回终端A，重新查询：

-- 终端A
-- SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- SELECT * FROM tb_test;
SELECT * FROM tb_test;

查询结果如下：

可以看到终端B中的更新未查询出来。现在我们将终端B的事务提交：

-- 终端B
-- BEGIN;
-- UPDATE TB_TEST SET TEXT = 'first row' WHERE ID = 1;
COMMIT;

然后再切换回终端A重新查询：

-- 终端A
-- SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- SELECT * FROM tb_test;
-- SELECT * FROM tb_test;
SELECT * FROM tb_test;

可以看到查出了终端B提交的更新：

可以看出来，READ COMMITTED隔离级别能避免脏读，但是也存在另一个问题，就是终端A的这个事务，在同一个事务内进行的相同查询，查询出来的结果会不一样。这种不可重复读一般称为幻读，这种模式在某些业务场景下也是难以接受的。

扫尾工作，将终端A和终端B的事务都正式提交：

-- 终端A
-- SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- SELECT * FROM tb_test;
-- SELECT * FROM tb_test;
-- SELECT * FROM tb_test;
COMMIT;

-- 终端B
-- BEGIN;
-- UPDATE TB_TEST SET TEXT = 'first row' WHERE ID = 1;
COMMIT;

总结：

1. 在MySQL底层，脏读问题到底是如何解决的？为什么无法避免幻读？

   上面的SELECT * FROM tb_test;语句其实是一种普通的无锁读语句，在MySQL官方文档中称为Consistent Nonlocking Reads，这种语句防止脏读的原理是基于MVCC，即多版本并发控制，简单讲就是每个提交的事务都对应一个版本。而在READ COMMITTED的隔离级别中，每次都读取最近已完成提交的那次事务快照，即可保证避免脏读。但是由于每次执行这种SELECT * FROM tb_test;时，都是读取的最新的已提交事务快照，因此无法避免幻读。

2. 对于READ COMMITTED隔离级别，事务内部执行的语句除了普通的无锁读，肯定还存在带锁读，MySQL官方文档中称为locking reads，比如SELECT FOR UPDATE语句、UPDATE语句、DELETE语句，这种带锁读对阻塞其他事务的INSERT么？

   答案是不阻塞，因为该隔离级别未开启间隙锁，也就是说不阻塞其他事务在查询的记录间隙(比如SELECT * FROM tb_test where id >= 10 and id <=20 FOR UPDATE;，这里的间隙就是10~20)插入新记录。这里之所以提到这一点是为了跟后面的REPEATABLE READ做对比，此隔离级别是开启间隙锁的，也就是防止在间隙内插入记录，这样的好处是可以避免幻读问题。

3. 为了解决幻读的问题，我们可以将事务隔离级别继续升级为REPEATABLE READ。

3. REPEATABLE READ级别

SQL标准中REPEATABLE READ是不要求防止幻读的，但是MySQL实现的更严格一些，做到了防止幻读。

幻读定义：事务A 按照一定条件进行数据读取， 期间事务B 插入了相同搜索条件的新数据，事务A再次按照原先条件进行读取时，发现了事务B 新插入的数据称为幻读。如果事务A 按一定条件搜索， 期间事务B 删除了符合条件的某一条数据，导致事务A 再次读取时数据少了一条，这种情况归为不可重复读，不归为幻读。

开始测试前，我们先准备下测试数据：

下面我们开始测试。打开终端A，将事务调整到REPEATABLE READ级别，并执行如下查询：

-- 终端A
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
SELECT * FROM tb_test where id >= 10 and id <=20;

注意我这里特意查询了一个范围：id >= 10 and id <=20。查询结果如下：

然后切换到终点B，插入一条id=15的记录：

-- 终端B
BEGIN;
INSERT INTO `tb_test`(`id`, `text`) VALUES (15, 'inserted row');
COMMIT;

可以看到执行成功：

然后切换回终端A，重新执行前面查询：

-- 终端A
-- SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- BEGIN;
-- SELECT * FROM tb_test where id >= 10 and id <=20;
SELECT * FROM tb_test where id >= 10 and id <=20;

查询结果如下，没有变化：

可见避免了幻读。那么我们在终端B中如果删除id=20的记录，那么终端A能查到吗？可以继续试试。切换到终端B执行：

-- 终端B
BEGIN;
DELETE FROM tb_test where id = 20;
COMMIT;

再切换到终端A执行：

-- 终端A
-- SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- BEGIN;
-- SELECT * FROM tb_test where id >= 10 and id <=20;
-- SELECT * FROM tb_test where id >= 10 and id <=20;
SELECT * FROM tb_test where id >= 10 and id <=20;

查询结果依然没有变化：

可见也避免了删除场景的不可重复读问题。

扫尾工作：将终端A的事务进行提交。

-- 终端A
-- SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- BEGIN;
-- SELECT * FROM tb_test where id >= 10 and id <=20;
-- SELECT * FROM tb_test where id >= 10 and id <=20;
-- SELECT * FROM tb_test where id >= 10 and id <=20;
COMMIT;

然后执行查询SELECT * FROM tb_test where id >= 10 and id <=20;看到了终端B的更新记录：

总结：

1. MySQL REPEATABLE READ隔离级别如何防止脏读和幻读？

   对于普通的无锁读语句， REPEATABLE READ隔离级别防止脏读的原理和REPEATABLE READ是一样的，都是基于MVCC，只不过 REPEATABLE READ事务内的SELECT语句每次不是读取最新的已提交快照，而是读取的第一次已提交快照，因此也防止了幻读。

2. 对于REPEATABLE READ隔离级别，事务内部执行的语句除了普通的无锁读，肯定也还存在带锁读，比如SELECT FOR UPDATE语句、UPDATE语句、DELETE语句，这种带锁读对阻塞其他事务的INSERT么？

   答案是阻塞，该隔离级别开启了间隙锁，因此会阻塞对SELECT FOR UPDATE语句选中的记录进行更新、删除，阻塞对选中的范围间隙进行插入。

参考资料：

1. dev.mysql.com/doc/refman/…
2. medium.com/@huynhquang…
3. blog.pythian.com/understandi…
4. dev.mysql.com/doc/refman/…