21 | 为什么我只改一行的语句，锁这么多？

---

MySQL45讲

实践篇

21 | 为什么我只改一行的语句，锁这么多？

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

next-key lock 加锁规则

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

• 原则 1：加锁的基本单位是 next-key lock。
• 原则 2：查找过程中访问到的对象才会加锁。
• 优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。
• 优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。
• bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止（mysql 8.0.18修复了这个bug）。

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);

案例一：等值查询间隙锁

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

疑问：为什么没用优化 1？

id=7 这行数据不存在，自然没有行锁，所以也就没有应用优化 1 的必要了。

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

1. 根据原则 1，加锁单位是 next-key lock，因此会给 (0,5]加上 next-key lock。c 是普通索引，因此仅访问 c=5 这一条记录不会马上停下来，需要向右遍历，查到 c=10 才放弃。
2. 根据原则 2，访问到的都要加锁，因此要给 (5,10]加 next-key lock。
3. 但是同时这个符合优化 2：等值判断，向右遍历，最后一个值不满足 c=5 这个等值条件，因此退化成间隙锁 (5,10)。
4. 根据原则 2 ，只有访问到的对象才会加锁，这个查询使用覆盖索引，并不需要访问主键索引，所以主键索引上没有加任何锁。

select … lock in share mode，如果使用了覆盖索引，则只会在覆盖索引上加锁。select … for update 系统会认为接下来要更新数据，将主键索引对应的那条记录也加上锁。

案例三：主键索引范围锁

1. 开始执行的时候，要找到第一个 id=10 的行，因此本该是 next-key lock(5,10]。 根据优化 1， 主键 id 上的等值条件，退化成行锁，只加了 id=10 这一行的行锁。
2. 范围查找就往后继续找，找到 id=15 这一行停下来，因此需要加 next-key lock(10,15]。

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

在第一次用 c=10 定位记录的时候，索引 c 上加了 (5,10]这个 next-key lock 后，由于索引 c 是非唯一索引，没有优化规则，也就是说不会蜕变为行锁（准确地说，向右遍历的最后一个值是 10，很明显就不能匹配上优化 2 中“不满足等值条件”的要求），因此最终 sesion A 加的锁是，索引 c 上的 (5,10] 和 (10,15] 这两个 next-key lock。

案例五：唯一索引范围锁 bug（MySQL 8.0.18 已优化）

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

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

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

虽然有两个 c=10，但是它们的主键值 id 是不同的（分别是 10 和 30），因此这两个 c=10 的记录之间，也是有间隙的。

1. session A 在遍历的时候，先访问第一个 c=10 的记录。同样地，根据原则 1，这里加的是 (c=5,id=5) 到 (c=10,id=10) 这个 next-key lock。
2. 然后，session A 向右查找，直到碰到 (c=15,id=15) 这一行，循环才结束。根据优化 2，这是一个等值查询，向右查找到了不满足条件的行，所以会退化成 (c=10,id=10) 到 (c=15,id=15) 的间隙锁。
   

案例七：limit 语句加锁

delete 语句明确加了 limit 2 的限制，因此在遍历到 (c=10, id=30) 这一行之后，满足条件的语句已经有两条，循环结束。因此，索引 c 上的加锁范围就变成了从（c=5,id=5) 到（c=10,id=30) 这个前开后闭区间。

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

案例八：一个死锁的例子

session B 的“加 next-key lock(5,10] ”操作，实际上分成了两步，先是加 (5,10) 的间隙锁，加锁成功；然后加 c=10 的行锁，被锁住。

因此，session A 要再插入 (8,8,8) 这一行，被 session B 的间隙锁锁住，发生死锁。