MySQL可重复读防止幻读

接上篇事务隔离级别和幻读，留了个坑，没想到竟然过了10天，时间不注意真的过的好快。顺便提下，简书图片链接是属于网站的，开发自己的图床迫在眉睫，万一哪天迁移就要做很多额外工作，一些概念或者思路用图片表达更直观清楚。
回到正题，之前提到一般情况下MySQL的InnoDB引擎在可重复读的情况下是没法保证不出现幻读的，但实际情况是MySQL可以通过加锁来防止幻读的出现，这种锁定通过Next-key机制来实现，是属于记录锁和间隙锁(Gap锁)的结合。
引申，行级别锁的三种算法:

• Record lock
• Gap lock
• Next-Key lock
  record lock 按照索引记录加锁，如果没有则采用隐式的主键来锁定。
  next-key 结合了Gap lock 和 record lock ，例如索引值：10,11,13,20。则按next-key 锁定的区间为(-∞,10],(10,11]
  ,(11,13],(13,20],(20,+∞)

注意此处指非唯一索引，如果是唯一索引，会降级为Record lock ，仅仅锁住索引本身，而非范围。同时这种降级只发生在查询所有索引列的情况下，如果存在联合索引且只是查询联合索引的某一列，即属于range类型查询，仍是采用Next-key锁定，不会降级。

举个存在唯一索引和辅助索引的例子做说明：

create table test ( a int , b int , primary key (a), key(b));
insert into test select 1, 1;
insert into test select 3, 1;
insert into test select 5, 3;
insert into test select 7, 6;
insert into test select 10,8;

执行 select * from test where b = 3 for update
存在两个索引，分别加锁，唯一主键列a加record lock ， 辅助索引列b加next-key lock (1,3) 以及给下一个值的区间(3,6)加gap锁；
因此在另一个事务里执行以下语句都会阻塞，具体分析：

select * from test where a = 5 lock in share mode;
insert into test select 4,2;
insert into test select 6,5;

第一个阻塞因为加了唯一索引的record lock a = 5；
第二个主键插入4，符合条件，但是根据辅助索引b 的范围, b = 2 在(1,3)中，同样阻塞；
第三个a =6 不在主键a锁定范围，b = 5 也不在辅助索引b 的范围(1,3)中，但在另一个gap锁范围(3,6)中，因此也阻塞；
这种锁定情形下，可以执行的包括类似语句：

insert into test select 8,6; 
insert into test select 2,0;

gap lock 可以通过设置隔离级别为读已提交或者将innodb_locks_unsafe_for_binlog = 1取消，但是从隔离性，性能，以及主从数据一致等方面都不建议这样做。

insert的特殊情况
对于insert 会检查下一条记录是否被锁定，如上述例子有select * from test where b = 3 for update插入insert into test select 2,2会检测到b = 3 已经被锁定，而insert into test select 2,0可以执行；

[1]:《MySQL技术内幕：InnoDB存储引擎》-第六章：锁