MySQL幻读及解决方法

工程师，期望当一个坐台歌手，个人主页：www.d-kai.me

这是一篇数据库隔离级别的科普文章，旨在了解数据库中著名的幻读现象，为了专注，对脏读、不可重复读不作讨论。

事务隔离级别

MySQL有四级事务隔离级别:

读未提交 READ-UNCOMMITTED：存在脏读，不可重复读，幻读的问题
读已提交 READ-COMMITTED：不存在脏读，但存在不可重复读，幻读问题
可重复读 REPEATABLE-READ：不存在脏读，不可重复读问题，但存在幻读问题
序列化SERIALIZABLE：解决脏读，不可重复读，幻读问题，但完全串行执行，性能最低

什么是幻读

幻读错误的理解：说幻读是事务A 执行两次 select 操作得到不同的数据集，即 select 1 得到10条记录，select 2 得到11条记录。这其实并不是幻读，这是不可重复读的一种，只会在 R-U R-C 级别下出现，而在 mysql 默认的 RR 隔离级别是不会出现的。

这里给出我对幻读的理解：

幻读，并不是说事务中多次读取获取的结果集不同，幻读更重要的是某次的 select 操作得到的结果集所表征的数据状态无法支撑后续的业务操作。更为具体一些：select 记录不存在，准备插入此记录，但执行 insert 时发现此记录已存在，无法插入，如同产生了幻觉

举个例子可能会简化理解：

mysql> show create table user\G
*************************** 1. row ***************************
       Table: user
Create Table: CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `name` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

分别开启两个事务T1 & T2，并设置其隔离级别为Reaptable-Read：

mysql> set global transaction isolation level repeatable read;                                                                                 

mysql> begin;
mysql> select * from user;
mysql> insert into user values (1, 'jeff');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

mysql> select * from user;

mysql> set global transaction isolation level repeatable read;                                                                                 

mysql> begin;
mysql> insert into user values (1, 'jeff');
mysql> commit;

T1 事务检测表中是否有 id 为 1 的记录，没有则插入

T2 插入干扰记录，造成T1出现幻读。

上例中需要确保T1事务执行begin后才开始执行事务T2。

上例中T1就发生了幻读，因为 T1读取的数据状态与后面的动作发生了语义上的冲突：查询的时候明明提示记录不存在，插入的时候去提示主键重复，类似于出现幻影，因而称之为幻读。

如何消除幻读

MySQL当前有两种方式可以消除幻读：

1. 通过对select操作手动加行X锁(SELECT ... FOR UPDATE )。原因是InnoDB中行锁锁定的
是索引，纵然当前记录不存在，当前事务也会获得一把记录锁（记录存在就加行X锁，不
存在就加next-key lock间隙X锁），这样其他事务则无法插入此索引的记录，杜绝幻
读。
2. 进一步提升隔离级别为SERIALIZABLE

测试一下效果

mysql> begin;

mysql> select * from user where id = 2 for update;
mysql> insert into user values (2, 'tony');

mysql> commit;

mysql> begin;

mysql> insert into user values (2, 'jimmy');
ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'

现在T1查询时携带了for update，在Innodb内会对该索引加锁（即使当前不存在），于是事务T2的insert会被阻塞直到T1显示提交，这样T1成功了，对于T1来说，幻读确实被消除了，但T2的插入会报主键重复，这也符合预期。

至于另外一种提升隔离级别消除幻读的方式感兴趣的可以自己尝试，这里不再重复，其本质是类似的，只是让系统代替了手工加锁。

总结

RR作为 mysql 事务默认隔离级别，是事务安全与性能的折中，正确认识幻读后，开发者便可以根据需求自行决定是否需要防止幻读。

SERIALIZABLE则是悲观的认为幻读时刻都会发生，故会自动的隐式的对事务所需资源加排它锁，其他事务访问此资源会被阻塞等待，故事务是安全的，但需要认真考虑性能。

InnoDB的锁是针对索引，这点需要引起注意。对行记录加锁，如果存在，加X锁，否则会加 next-key lock / gap 锁 / 间隙锁，故InnoDB可以实现事务对某记录的预先占用，只要本事务还在，其他事务就别想占有它。关于锁，后面还会再有专门的文章讨论。

MySQL幻读及解决方法

MySQL幻读及解决方法

事务隔离级别

什么是幻读

如何消除幻读

总结

Recommend

你真的明白什么是幻读吗？

脏读、幻读与不可重复读

面试官：什么是脏读、幻读？

MySQL到底有没有解决幻读问题？这篇文章彻底给你解答 - 一灯架构

美团三面：一直追问我， MySQL 幻读被彻底解决了吗？

InnoDB 是如何解决幻读的 - mghio

InnoDB 是如何解决幻读的

小白必看！轻松理解和解决MySQL幻读问题！

RR有幻读问题吗？MVCC能否解决幻读？ - 磊哥|www.javacn.site

MVCC多版本并发控制和幻读问题的解决 - 天~若比邻

About Joyk