mysql 临键锁_记录锁、间隙锁、临键锁

这三种并不是锁，而是锁的算法。它们的共同特点是互斥的。

间隙锁和临键锁只有在RR级别中才能生效。

set global transaction isolation level repeatable read;

select @@global.tx_isolation;

间隙锁的目的是为了防止多个事务把记录插入到同一范围中去，这样能防止幻读

间隙锁可能会出现在唯一索引和辅助索引，现在分情况讨论。

一 唯一索引

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

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; #阻塞

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; #成功

二 普通索引

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; #成功

select * from fruit where num = 70 for update;

insert into fruit select 41 , 'mango', 61; # 阻塞

……

其余情况与唯一索引类似