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:等值查询间隙锁
![](https://static001.geekbang.org/resource/image/58/6c/585dfa8d0dd71171a6fa16bed4ba816c.png)
图1 等值查询间隙锁
分析:
- 步骤1:根据原则1,加锁(5, 10]
- 步骤2:根据优化2,id=10不满足查询条件,因此退化为间隙锁(5, 10)
结论:
- Session B阻塞是因为id=8在间隙锁(5, 10)内
- Session C可以执行是因为没有锁住id=10这行
案例2:非唯一索引等值锁
![](https://static001.geekbang.org/resource/image/46/65/465990fe8f6b418ca3f9992bd1bb5465.png)
图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:主键索引范围锁
![](https://static001.geekbang.org/resource/image/30/80/30b839bf941f109b04f1a36c302aea80.png)
图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:非唯一索引范围锁
![](https://static001.geekbang.org/resource/image/73/7a/7381475e9e951628c9fc907f5a57697a.png)
图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
![](https://static001.geekbang.org/resource/image/b1/6d/b105f8c4633e8d3a84e6422b1b1a316d.png)
图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);
![](https://static001.geekbang.org/resource/image/b5/78/b55fb0a1cac3500b60e1cf9779d2da78.png)
图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);
![](https://static001.geekbang.org/resource/image/af/2e/afc3a08ae7a254b3251e41b2a6dae02e.png)
图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实际上是间隙锁和行锁加起来的结果。
![](https://static001.geekbang.org/resource/image/7b/06/7b911a4c995706e8aa2dd96ff0f36506.png)
图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:非唯一索引排序范围锁
![](https://static001.geekbang.org/resource/image/3a/1e/3a7578e104612a188a2d574eaa3bd81e.png)
图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外。