MySQL45讲学习笔记|21 | 为什么我只改一行的语句，锁这么多（） MySQL45讲学习笔记|mysql

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

案例一：等值查询间隙锁 【MySQL45讲学习笔记|21 | 为什么我只改一行的语句，锁这么多（）】

文章图片

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

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

文章图片

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

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

文章图片

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

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

文章图片

在第一次用 c=10 定位记录的时候，索引 c 上加了 (5,10]这个 next-key lock 后，由于索引 c 是非唯一索引，没有优化规则，也就是说不会蜕变为行锁，因此最终 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 的记录之间，也是有间隙的。

文章图片

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) 的间隙锁。

文章图片