『MySQL 实战 45 讲』21 - 为什么我只改一行的语句，锁这么多？

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

1. 需求：创建一个小表

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

2. 加锁规则

• MySQL 后面的版本可能会改变加锁策略，所以这个规则只限于截止到现在的最新版本，即 5.x 系列 <=5.7.24，8.0 系列 <=8.0.13

案例一：等值查询间隙锁

1. 表 t 中没有 id=7 的记录，按照加锁规则

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

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

1. 这是一个覆盖索引上的锁

• 原则 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：只有访问到的对象才会加锁，这个查询使用覆盖索引，并不需要访问主键索引，所以主键索引上没有加任何锁，这就是为什么 session B 的 update 语句可以执行完成
• 但 session C 要插入一个 (7,7,7) 的记录，就会被 session A 的间隙锁 (5,10) 锁住。(1,1,1) 也不行，因为已经锁了 (0,5]

2. lock in share mode 只锁覆盖索引。执行 for update 时，系统会认为你接下来要更新数据，因此会顺便给主键索引上满足条件的行加上行锁
3. 通过例子，明确锁是加在索引上的
4. 使用 lock in share mode 来给行加读锁避免数据被更新的话，就必须得绕过覆盖索引的优化。例如将 session A 的查询语句改成 select d from t where c=5 lock in share mode

案例三：主键索引范围锁

# Q1
select * from t where id=10 for update;
# Q2
select * from t where id>=10 and id<11 for update;

1. Q1 和 Q2 实际加的锁不一样。先看 session A 执行第二个查询语句的加锁效果
   
2. 加锁规则

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

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

1. 查询语句的 where 部分用的是字段 c
2. 加锁规则

• 在第一次用 c=10 定位记录的时候，索引 c 上加了 (5,10] 这个 next-key lock
• 由于索引 c 是非唯一索引，没有优化规则（因为是范围查询），也就是说不会蜕变为行锁，因此最终 sesion A 加的锁是，索引 c 上的 (5,10] 和 (10,15] 这两个 next-key lock

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

1. 执行规则

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

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

1. 首先在表中，再插入一条新记录

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

2. 表中有 2 个 c=10，但是主键值 id 不相同（分别是10，30），2 个 c=10 的记录之间，也有间隙
   
3. 下面用 delete 语句来验证（也等同于 for update）
   4. 加锁规则

• 原则 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) 的间隙锁，实际是 (5,15)
• 测试可以：(12, 100, 100)，测试不可以：(13, 12, 100)。也就是说只要隔开 (10,10, *) 到 (30,10,*) 范围都可以

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

案例七：limit 语句加锁

1. session A 的 delete 语句加了 limit 2，但是 c=10 的记录其实只有两条，因此加不加 limit 2，删除效果都一样，但是加锁的效果就不一样
   

2. 执行规则

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

3. 指导意义：在删除数据的时候尽量加 limit，控制删除数据的条数，还可以减小加锁的范围

案例八：一个死锁的例子

1. 执行逻辑

• 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 的“加 next-key lock (5,10] ”操作，实际上分成了两步，先是加 (5,10) 的间隙锁，加锁成功。然后加 c=10 的行锁，这时候才被锁住的

2. 总结：分析加锁规则的时候可以用 next-key lock 来分析。但是要知道，具体执行的时候，是要分成间隙锁和行锁两段来执行的

案例九：数据排序

1. 加锁规则

• 由于是 order by c desc，第一个要定位的是索引 c 上 c=20 的行，所以会加上间隙锁 (20,25) 和 next-key lock (15,20]
• 在索引 c 上向左遍历，要扫描到 c=10 才停下来，所以 next-key lock 会加到 (5,10]
  • 所以如果这时候执行 update t set d=d+1 where c = 10，也一定会锁住