mysql limit锁_MySQL锁（四）行锁的加锁规则和案例

在上一篇文章，我们学习了间隙锁和next-key lock，但是不知道怎么加锁，有哪些规则。间隙锁的概念不太好理解，尤其是配合上行锁后，很容易在判断是否会出现锁等待的问题上犯错。

今天我们就来学习一下加锁规则吧。

在学习前要说明一点，以下的规则只限于版本范围：5.x系列<=5.7.24，8.0系列<=8.0.13。

加锁规则

这个加锁规则包含两个“原则”、两个“优化”和一个“bug”。

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

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

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

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

一个bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

下面以表t为例来介绍一下这些规则。表t的建表语句和初始化语句如下。

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 等值查询间隙锁

分析：

步骤1：根据原则1，加锁(5, 10]

步骤2：根据优化2，id=10不满足查询条件，因此退化为间隙锁(5, 10)

结论：

Session B阻塞是因为id=8在间隙锁(5, 10)内

Session C可以执行是因为没有锁住id=10这行

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

图2 非唯一索引等值锁

分析：

步骤1：根据原则1，加锁(0, 5]

步骤2：由于c是普通索引，因此还需要继续遍历，直到找到c=10，不满足条件。根据原则2，访问到的对象要加锁，因此要加(5, 10]。

步骤3：同时根据优化2，这是一个等值查询，向右遍历的不满足条件的第一个值10，(5, 10]要退化为间隙锁(5, 10)

因此加锁是索引c的next-key lock(0,5]和间隙锁(5,10)

结论：

Session B可以执行是因为加锁的是索引c，而不是主键索引

Session C阻塞是因为c的插入值是7，在间隙锁(5, 10)范围内

案例3：主键索引范围锁

图3 主键索引范围锁

分析：

步骤1：根据原则1，加锁(5, 10]

步骤2：根据优化1，退化为id=10的行锁

步骤3：继续遍历，找到不满足id<11的值id=15，加锁(10, 15]

因此加锁id=15的行锁和id的next-key lock(10, 15]

结论：

插入id=13被阻塞：next-key lock (10, 15]

更新id=15被阻塞：next-key lock (10, 15]

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

图4 非唯一索引范围锁

分析：

步骤1：根据原则1，加锁(5, 10]

步骤2：继续遍历，找到不满足c<11的值c=15，根据原则2，加锁(10, 15]

因此加锁索引c (5, 10]和(10, 15]

结论：

插入c=8，被(5, 10]阻塞

更新c=15，被(10, 15]阻塞

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

图5 唯一索引范围锁bug

分析：

步骤1：根据原则1，加锁(10, 15]，再根据优化1，退化为id=15的行锁

步骤2：向右遍历，加锁(10, 15]

步骤3：根据BUG，要访问到不满足条件的第一个值，即id=20，加锁(15 ,20]。

因此加锁为(10, 15]和(15, 20]

结论：

更新id=20阻塞，被(15, 20]锁住

插入id=16阻塞，被(15, 20]锁住

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

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

图6 非唯一索引上存在"等值"的例子

分析：

步骤1：根据原则1，(c=5,id=5)到(c=10,id=10)这个next-key lock

步骤2：向右查找，直到碰到(c=15,id=15)这一行，循环才结束。根据优化2，这是一个等值查询，向右查找到了不满足条件的行，所以会退化成(c=10,id=10) 到 (c=15,id=15)的间隙锁

因此加锁(c=5,id=5)到(c=10,id=10)这个next-key lock和(c=10,id=10)到(c=15,id=15)这个间隙锁

结论：

插入c=12阻塞，被(c=10,id=10)到(c=15,id=15)这个间隙锁锁住

更新c=15成功，没有锁住c=15

案例7：limit 语句加锁

先插入一条记录。

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

图7 limit 语句加锁

分析：

步骤1：根据原则1，(5, 10]，因为c=10有两条行，因此遍历到这里就结束

步骤2：因为是delete，因此加两个行锁(id=10和id=30)

因此加锁c (5, 10)和两个行锁(id=10和id=30)

结论：

插入c=12成功，因为c=12没有被锁住

说明：这个例子对我们实践的指导意义就是，在删除数据的时候尽量加limit。

案例8：一个死锁的例子

这个案例的目的是说明：next-key lock实际上是间隙锁和行锁加起来的结果。

图8 一个死锁的例子

分析：

步骤1：根据原则1，加锁(5, 10]

步骤2：继续遍历，直到c=15不满足条件，加锁(10, 15]，根据优化2，退化为(10, 15)

结论：

Session B在等待锁，此时Session B已经加了间隙锁(5, 10)，在等待加行锁c=10。

Session A插入c=8，也在等待锁，从而导致死锁

说明：next-key lock具体执行的时候，是要分成间隙锁和行锁两段来执行的。

案例9：非唯一索引排序范围锁

图9 非唯一索引排序范围锁

分析：

步骤1：先执行c=20，加锁(15, 20]

步骤2：根据优化2，加间隙锁(20, 25)

步骤3：再执行c=15，加锁(10, 15]

步骤4：继续向左遍历，找到记录id=10为止，加锁(5, 10]

在扫描过程中，c=20、c=15、c=10这三行都存在值，由于是select *，所以会在主键id上加三个行锁。

因此要加锁索引c (5, 25)和三个行锁(id=10，id=15，id=20)。

结论：

插入c=6，被c(5, 25)锁住

案例10：不等号条件里的等值查询

begin;

select * from t where id>9 and id<12 order by id desc for update;

在执行过程中，通过树搜索的方式定位记录的时候，用的是“等值查询”的方法。

分析：

步骤1：根据原则1，加锁 (10, 15]

步骤2：根据优化2，退化为(10, 15)

步骤3：向左遍历，找到id=10，加锁(5, 10]，继续找到id=5为止，加锁(0, 5]

案例11：in范围锁

begin;

select id from t where c in(5,20,10) lock in share mode;

分析：

说明：锁是逐个逐个加的。

步骤1：先c=5，加(0, 5]和(5, 10)

步骤2：再c=10，加(5, 10]和(10, 15)

步骤3：后c=20，加(15, 20]和(20, 25)

间隙锁是不互斥的，因为加锁范围是(0, 25)，除c=15外。

死锁情况

select id from t where c in(5,20,10) order by c desc for update;

有一种情况，同时执行倒序语句，因为刚好同时执行，逐渐加锁(倒序加锁)，会出现死锁情况。

参考资料