间隙锁和行锁加锁规则

表和数据

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

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

规则总结(这个规则只限于截止到现在的最新版本,即 5.x 系列 <=5.7.24,8.0 系列 <=8.0.13。):
原则 1:加锁的基本单位是 next-key lock。希望你还记得,next-key lock 是前开后闭区间。
原则 2:查找过程中访问到的对象才会加锁。
优化 1:索引上的等值查询,给唯一索引加锁的时候,next-key lock 退化为行锁。
优化 2:索引上的等值查询,向右遍历时且最后一个值不满足等值条件的时候,next-key lock 退化为间隙锁。
一个 bug:唯一索引上的范围查询会访问到不满足条件的第一个值为止。

案例一:等值查询间隙锁


等值查询间隙锁
  • 根据原则 1,加锁单位是 next-key lock,session A 加锁范围就是 (5,10];
  • 同时根据优化 2,这是一个等值查询 (id=7),而 id=10 不满足查询条件,next-key lock 退化成间隙锁,因此最终加锁的范围是 (5,10)。

案例二:非唯一索引等值锁


非唯一索引等值锁

根据原则1,优化2,锁的范围是(0,5],(5,10),但是根据原则2,只有访问到的对象才加锁,这个查询使用了覆盖索引,并不访问主键索引,所以主键上没加锁。
需要注意,在这个例子中,lock in share mode 只锁覆盖索引,但是如果是 for update 就不一样了。 执行 for update 时,系统会认为你接下来要更新数据,因此会顺便给主键索引上满足条件的行加上行锁。

案例三:主键索引范围锁


主键索引范围锁
  • 开始执行的时候,要找到第一个 id=10 的行,因此本该是 next-key lock(5,10]。 根据优化 1, 主键 id 上的等值条件,退化成行锁,只加了 id=10 这一行的行锁。
  • 范围查找就往后继续找,找到 id=15 这一行停下来,因此需要加 next-key lock(10,15]。
    首次 session A 定位查找 id=10 的行的时候,是当做等值查询来判断的,而向右扫描到 id=15 的时候,用的是范围查询判断

案例四:非唯一索引范围锁


非唯一索引范围锁

这次 session A 用字段 c 来判断,加锁规则跟案例三唯一的不同是:在第一次用 c=10 定位记录的时候,索引 c 上加了 (5,10] 这个 next-key lock 后,由于索引 c 是非唯一索引,没有优化规则,也就是说不会蜕变为行锁,因此最终 sesion A 加的锁是,索引 c 上的 (5,10] 和 (10,15] 这两个 next-key lock。

案例五:唯一索引范围锁 bug


唯一索引范围锁 bug
  • session A 是一个范围查询,按照原则 1 的话,应该是索引 id 上只加 (10,15] 这个 next-key lock,并且因为 id 是唯一键,所以循环判断到 id=15 这一行就应该停止了。
  • 但是实现上,InnoDB 会往前扫描到第一个不满足条件的行为止,也就是 id=20。而且由于这是个范围扫描,因此索引 id 上的 (15,20] 这个 next-key lock 也会被锁上。改成select * from t where id>10 and id<15 for update;则只会加new-key lock(10,15]

案例六:非唯一索引上存在"等值"的例子
表中插入一条mysql> insert into t values(30,10,30);



这时,session A 在遍历的时候,先访问第一个 c=10 的记录。同样地,根据原则 1,这里加的是 (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) 的间隙锁。

案例七:limit 语句加锁



索引 c 上的加锁范围变成了从(c=5,id=5) 到(c=10,id=30) 这个前开后闭区间,如下图所示:


案例八:一个死锁的例子


死锁
  • session A 启动事务后执行查询语句加 lock in share mode,在索引 c 上加了 next-key lock(5,10] 和间隙锁 (10,15);
  • session B 的 update 语句也要在索引 c 上加 next-key lock(5,10] ,进入锁等待;
  • 然后 session A 要再插入 (8,8,8) 这一行,被 session B 的间隙锁锁住。由于出现了死锁,InnoDB 让 session B 回滚。
    session B的行锁c=10和session A有冲突,所以死锁

在读提交情况下,语句执行过程中加上的行锁,在语句执行完成后,就要把“不满足条件的行”上的行锁直接释放了,不需要等到事务提交。

费解的问题



1.由于是 order by c desc,第一个要定位的是索引 c 上“最右边的”c=20 的行,所以会加上间隙锁 (20,25) 和 next-key lock (15,20]。
2.在索引 c 上向左遍历,要扫描到 c=10 才停下来,所以 next-key lock 会加到 (5,10],这正是阻塞 session B 的 insert 语句的原因。
3.在扫描过程中,c=20、c=15、c=10 这三行都存在值,由于是 select *,所以会在主键 id 上加三个行锁。

参考:https://time.geekbang.org/column/article/75659

推荐阅读更多精彩内容

  • 1 最近在读王兴的传记,却意外对王背后的的另外一个王感兴趣。 他,就是王慧文,媒体笔下的老王,美团人口中的慧文哥。...
    tanglewood阅读 3,920评论 1 0
  • 每天,我们都在路上,走着,走着。有的人抬头望向天空,有的人低头玩着手机,有的人就是默默的走着。我们有个共同的特点,...
    wowwowwowsky阅读 113评论 0 0
  • 近日笔者因访友途经一西北城市,飞机上顺手拿一份当地的报纸随意浏览,赫然看到当地市政2019年计划斥巨资兴建熊猫馆、...
    少年李鱼阅读 86评论 0 5
  • 我想我们在慢慢的成长,成长的过程中是残忍的。我们得到一些也失去更多,我们要慢慢适应这样失去的过程,
    884a4ec8eb2d阅读 20评论 0 1
  • 苏州郎心铁阅读 46评论 2 2