Next-Key Lock

前置知识

==行锁（Record Lock）==：把某一行的记录给锁住

==间隙锁（Gap Lock)==：把两个已存在的行记录之间的空间隙锁住，不允许其他事务对该区间内进行数据插入等操作

==Next-Key Lock = 行锁 + 间隙锁==，每个Next-Key Lock是一个前开后闭区间

如果查询一条记录时，命中记录则加行锁，未命中记录则加该区间对应的间隙锁。

Next-key Lock

加锁规则：两原则两优化和一个bug

1. 原则1：加锁的基本单位是next-key lock，next-key lock是前开后闭区间。
2. 原则2：查找过程中访问到的对象才会加锁。
3. 优化1：索引上的等值查询，给唯一索引加锁的时候，next-key lock退化为行锁。（注意案例2)
4. 优化2：索引上的等值查询，从第一个满足等值条件的索引记录开始向右遍历到第一个不满足等值条件记录，并将第一个不满足等值条件记录上的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. 根据原则1，加锁单位是next-key lock，session A加锁范围就是(5,10]；
2. 同时根据优化2，这是一个等值查询(id=7)，而id=10不满足查询条件，next-key lock退化成间隙锁，因此最终加锁的范围是(5,10)。

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

1. 根据原则1，加锁单位是next-key lock，因此会给(0,5]加上next-key lock。
2. 要注意c是普通索引，因此仅访问c=5这一条记录是不能马上停下来的，需要向右遍历，查到c=10才放弃。根据原则2，访问到的都要加锁，因此要给(5,10]加next-key lock。
3. 但是同时这个符合优化2：等值判断，向右遍历，最后一个值不满足c=5这个等值条件，因此退化成间隙锁(5,10)。（我理解是相当于给整体加了一个(0,10)的间隙锁）
4. 根据原则2 ，只有访问到的对象才会加锁，这个查询使用覆盖索引，并不需要访问主键索引，所以主键索引上没有加任何锁，这就是为什么session B的update语句可以执行完成。

案例三：主键索引范围锁

1. 开始执行的时候，要找到第一个id=10的行，因此本该是next-key lock(5,10]。 根据优化1， 主键id上的等值条件，退化成行锁，只加了id=10这一行的行锁。
2. 范围查找就往后继续找，找到id=15这一行停下来，因此需要加next-key lock(10,15]。（注意不退化！)

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

这次session A用字段c来判断，加锁规则跟案例三唯一的不同是：在第一次用c=10定位记录的时候，索引c上加了(5,10]这个next-key lock后，由于索引c是非唯一索引，没有优化规则，也就是说不会蜕变为行锁，因此最终sesion A加的锁是，索引c上的(5,10] 和(10,15] 这两个next-key lock（对比等值锁案例二）

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

session A是一个范围查询，按照原则1的话，应该是索引id上只加(10,15]这个next-key lock，并且因为id是唯一键，所以循环判断到id=15这一行就应该停止了。

但是实现上，InnoDB会往前扫描到第一个不满足条件的行为止，也就是id=20。而且由于这是个范围扫描，因此索引id上的(15,20]这个next-key lock也会被锁上

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

再插入一条记录

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

类似与案例二，此时加锁的范围如下所示（间隙锁，不包含两侧）：

案例七：limit 语句加锁

不同于案例六，案例七里的delete语句明确加了limit 2的限制，因此在遍历到(c=10, id=30)这一行之后，满足条件的语句已经有两条，循环就结束了。

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

这个例子对我们实践的指导意义就是，在删除数据的时候尽量加limit。这样不仅可以控制删除数据的条数，让操作更安全，还可以减小加锁的范围。

案例八：一个死锁的例子

1. session A 启动事务后执行查询语句加lock in share mode，在索引c上加了next-key lock(5,10] 和间隙锁(10,15)；
2. session B 的update语句也要在索引c上加next-key lock(5,10] ，进入锁等待；
3. 然后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的行锁，这时候才被锁住的。

上面的所有案例都是在可重复读隔离级别(repeatable-read)下验证的

一个附加思考题

1. 由于是order by c desc（降序），第一个要定位的是索引c上“最右边的”c=20的行，此时查找第一个值是等值查询（如果仅仅是<号，则只会加(15,20]的锁），所以会加上间隙锁(20,25)和next-key lock (15,20]。
2. 在索引c上向左遍历(因为desc），要扫描到c=10才停下来（确保所有的c=15都包含了），所以next-key lock会加到(5,10]，这正是阻塞session B的insert语句的原因。
3. 在扫描过程中，c=20、c=15、c=10这三行都存在值，由于是select *，所以会在主键id上加三个行锁。

因此，session A 的select语句锁的范围就是：

1. 索引c上 (5, 25)；
2. 主键索引上id=10、15、20三个行锁。

如果是升序（asc）的话，加锁的范围就是（10，25]

参考

MySQL 实战 45 讲