详解MySQL幻读及如何消除_Mysql

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

事务隔离级别

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：

t1：

									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;

t2:

									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;

t2:

									mysql> begin;

									​

									mysql> insert into user values (2, 'jimmy');

									error 1062 (23000): duplicate entry '2' for key 'primary'