21 | update一行却锁了多行记录

〇、复习
案例一：等值查询间隙锁

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

案例三：主键索引范围锁

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

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

案例六：非唯一索引上存在"等值"的例子
先插入：mysql> insert into t values(30,10,30); 问题：新插入的这一行 c=10，也就是说现在表里有两个 c=10 的行。那么，这时候索引 c 上的间隙是什么状态了呢？
PS：delete相当于for update 所以也会锁主键索引

案例七：limit 语句加锁

案例八：一个死锁的例子

一、头打掉都要记住的地方

🏁：1.加锁规则。

前提：RR隔离级别下，且版本5.x 系列 <=5.7.24，8.0 系列 <=8.0.13。

加锁规则里面，包含了两个“原则”、两个“优化”和一个“bug”。
原则 1：加锁的基本单位是 next-key lock。希望你还记得，next-key lock 是前开后闭区间。
原则 2：查找过程中 访问到的对象才会加锁。
优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。
优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。
一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

🏁：2.lock in share mode 只锁覆盖索引，但是如果是 for update 就不一样了。 执行 for update 时，系统会认为你接下来要更新数据，因此会顺便给主键索引上满足条件的行加上行锁。

🏁：3.读提交的间隙锁。纳尼？读体检不是不会加间隙锁嘛？不，在外键场景下还是有间隙锁，相对比较复杂。
🏁：3.1.在读提交隔离级别下还有一个优化，即：语句执行过程中加上的行锁，在语句执行完成后，就要把“不满足条件的行”上的行锁直接释放了，不需要等到事务提交

🏁：4.案例六的答案中重点关注这个蓝色部分，如果将案例六的session B改为mysql> insert into t_20 values(50,5,50);发现也会阻塞
（block）

二、课后题
还是我们在文章开头初始化的表 t，里面有 6 条记录，图 12 的语句序列中，为什么 session B 的 insert 操作，会被锁住呢？

三、评论区

💋：5.一个有趣的问题

CREATE TABLE z (
id INT PRIMARY KEY AUTO_INCREMENT,
b INT,
KEY b(b)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;

INSERT INTO z (id, b)
VALUES (1, 2), (3, 4), (5, 6), (7, 8), (9, 10);

session A
BEGIN;
SELECT * FROM z WHERE b = 6 FOR UPDATE;

session B
INSERT INTO z VALUES (0, 4);
这里为什么会被锁住?

💡：提示，主键自增时，插入id为0会默认拿到数据库自增id的最大值作为当前id主键插入

🏁：6.好的总结
1、查询过程中访问到的对象才会加锁，而加锁的基本单位是next-key lock（前开后闭）；
2、等值查询上MySQL的优化：索引上的等值查询，如果是唯一索引，next-key lock会退化为行锁，如果不是唯一索引，需要访问到第一个不满足条件的值，此时next-key lock会退化为间隙锁；
3、范围查询：无论是否是唯一索引，范围查询都需要访问到不满足条件的第一个值为止；

🏁：7.唯一索引范围锁蔓延
案例五：唯一索引范围锁 bug
begin;
select * from t where id>10 and id<=15 for update;
1、执行如上语句加锁范围(10,15]和(15,20]；
2、因为10未加锁，所以我单独再开一个连接，执行delete from t where id=10;不会锁等待，能正常删除；
3、但是我再执行insert into t values(10,10,10); 语句会等待，无法正常执行；
4、经过分析我发现第一个连接执行的语句的加锁范围已经变成(5,15]和(15,20]，代表锁蔓延了；即：更新边界值导致间隙变大，再改成原值改不回去，被阻塞

🏁：8.update按照条件更新唯一索引时，索引行存在会退化为行锁，不存在那就是间隙锁了。例如：案列一中update t set d=d+1 where id=7 中id是主键也是唯一索引，按优化1应该退化成行锁才对，为什么insert into t values(8,8,8)会被锁住，