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

本文主要是介绍第二十一章 为什么我只改一行的语句，锁这么多？，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

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

简单介绍一下 next-key lock 的加锁规则 ?

• 两个原则、两个优化、一个 bug

1. 原则：加锁的基本单位是 next-key lock（前开后闭）
2. 原则：查询过程中访问到的对象才会加锁（首先是 where 的索引对象，其次是 select 的索引对象）
3. 优化：索引上的等值查询，如果是唯一索引，next-key lock 会退化为 行锁
4. 优化：索引上的等值查询，向右遍历时且最后一个值不满足等值条件 的时候，next-key lock 退化为 间隙锁
5. 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);

案例一：等值查询间隙锁

加锁判断流程：

• session A 等值查询，筛选条件 id 为唯一索引，索引数据查询落地在 id 索引树的 5 和 10 之间
  • 如果 id 等于 7 的记录在表中查找到，则 next-key lock 会退化为 行锁
  • 如果 id 等于 7 的记录没有在表中找到，则 加锁单位是 next-key lock
  • 所以 session A 的加锁范围就是 (5,10]
• 查询条件 id = 7，向右遍历，第一个不满足条件的记录是 (10,10,10)，所以 next-key lock 退化为 间隙锁
  • 加锁范围就是 (5,10)
• session B 插入数据 (8,8,8) 在 (5,10) 区间内，所以需要等待 sessionA锁 释放
• session C (id = 10) 只有间隙锁，所以可以直接更新

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

加锁判断流程：

• 根据原则1，加锁单位是 next-key lock，因此会给 (0,5] 加上 next-key lock
  • 因为 next-key lock 是 左开右闭 区间，所以这里加锁区间不能是 (5,10]，否则 5 就不在加锁范围内了
• 因为 c 不是唯一索引，所以需要向右遍历到第一个不符合条件的值才停止
  • 查看 c = 10 才放弃，根据原则2，访问到的都要加锁，因此要给 (5,10] 加上 next-key lock
  • 但同时这个符合优化2：等值判断，向右遍历，最后一个值不满足 c=5 这个等值条件，因此退化成间隙锁 (5,10)
• 根据原则2，只有访问到的对象才会加锁，这个查询使用覆盖索引，并不需要访问主键索引，所以主键索引上没有加任何锁，这就是为什么 session B 的 update 语句可以执行完成
  • 访问到的对象才加锁，这个对象指的是列的索引，不是 记录行
  • 加锁，是加在索引上的
  • 列上有索引，就加在索引上；列上如果没有索引，就加在主键上
    • 你的普通等值查询的列没有索引，没有索引就会遍历主键索引树，并且是遍历整个主键索引树，所以会把整个表都锁住
• session C 插入一个 (7,7,7) 的记录，因为 c 列上有锁，所以被 session A 的间隙锁 (5,10) 锁住了

关于 for update 和 lock in share mode 的说明 ?

• for update：会顺便给主键索引加锁
• lock in shared mode：如果有覆盖索引优化，没有访问到主键索引，那么主键索引就不会加锁

案例三：主键索引范围锁

加锁判断流程：

• 开始执行的时候，要找到第一个 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]

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

• 在第一次用 c=10 定位记录的时候，索引 c 上加了 (5,10] 这个 next-key lock 后，由于索引 c 是非唯一索引，没有优化规则，也就是说不会蜕变为行锁
• 范围查找就往后继续找，找到 id=15 这一行停下来，因此需要加 next-key lock(10,15]
• session A 加的锁是：索引 c 上的 (5,10] 和 (10,15] 这两个 next-key lock
• sesson B 要插入 (8,8,8) 的这个 insert 语句时就被堵住了

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

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

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

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),(30,10,30);

• session A 在遍历的时候，先访问第一个 c=10 的记录。同样地，根据原则 1，这里加的是 (c=5,id=5) 到 (c=10,id=10) 这个 next-key lock (5,10]
• session A 向右查找，直到碰到 (c=15,id=15) 这一行，循环才结束。根据优化 2，这是一个等值查询，向右查找到了不满足条件的行，所以会退化成 (c=10,id=10) 到 (c=15,id=15) 的间隙锁 (10,15)
• (5,10] + (10,15) ⇒ (5,15)
• 此时 session A 的主键 id 持有两个行锁：
  • 锁的是 id=10 的行（id=10, c=10）
  • 锁的是 id=30 的行（id=30, c=10）
• delete 语句在索引 c 上的加锁范围

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

关于 delete 语句加锁

• delete 和 for update 的加锁逻辑类似， 如果是走非主键索引的话，除了给那个索引加锁，还会顺便给主键索引加锁

案例七：limit 语句加锁

• session A 的 delete 语句加了 limit 2
• 你知道表 t 里 c=10 的记录其实只有两条，因此加不加 limit 2，删除的效果都是一样的，但是加锁的效果却不同
• 可以看到，session B 的 insert 语句执行通过了，跟案例六的结果不同
• 案例七里的 delete 语句明确加了 limit 2 的限制，因此在遍历到 (c=10, id=30) 这一行之后，满足条件的语句已经有两条，循环就结束了

• 因此 insert 语句插入 c=12 是可以执行成功的

案例八：一个死锁的例子

加锁判断流程：

• session A 启动事务后执行查询语句加 lock in share mode，在索引 c 上加了 next-key lock(5,10] 和间隙锁 (10,15)
• session B 的 update 语句也要在索引 c 上加 next-key lock(5,10] ，进入锁等待
  • next-key lock(5,10] 其实是分成两步来完成的：先是间隙锁，然后是行锁
    • 先是加 (5,10) 的间隙锁，加锁成功
    • 然后加 c=10 的行锁，这时候被锁住
• 然后 session A 要再插入 (8,8,8) 这一行，被 session B 的间隙锁锁住。由于出现了死锁，InnoDB 让 session B 回滚
  • insert(8,8,8) 等待间隙锁 (5,10) 释放
  • 形成了 session A 等 session B 的间隙锁，session B 等 session A 的行锁的死锁局面

例子

下列 事务A 的语句执行后，事务B 的语句能执行成功吗，为什么 ?

-- 事务A
BEGIN;
update t set d = d + 1 where id = 10;-- 事务B
update t set c = c + 1 where c = 10;-- 事务B1
select c from t where c = 10 lock in share mode;-- 事务B2
select d from t where c = 10 lock in share mode;

事务B 阻塞，事务B1 执行成功，事务B2 阻塞

加锁判断流程：

• 事务A 查询时，仅访问主键索引树，因为 id 是主键，所以加锁范围是行锁 id=10
• 事务B 被阻塞了，是因为更新数据需要访问主键索引树，要访问 id 为 10 的节点，所以锁住了
• 事务B1 执行成功，是因为只需要查询普通索引树即可，不访问主键索引树
• 事务B2 被阻塞了，是因为需要回表查询主键索引树，要访问id为10的节点，所以锁住了

实战

为什么 session B 的 insert 操作，会被锁住呢 ?

• 首先，因为排序字段是 索引 c，且是降序，所以优化器选择使用 c <= 20 索引执行，扫描从 右边 开始 向左 结束
• 加上间隙锁 (20,25) 和 next-key lock (15,20]、(10,15]
  • 数据库有20，它是小于等于20，所以需要往后找，找到不满足 小于等于20 的数据，也就是 25，所以是 (20,25)
    • 8.0.18版本前是 (20,25]
    • 8.0.18版本后事 (20,25)
• 在索引 c 上向左遍历，范围查找要扫描到 c=10 才停下来，所以 next-key lock 会加到 (5,10]
• 在扫描过程中，c=20、c=15、c=10 这三行都存在值，由于是 select *，所以会在主键 id 上加三个行锁
• 所以，session A 的 select 语句锁的范围就是：索引 c 上 (5, 25)；主键索引上 id=15、20 两个行锁

这篇关于第二十一章 为什么我只改一行的语句，锁这么多？的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---