mysql 可重复读 幻读_MySQL可重复读防止幻读

接上篇

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