MySQL-21：next-key lock规则及案例

next-key lock加锁规则：

1. 原则一：加锁的基本单位为next-key lock且范围为前开后闭。
2. 原则二：查找过程中只有访问到的对象才会加锁，例如走全表扫描的情况，这种在扫描前就会给全表加上next-key lock。
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);

案例一：等值查询间隙锁

1. 因为id=7的行不存在，根据原则一，Session加锁的范围为id主键索引上的(5,10]。
2. 根据优化二，继续向右访问，id=10不满足条件，范围为(5,10]的next-key lock退化为间隙锁(5,10)。

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

1. 所需要的信息我们在普通索引C上就可以找到，利用覆盖索引优化，不会进行回表，根据原则一，Session A加锁范围为普通索引C上的(0,5]。
2. 根据优化二，继续向右寻找，id = 10不满足条件，范围为(5,10]的next-key lock退化为间隙锁(5,10)。
3. 根据原则二，Session B的update sql可以执行，因为主键索引表上没有加锁。

注意点：

1. 因为覆盖索引优化，lock in share mode只锁覆盖索引，不锁主键索引。
2. for update会给主键索引上加锁，因为系统以为你要修改数据。
3. 我们将上述sql改为select * from t where c = 5 lock in share mode，让它涉及到主键索引表就可以解决问题。

案例三：主键索引范围锁

1. Session A 先找到id=10这一行，根据优化一，原本(5,10]的next-key lock退化为id=10的行锁。
2. 唯一索引上的范围查找需要向后查找，找到id=15这一行，加锁next-key lock(10,15]。

这样后面的语句就明了了，这里有个注意点：找id=10这一行的时候是等值查询，id=15这一行的时候是范围查询判断。

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

1. 找到c=10这一行，加上(5,10]的next-key lock，由于不是非唯一索引，不退化为行锁。
2. 继续向前寻找，找到c=15这一行，加(10,15]的next-key lock。

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

1. 找到id=15这一行，由于是等值查询，所以加上(10,15]的next-key lock。
2. 由于InnoDB 会往前扫描到第一个不满足条件的行为止，也就是id=20这一行，也是范围查找，加上(15,20]的next-key lock。

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

案例建立在插入如下sql：

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

索引C的示意图如下：

模拟场景：

delte语句的加锁逻辑与select…for update是类似的。

1. 找到c=10的两行，会加上(5,10]，(10,10]的next-key lock。
2. 根据优化二，继续向右查询，找到c=15，结果会加上(10,15)的间隙锁。

案例七：limit 语句加锁

情况与案例七类似，不过加上limit 2，查询到两条数据之后就不会再查询，也就是不会查找到c=15这一行，示图如下：

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

案例八：一个死锁的例子

1. Session A在执行select语句时加(5,10]的next-key lock和(10,15)的间隙锁。
2. Session B在执行update语句时，先加(5,10)的间隙锁，再申请写锁的时候阻塞。
3. Session A在插入语句时发生死锁现象。

分析情况时用next-key lock，申请锁时还是分成间隙锁和行锁两段执行的。

总结：

1. 加锁的基本单位为next-key lock = 间隙锁+行锁，范围为前开后闭，但加锁过程为先申请间隙锁在申请行锁。
2. 只有访问到的对象才可以加锁。
3. 等值查询：①主键索引：next-key lock会退化为行锁；②非主键索引：保持原样
4. 等值查询：InnoDB会向右继续遍历，找到第一个不符合的数据行，加next-key lock并退化为间隙锁。
5. 范围查询：先找到复核条件的一行数据，再向右查找，向右查找时加的锁不会退化为间隙锁。
6. 无论什么情况下，InnoDB 会往前扫描到第一个不满足条件的行为止。