微信公众号：欢少的成长之路

在上篇文件我们介绍了由幻读的锁引出了间隙锁这个概念。并且介绍了一下加锁规则优化。今天我们接着上篇文章继续延伸一下技术点。

概念

原则

• 原则 1：加锁的基本单位是 next-key lock。希望你还记得，next-key lock 是前开后闭区间。

• 原则 2：查找过程中访问到的对象才会加锁。

优化

• 优化1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。

• 优化2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。

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);

等值查询间隙锁（一）

第一个案例是关于等值的查询的间隙锁的加锁规则。如下图，我们分析一下

sessionA

• sessionA 修改id为7的d数据。那么会给d这行数加锁。但是没有7这条数据，根据加锁规则，加在5和10的位置。

• 因为id=7 是等值查询，所以根据原则1，加锁单位next-key lock 变成(5,10]。

• 根据优化2，next-key lock 退化成间隙锁，因此最终就改成(5,10)。

sessionB

• 插入一条8这条数据。这条数据在5-10之间（且不等于），所以sessionB会被sessionA锁住

sessionC

• 修改id=10的这条数据。由上述可知，sessionB没有执行成功，sessionA对数据5-10之间（且不等于）加了间隙锁。所以sessionC可以执行成功。

非唯一索引等值锁（二）

关于覆盖索引上的锁。如下图所示，我们分析一下

sessionA

• c是索引key，要给c=5这条记录加读锁。

• 按照原则1，需要给(0-5]加next-key lock。

• 因为c是普通索引，不是唯一索引或者主键，所以要向右扫描，扫描到的行都要加索引，到了下个目标停止也就是10。按照加锁条例(5,10]加next-key-lock。

• 根据优化2，等值判断，向右遍历，最后一个不满足退化为间隙锁。(5,10)

sessionB

• 根据原则2，访问到的才加锁，这里是覆盖索引。并不需要访问主键索引。所以主键上没有加任何锁。所以sessionB是可以执行成功的。

覆盖索引没想明白的话可以看看SQL。select id 只走索引树，不走其他数据。

解释：id=5 这里只是查询，需要读锁，读锁共享，可以查。改的数据是改的d，d没有加

sessionC

直接锁住。因为sessionA锁的是（5,10）。你插入一个第7行肯定是要被锁住的。

总结：

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

2. 锁是加在索引上的；同时，它给我们的指导是，如果你要用 lock in share mode 来给行加读锁避免数据被更新的话，就必须得绕过覆盖索引的优化，在查询字段中加入索引中不存在的字段。

主键索引范围锁（三）

这里介绍的是范围锁规则。比如>=5 <10之类的。如下图所示，我们一起分析一下

select * from t where id=10 for update;
select * from t where id>=10 and id<11 for update;

我们可以试想一下，这两条SQL相同吗？

从语义上来说是相同的，但是从逻辑加锁规则来说是不相同的。

sessionA

• 要先找到id为10的数据，因此要加 next-key-lock 到(5,10]。

• 根据优化1，退化为行锁。所以只加id为10这一条记录上的锁。

• 范围查找继续向右遍历，找到了15然后进行判断是否小于11。因此加了 next-key-lock (10,15]的锁。

因此sessionA一共锁了两处锁，id为10的行锁以及 next-key-lock (10,15]的锁。

sessionB

• 插入8这条数据的时候没有冲突sessionA的锁，所以插入正常。

• 在插入13的时候就触发了sessionA的next-key-lock (10,15]锁，所以发现锁住

sessionC

• 更新15这条数据。案例三上面提到的锁索引树，那里是覆盖索引只锁树，这里不一样。所以是都锁。sessionC也执行失败被锁住。

非唯一索引范围锁（四）

如下图所示我们继续分析

sessionA

• 先查找c=10这条记录，找到之后加写锁。因为索引C只是普通索引不是唯一索引，所以不会利用规则优化。(5,10]

• 继续向右遍历，找到15判断是否小于11。不满足。加next-key-lock (10,15]

sessionB

• 插入8这条记录，被sessionA锁住了。

sessionC

• sessionA是普通索引，不是唯一索引，所以没有退化成间隙锁，所以是(10,15]不是(10,15)。所以session也被锁住了

唯一索引范围锁 bug（五）

声明一下 案例五完全是抄袭丁奇老师的MySQL45讲。本人的所有文章都是自己手写，学习途径就是丁奇老师。所以喷子喷之前请看好文章，我怕喷子看不见不好使，我给加粗一下

如下图所示我们继续分析

sessionA

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

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

所以你看到了，session B 要更新 id=20 这一行，是会被锁住的。同样地，session C 要插入 id=16 的一行，也会被锁住。

照理说，这里锁住 id=20 这一行的行为，其实是没有必要的。因为扫描到 id=15，就可以确定不用往后再找了。但实现上还是这么做了，因此我认为这是个 bug。

我也曾找社区的专家讨论过，官方 bug 系统上也有提到，但是并未被 verified。所以，认为这是 bug 这个事儿，也只能算我的一家之言，如果你有其他见解的话，也欢迎你提出来。

非唯一索引上存在"等值"的例子（六）

来说明一下 “间隙”这个概念。给表 t 插入一条新记录。

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

新插入的这一行 c=10，也就是说现在表里有两个 c=10 的行。由于非唯一索引上包含主键的值，所以是不可能存在“相同”的两行的。

可以看到，虽然有两个 c=10，但是它们的主键值 id 是不同的（分别是 10 和 30），因此这两个 c=10 的记录之间，也是有间隙的。

图中我画出了索引 c 上的主键 id。为了跟间隙锁的开区间形式进行区别，我用 (c=10,id=30) 这样的形式，来表示索引上的一行。如下图所示

sessionA

• 这里的delete与select ... for update 是类似的。先访问第一个 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) 的间隙锁。

上面五个案例走过来。大家应该能推出来sessionB与sessionC的执行结果的原因吧。这里就不做介绍了。12被锁了，15是开区间没有被包含住。

limit 语句加锁（七）

如下图所示，用的是示例6的delete例子。

sessionA

• 查找c为10的那条记录，给(5,10] 加 next-key-lock。最重要的就是limit 2这里，改变了整个命运。

• 因为c为10的数据，一共就两条。当查出来之后，就直接确定了，不需要再向后遍历了。

这里说一下，为啥我说的改变命运，如果继续循环判断的话。有可能会把下一次遍历的值也加锁。那么我们可以试想一下，如果下一条遍历对象是c=40，而c=40刚好又非常多。那么就造成了大面积加锁。

科普：所以平时在写删除语句的时候，如果可以确保是一条记录。可以加一下limit 1。这里节省性能。这是一个比较好的习惯。

sessionB

• 这里验证了上述的加锁，插入12这条记录的时候成功了。说明没有找到 15 就停止了。

一个死锁的例子（八）

这里我们 说明：next-key lock 实际上是间隙锁和行锁加起来的结果。如下图所示

• session A 启动事务后执行查询语句加 lock in share mode，在索引 c 上加了 next-key lock(5,10] 和间隙锁 (10,15)；

解释：c=10查找完之后，不是唯一索引，所以向右遍历，所以15是 闭区间 ] 不是开区间 )又因为输出的是id，所有走的覆盖索引。这里被退化了 所以是15开区间。

• session B 的 update 语句也要在索引 c 上加 next-key lock(5,10] ，进入锁等待；

• session A 要再插入 (8,8,8) 这一行，被 session B 的间隙锁锁住。由于出现了死锁，InnoDB 让 session B 回滚。

这里解释一下sessionB的blocked。

session B 的“加 next-key lock(5,10] ”操作，实际上分成了两步，先是加 (5,10) 的间隙锁，加锁成功；然后加 c=10 的行锁，这时候才被锁住的。也就是说，我们在分析加锁规则的时候可以用 next-key lock 来分析。但是要知道，具体执行的时候，是要分成间隙锁和行锁两段来执行的。

总结

这里我们举例说明了可重复隔离级别下的加锁规则，以及锁规则优化，以及next-key-lock的两阶段提交。

相信对next-key-lock 有了新的认识

但是还是不够，我们下面继续延伸学习。