MySQL加锁规则

1.加锁规则

原则 1：加锁的基本单位是 next-key lock。希望你还记得，next-key lock 是前开后闭区间。原则 2：查找过程中访问到的对象才会加锁。优化 1：索引上（唯一索引）的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。（也不会向右遍历了,因此不会增加右侧的间隙锁）（必须是记录匹配的情况下）优化 2：索引上（唯一与非唯一索引）的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。（记录匹配或不匹配都可，匹配上了，如果是唯一索引，就加行锁，如果是非唯一索引，就加next-key lock；匹配不上，无论是唯一索引还是非唯一索引，都加间隙锁）一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止（针对范围锁，无论是唯一索引还是非唯一索引，都要访问到不满足条件的第一个值为止）。

已知数据库中执行insert into t values(0,0,0),(5,5,5),(10,10,10),(15,15,15),(20,20,20),(25,25,25)

案例一（主键索引等值查询，加锁的情况）

由于表 t 中没有 id=7 的记录，所以用我们上面提到的加锁规则判断一下的话：

根据原则 1，加锁单位是 next-key lock，session A 加锁范围就是 (5,10]；（这里为啥是(5,10]其实没有说出规则，只能猜测只有当前这个next-key lock能锁住id=7）同时根据优化 2，这是一个等值查询 (id=7)，由于不存在id=7，只能向右遍历，而 id=10又不满足查询条件，next-key lock 退化成间隙锁，因此最终加锁的范围是 (5,10)。

案例二（非唯一索引等值查询，加锁情况）

根据原则 1，加锁单位是 next-key lock，因此会给 (0,5]加上 next-key lock。要注意 c 是普通索引，因此仅访问 c=5 这一条记录是不能马上停下来的，需要向右遍历，查到 c=10 才放弃。根据原则 2，访问到的都要加锁，因此要给 (5,10]加 next-key lock。但是同时这个符合优化 2：等值判断，向右遍历，最后一个值不满足 c=5 这个等值条件，因此退化成间隙锁 (5,10)。根据原则 2 ，只有访问到的对象才会加锁，这个查询使用覆盖索引，并不需要访问主键索引，所以主键索引上没有加任何锁，这就是为什么 session B 的 update 语句可以执行完成。但 session C 要插入一个 (7,7,7) 的记录，就会被 session A 的间隙锁 (5,10) 锁住。需要注意，在这个例子中，lock in share mode 只锁覆盖索引，但是如果是 for update 就不一样了。 执行 for update 时，系统会认为你接下来要更新数据，因此会顺便给主键索引上满足条件的行加上行锁。这个例子说明，锁是加在索引上的；同时，它给我们的指导是，如果你要用 lock in share mode 来给行加读锁避免数据被更新的话，就必须得绕过覆盖索引的优化，在查询字段中加入索引中不存在的字段。比如，将 session A 的查询语句改成 select d from t where c=5 lock in share mode。

案例三（主键（唯一）索引范围锁）

现在我们就用前面提到的加锁规则，来分析一下 session A 会加什么锁呢？开始执行的时候，要找到第一个 id=10 的行，因此本该是 next-key lock(5,10]。 根据优化 1， 主键 id 上的等值条件，退化成行锁（属于唯一索引），只加了 id=10 这一行的行锁。范围查找就往后继续找，找到 id=15 这一行停下来，因此需要加 next-key lock(10,15]。所以，session A 这时候锁的范围就是主键索引上，行锁 id=10 和 next-key lock(10,15]。这样，session B 和 session C 的结果你就能理解了。这里你需要注意一点，首次 session A 定位查找 id=10 的行的时候，是当做等值查询来判断的，而向右扫描到 id=15 的时候，用的是范围查询判断。

案例四（非唯一索引范围锁）

这次 session A 用字段 c 来判断，加锁规则跟案例三唯一的不同是：在第一次用 c=10 定位记录的时候，索引 c 上加了 (5,10]这个 next-key lock 后，由于索引 c 是非唯一索引，没有优化规则，也就是说不会蜕变为行锁，因此最终 sesion A 加的锁是，索引 c 上的 (5,10] 和 (10,15] 这两个 next-key lock。所以从结果上来看，sesson B 要插入（8,8,8) 的这个 insert 语句时就被堵住了。这里需要扫描到 c=15 才停止扫描，是合理的，因为 InnoDB 要扫到 c=15，才知道不需要继续往后找了。

案例五（唯一索引范围锁 bug）

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。这里为什么没有加(5,10]的next-key lock，因为"对访问到的对象加锁"，id>10 and id<=15访问到的对象是15,20，因此加的锁是(10,15],(15,20]; 不会访问到id=10这一条记录的，因此没有(5,10]这个锁。

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

新插入的这一行 c=10、id=30，也就是说现在表里有两个 c=10 的行，如下图。那么，这时候索引 c 上的间隙是什么状态了呢？你要知道，由于非唯一索引上包含主键的值，所以是不可能存在“相同”的两行的。

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

可以看到，虽然有两个 c=10，但是它们的主键值 id 是不同的（分别是 10 和 30），因此这两个 c=10 的记录之间，也是有间隙的。图中我画出了索引 c 上的主键 id。为了跟间隙锁的开区间形式进行区别，我用 (c=10,id=30) 这样的形式，来表示索引上的一行。现在，我们来看一下案例六。这次我们用 delete 语句来验证。注意，delete 语句加锁的逻辑，其实跟 select ... for update 是类似的（要update和delete的时候，要“先读后写”，这个读就开始加锁了。insert的时候要有插入意向锁（就是会跟gap lock冲突的那个），因此执行 “delete from t where c=10;” 语句，索引c上的next-key lock是(5,10],(10,15)。那么主键索引上的锁只有行锁，锁住的是 (10,10,10) 和 (30,10,30) 两行，因此先“读”，一旦读就会回表扫描到主键索引），也符合上边的原则，也就是我在文章开始总结的两个“原则”、两个“优化”和一个“bug”。

这时，session A 在遍历的时候，先访问第一个 c=10 的记录。同样地，根据原则 1，这里加的是 (c=5,id=5) 到 (c=10,id=10) 这个 next-key lock（也就是(5,10]）。然后，session A 向右查找，直到碰到 (c=15,id=15) 这一行（也就是(10,15]），循环才结束。根据优化 2，这是一个等值查询，向右查找到了不满足条件的行，所以会退化成 (c=10,id=10) 到 (c=15,id=15) 的间隙锁（也就是(10,15)）。也就是说，这个 delete 语句在索引 c 上的加锁范围，就是下图中蓝色区域覆盖的部分。

案例七（limit 语句加锁）

这个例子里，session A 的 delete 语句加了 limit 2。你知道表 t 里 c=10 的记录其实只有两条，因此加不加 limit 2，删除的效果都是一样的，但是加锁的效果却不同。可以看到，session B 的 insert 语句执行通过了，跟案例六的结果不同。这是因为，案例七里的 delete 语句明确加了 limit 2 的限制，因此在遍历到 (c=10, id=30) 这一行之后，满足条件的语句已经有两条，循环就结束了。因此，索引 c 上的加锁范围就变成了从（c=5,id=5) 到（c=10,id=30) 这个前开后闭区间，如下图所示：

可以看到，(c=10,id=30）之后的这个间隙并没有在加锁范围里，因此 insert 语句插入 c=12 是可以执行成功的。这个例子对我们实践的指导意义就是，在删除数据的时候尽量加 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 的行锁，这时候才被锁住的。（这也就是为什么sessionB无法加(10,15)间隙锁的原因，因为此时它已经被阻塞，无法继续扫描了）也就是说，我们在分析加锁规则的时候可以用 next-key lock 来分析。但是要知道，具体执行的时候，是要分成间隙锁和行锁两段来执行的。

当对索引加锁时，如果这个索引所对应的行记录存在，则加行锁+间隙锁，如果这个记录不存在，则对这个索引所在的记录间隙加间隙锁，如下。

update t set d= 10 where c = 7

如果7不存在，则加(5,10)，如果7存在，则加(5,7] 。