记录锁、间隙锁、临键锁

这三种并不是锁，而是锁的算法。它们的共同特点是互斥的。
间隙锁和临键锁只有在RR级别中才能生效。

set global transaction isolation level repeatable read;
select @@global.tx_isolation;

间隙锁的目的是为了防止多个事务把记录插入到同一范围中去，这样能防止幻读
间隙锁可能会出现在唯一索引和辅助索引，现在分情况讨论。

一 唯一索引

1. select * from fruit where id = 50 for update; # 记录锁

2. select * from fruit where id between 30 and 50 for update;
   临界锁 [30, 50] , [50, 55]
   insert into fruit select 29, 'mango', 50; # 成功
   insert into fruit select 33, 'mango', 50; # 阻塞
   insert into fruit select 51, 'mango', 50; # 阻塞
   insert into fruit select 58, 'mango', 50; # 成功
   update fruit set num = num -1 where id = 55; #阻塞

3. select * from fruit where id = 20 for update;
   （15, 30）
   insert into fruit select 16,'mango', 100; # 阻塞
   insert into fruit select 25,'mango', 100; #阻塞
   insert into fruit select 13 ,'mango', 100; #成功
   insert into fruit select 33 ,'mango', 100; #阻塞
   update fruit set num = num -1 where id = 15; #成功
   update fruit set num = num -1 where id = 30; #成功

二 普通索引

1. select * from fruit where num = 80 for update;
   普通索引 (60, 90) 主键(40 ,60)
   insert into fruit select 100,'mang0',70; #阻塞
   insert into fruit select 51,'mang0', 1; #成功
   insert into fruit select 41,'mang0',60; #阻塞
   insert into fruit select 39,'mang0',60; #成功
   insert into fruit select 59,'mang0',90; #阻塞
   insert into fruit select 61,'mang0',90; #成功
   update fruit set name = 'mango' where id = 40; #成功
   update fruit set num = num -1 where id = 40; #成功
   update fruit set name = 'mango' where id = 60; #成功
   update fruit set num = num -1 where id = 40; # 失败
   update fruit set num = num -1 where id = 70; # 失败
   update fruit set num = num -1 where id = 100; #成功

2. select * from fruit where num = 70 for update;
   insert into fruit select 41 , 'mango', 61; # 阻塞
   ……
   其余情况与唯一索引类似

参考：MySQL的锁机制 - 记录锁、间隙锁、临键锁， 《MySQL技术内幕》