修改被锁分析

⚠️ 基于可重复读隔离级别

加锁规则

1. [原则1]加锁基本单位是next-key lock，(前开后闭]
2. [原则2]查询访问到的对象才加锁
3. [优化1]索引上的等值查询，给唯一索引加锁，next-key lock退化为行锁
4. [优化2]索引上的等值查询，向右遍历时且最右一个值不满足等值条件时，next-key lock退化为间隙锁。
5. [bug1]唯一索引上的范围查询会访问到不满足条件的第一个值为止

---

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，加锁单位时next-key lock，sessionA加锁范围(5,10]
• 优化2，这是一个等值查询(id=7)，而id=10不满足条件，next-key lock退化为间隙锁.因此最终加锁范围时(5,10)

所以 sessionB插入8被锁住，而sessionC修改10这行是可以的。

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

• 原则1，加锁单位时next-key lock，因此会给(0,5]加上next-key lock
• c是普通索引，因此仅访问c=5不能马上停下来，需要向右遍历，查到c=10才放弃。原则2，访问到的都要加锁，因此给(5,10]加next-key lock
• 优化2，索引等值判断，向右遍历，最后一个不满足c=5。next-key lock退化为间隙锁(5,10)
• 原则2，只有访问到才加锁。该查询使用覆盖索引，不需要访问主键索引，所以主键索引没有加锁，所以sessionB的update语句可以执行完成。

但sessionC要插入一个(7,7,7)但记录，就被sessionA的间隙锁锁住。

案例三：主键索引范围锁

• 找到第一个id=10的行，原则1，加(5,10]。优化1，唯一索引id上的等值查询，退化为行锁。只加id=10
• 范围查找要往后继续找，到id=15，加锁(10,15]

所以，sessionA锁的是主键索引上，行锁id=10和next-key lock (10,15]

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

• 找到第一个c=10的行，原则1加(5,10]，索引c并非唯一索引，不会退化为行锁
• 范围查找要往后继续找，到id=15，加锁(10,15]

所以，sessionA在索引c上，加了(5,10]和(10,15]两个next-key lock。

这里需要扫描到c=15才停止扫描，是合理的，因为InnoDB要扫到c=15，才知道不需要继续往后找了。

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

• 原则1，索引id加(10,15]，且id唯一，索引循环判断到id=15这一行就该停止了
• 但是bug1，InnoDB会往前扫描到第一个不满足条件的行为止，即id=20。且因为是范围查询，加锁(15,20]

所以，sessionB跟新id=20被锁住，sessionC插入id=16被锁住。

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

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

现在表里有两个c=10的行:

• 先访问第一个c=10的记录。原则1，加(c=5,id=5)到(c=10,id=10)这个next-key lock
• 向右查找，直到(c=15,id=15)这一行。优化2，等值查询，向右找到不满足条件的行，退化为(c=10,id=10)到(c=15,id=15)的间隙锁

加锁范围如下：

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

案例七：limit 语句加锁

• 遍历到(c=10, id=30)这一行之后，满足条件的语句已经有两条，循环就结束了

• 因此，索引c上的加锁范围就变成了从（c=5,id=5)到（c=10,id=30)这个前开后闭区间

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

案例八：一个死锁的例子

• 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不是还没申请成功吗？

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

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