MySQL SERIALIZABLE隔离级别为何仍出现不可重复读与幻读？

我为验证MySQL的SERIALIZABLE隔离级别是否能防止不可重复读和幻读做了实验，但结果出乎意料——两种事务异常似乎都出现了。

相关概念定义 #

• 不可重复读：同一事务内，先后两次读取同一数据，结果不一致（其他已提交事务修改了该数据）。
• 幻读：同一事务内，先后两次执行相同范围的查询，结果集行数不一致（其他已提交事务插入或删除了符合条件的行）。

实验环境 #

• MySQL版本：8.0.30
• 事务隔离级别：全局与会话均设置为SERIALIZABLE
• 自动提交：默认开启autocommit
• 存储引擎：InnoDB
• 测试表：person表（含id、name字段），初始数据：
  • id=1，name=John
  • id=2，name=David

不可重复读实验步骤（两个命令行窗口模拟T1、T2事务） #

1. T1执行BEGIN;启动事务
2. T2执行BEGIN;启动事务
3. T1执行UPDATE person SET name = 'Tom' WHERE id = 2;，将id=2的name更新为Tom
4. T2执行SELECT * FROM person WHERE id = 2;，无法读取，处于等待状态
5. T1执行COMMIT;提交事务
6. T2再次执行SELECT * FROM person WHERE id = 2;，读取到name为Tom（而非初始的David），出现不可重复读假象
7. T2执行COMMIT;提交事务

幻读实验步骤（两个命令行窗口模拟T1、T2事务） #

1. T1执行BEGIN;启动事务
2. T2执行BEGIN;启动事务
3. T1执行INSERT INTO person VALUES (3, 'Tom');，插入新行
4. T2执行SELECT * FROM person;，无法读取，处于等待状态
5. T1执行COMMIT;提交事务
6. T2再次执行SELECT * FROM person;，读取到3行数据（而非初始的2行），出现幻读假象
7. T2执行COMMIT;提交事务

问题解答 #

为什么会出现这种假象？ #

你的实验步骤顺序有误，导致观察到的并非真正的不可重复读或幻读：

1. 不可重复读场景的错误：标准的不可重复读需要同一事务内先读取到旧数据，再读取到新数据。但实验中，T2的第一次SELECT被T1的UPDATE持有的排他锁阻塞，直到T1提交后才执行，第一次读取就直接获取了T1修改后的新数据，两次读取结果一致，并不符合不可重复读的定义。
2. 幻读场景的错误：同理，T2的第一次SELECT被T1的INSERT阻塞，直到T1提交后才读取到新插入的数据，两次读取结果一致，也不是真正的幻读。

InnoDB的SERIALIZABLE隔离级别如何防止这两种异常？ #

InnoDB在SERIALIZABLE隔离级别下，会对普通SELECT语句隐式添加LOCK IN SHARE MODE（共享锁），对于范围查询还会添加间隙锁，从而避免其他事务修改或插入符合查询条件的数据：

• 防止不可重复读：如果T2先执行SELECT * FROM person WHERE id = 2;，会对该行加共享锁，此时T1执行UPDATE会被阻塞，直到T2提交或回滚。T2在事务内多次读取该行，都会得到初始的David，不会出现不可重复读。
• 防止幻读：如果T2先执行SELECT * FROM person;，会对整个表加范围锁（包括间隙），T1执行INSERT会被阻塞，直到T2提交。T2在事务内多次执行该查询，都会得到初始的2行数据，不会出现幻读。

总结 #

你的实验因为步骤顺序错误，导致观察到的是锁等待后的正常读取结果，而非SERIALIZABLE隔离级别失效。实际上，InnoDB的SERIALIZABLE隔离级别完全可以防止不可重复读和幻读。

内容的提问来源于stack exchange，提问作者Super Kai - Kazuya Ito