MySQL加锁规则( 二 ) _MySQL加锁

案例五（唯一索引范围锁 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 语句加锁）

文章插图
【MySQL加锁规则】
这个例子里，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 来分析。但是要知道，具体执行的时候，是要分成间隙锁和行锁两段来执行的。