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存储引擎》-第六章:锁

推荐阅读更多精彩内容