间隙锁在可重复读隔离级别下才有效

加锁规则

总结为两个“原则”、两个“优化”和一个“bug”

原则1：加锁的基本单位是next-key lock,前开后闭区间；

原则2：查找过程中访问到的对象都会加锁；

优化1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁；

优化2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，nextkeylock 退化为间隙锁；

一个bug:唯一索引上的范围查询会访问到不满足条件的第一个值为止;

以表t为例

CREATE TABLE `t` (
 `id` int(11) NOT NULL,
 `c` int(11) NOT NULL,
 `d` int(11) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `c` (`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

insert into t values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);

一、案例一：等值查询间隙锁

sessionA	sessionB	sessionC
begin;update t set d=d+1 where id=7;
	insert into t values(8,8,8);(blocked)
		update t set d=d+1 where id=10;(Query OK)

加锁规则判断：

表中没有id=7记录，根据原则1，加锁的单位是next-key lock,sessionA的加锁范围是(5,10]；

根据优化2，这是一个等值查询(id=7),id=10不满足查询条件，next-key Lock退化成间隙锁，最终加锁的范围就是(5,10);

二、非唯一索引等值锁

sessionA	sessionB	sessionC
begin;select id from t where c=5 lock in share mode;
	update t set d=d+1 where id=5;(Query OK)
		insert into t values(7,7,7);(blocked)

加锁规则判断：

根据原则1，加锁单位是next-key lock,因此会给(0,5]加上next-key lock;

c是普通索引，向右遍历查到c=10才放弃，根据原则2，访问到的都要加锁，索引(5,10]加next-key lock;

符合优化2，等值判断，向右遍历，最后一个值不满徐足c=5这个等值条件，退化成间隙锁(5,10);

根据原则2，只有访问到的才加锁，查询采用的覆盖索引，并不需要访问主键索引，主键索引上没有任何锁，所以sessionB的update语句可以执行；

sessionC要插入(7,7,7)就会被sessionA的间隙锁(5,10)挡住

注意：
如果将select id from t where c=5 lock in share mode;换成
select id from t where c=5 for update;
则会强制将主键索引也给锁上，此时sessionB就会变为阻塞

三、案例三：主键索引范围锁

sessionA	sessionB	sessionC
begin; select * from t where id>=10 and id<11 for update;
	insert into t values(8,8,8);(Query OK)insert into t values(13,13,13);(blocked)
		update t set d=d+1 where id=15;(blocked)

开始执行的时候找到第一个id=10的行，根据原则1，next-key lock(5,10]退化成行锁，只加了id=10这一行的行锁；

范围查找向后继续查找，找到id=15这一行停下来，需要加next-key lock(10,15]。

所以sessionB和sessionC都被阻塞。

四、案例四：非唯一索引范围锁

sessionA	sessionB	sessionC
begin; select * from t where c>=10 and c<11 for update;
	insert into t values(8,8,8);(blocked)
		update t set d=d+1 where c=15;(blocked)

sessionA的加锁场景，索引c添加了(5,10]和(10,15]这两个next-key lock

所以sessionB和sessionC的操作都被阻塞；

五、案例五：唯一索引范围锁bug

sessionA	sessionB	sessionC
begin; select * from t where id>10 and id<=15 for update;
	update t set d=d+1 where id=20;(blocked)
		insert into t values(16,16,16);(blocked)

sessionA是一个范围查询，根据原则1，在索引d上只加了(10,15]这个next-key lock,id是唯一键，循环判断到id=15这一行停止；

实现上，InnoDB会往前扫描到第一个不满足条件为止，所以索引id上(15,20]这个next-key lock也会被锁上；

所以sessionB和sessionC的操作都会被锁住；

六、案例六：非唯一索引上存在“等值”的例子

insert into t values(30,10,20);

新插入的这一行c=10,现在表中存在两个c=10的行

虽然有两个c=10,但是主键值id是不同的(分别是10和30)，因此两个10之间也是有间隙的 采用delete语句来验证：

sessionA	sessionB	sessionC
begin; delete from t where c=10;
	insert into t value(12,12,12);(blocked)
		update t set d=d+1 where c=15;(Query OK)

sessionA遍历的时候是（c=5,id=5) 到 (c=10,id=10) 这个 next-key lock，session A 向右查找，直到碰到 (c=15,id=15) 这一行，循环才结束；

根据优化2，等值查询，向右查找到了不满足条件的行，所以会退化成(c=10,id=10) 到 (c=15,id=15) 的间隙锁；

也就是说，这个 delete 语句在索引 c 上的加锁范围，就是下图中蓝色区域覆盖的部分

蓝色区域是虚线，表示开区间，即(c=5,id=5) 和 (c=15,id=15) 这两行上都没有锁;

六、案例七：limit加锁

sessionA	sessionB
begin; delete from t where c=10 limit 2;
	insert into t value(12,12,12);(Query OK)

和案例六相比加锁的效果不同，可以看到，session B 的 insert 语句执行通过了，跟案例六的结果不同。

案例七里的 delete 语句明确加了 limit 2 的限制，因此在遍历到 (c=10,id=30) 这一行之后，满足条件的语句已经有两条，循环就结束了。

因此索引 c 上的加锁范围就变成了从（c=5,id=5) 到（c=10,id=30) 这个前开后闭区间。

可以看到，(c=10,id=30）之后的这个间隙并没有在加锁范围里，因此 insert 语句插入 c=12 是可以执行成功的。

在删除数据的时候尽量加limit,不仅可以控制删除数据的条数，还可以让数据更加安全，减少加锁的范围。

总结

可重复读隔离级别遵守两阶段锁协议，所有加锁的资源，都是在事务提交或者回滚的时候才释放的；

next-key lock 实际上是由间隙锁加行锁实现的。如果切换到读提交隔离级别 (read-committed) 的话，就好理解了，过程中去掉间隙锁的部分，也就是只剩下行锁的部分