心灵拷问:MySQL 到底是怎么加行级锁、间隙锁、临键锁的?
讲之前,先唠点5毛钱的基础小知识。我们都知道 MySQL 有全局锁、表记锁和行级别锁,其中行级锁加锁规则比较复杂,不同的场景,加锁的形式还不同。
需要明确的是:对记录加锁时,加锁的基本单位是 next-key lock,它是由记录锁和间隙锁组合而成的,next-key lock 是前开后闭区间,而间隙锁是前开后开区间。而 next-key lock 在一些场景下会退化成记录锁或间隙锁。
先回顾一下:锁类型
- 共享锁 (S锁):假设事务T1对数据A加上共享锁,事务T2可以读数据A,不能修改数据A。
- 排他锁 (X锁):假设事务T1对数据A加上排他锁,事务T2不能读也不能修改数据A。
- 意向共享锁 (IS锁):事务在获取(任何一行/或全表)S锁前,一定会先在所在的表上加IS锁。
- 意向排他锁 (IX锁):事务在获取(任何一行/或全表)X锁前,一定会先在所在的表上加IX锁。
了解行级锁:MySQL InnoDB支持三种行锁定方式
InnoDB默认加锁方式是next-key。如果某个加锁操作未使用到索引,该锁则会退化为表锁。
- 行锁 (Record Lock):存在唯一索引中 (包含主键索引),锁是在加索引上而不是行上的,也就是key。innodb一定存在聚簇索引,因此行锁最终都会落到聚簇索引上!
- 间隙锁 (Gap Lock):存在非唯一索引中,锁定索引记录间隙,确保索引记录的间隙不变。
- 临键锁 (Next-Key Lock) :一种特殊的间隙锁 = 行锁+间隙锁,除了锁住记录本身,还会锁住索引之间的间隙,即锁定一段左开右闭的索引区间
✈ 那么何时使用行锁,何时产生间隙锁?
- 只使用唯一索引查询,并且只锁定一条记录时,innoDB会使用行锁。
- 只使用唯一索引查询,但是检索条件是范围检索,或者是唯一检索但检索结果不存在(试图锁住不存在的数据)时,会产生 Next-Key Lock(临键锁)。
- 使用普通索引检索时,不管是何种查询,只要加锁,都会产生间隙锁(Gap Lock)。
- 同时使用唯一索引和普通索引时,由于数据行是优先根据普通索引排序,再根据唯一索引排序,所以也会产生间隙锁。
☁ 扩展小知识:为什么要有间隙锁?
是为了防止幻读,其主要通过两个方面实现这个目的
(1)防止间隙内有新数据被插入
(2)防止已存在的数据,更新成间隙内的数据
RU
和 RC
事务隔离级别下的加锁
前导:
RU
和RC
不存在间隙锁!如果隔离级别为RU
和RC
,无论条件列上是否有索引,都不会锁表,只锁行!而所谓的“锁表”,其原理是通过行锁+间隙锁来实现的。
假设我们的数据表如下:
id (主键索引) | name (无索引) | age (普通索引) |
---|---|---|
1 | 张三 | 18 |
2 | 李四 | 20 |
3 | 王五 | 30 |
7 | 赵六 | 20 |
查看以下6种查询:等值/范围 加锁情况
select * from table where id = 2 ; select * from table where id < 2 ;select * from table where id = 2 lock in share mode;select * from table where id < 2 lock in share mode;select * from table where id = 2 for update;select * from table where id < 2 for update;
**➳ 解析:**这2种隔离级别下,无论是范围查询还是等值查询,只存在2种加锁类型:读锁、写锁。
注意:如果上述where条件中换成无索引的name,与本例中条件是主键索引的加锁情况一致。实际内部不一样,在RC/RU隔离级别中,MySQL Server做了优化。在条件列没有索引的情况下,尽管通过聚簇索引来扫描全表,进行全表加锁。但MySQL Server层会进行 过滤把不符合条件的锁当即释放掉,因此看起来最终结果一样。实际内部多了一个释放不符合条件的锁的过程而已
RR级别下加锁范围:环境准备
不同版本的加锁规则存在差异,将以 MySQL 8.0.25 为例,验证 next-key lock 加锁范围。
MySQL 版本:8.0.27
隔离级别:可重复读(RR)
存储引擎:InnoDB
构建数据表:
▎非唯一索引的等值查询
当我们用非唯一索引进行等值查询的时候,查询的记录存不存在,加锁的规则也会不同。
★ 加锁规则
- 查询的记录存在:除了会加 next-key lock 外,还额外加Gap Lock间隙锁,即加两把锁。
- 记录不存在时:只会加 next-key lock,然后会退化为Gap Lock间隙锁,即只会加一把锁。
☛ 等值查询:值存在
针对非唯一索引等值查询,查询的值存在,加2把锁: next-key lock + 间隙锁。
1. 假设下面2个事务,进行对数据的操作顺序如下
时间顺序 | 事务A | 事务B |
---|---|---|
1 | begin; -- 开始事务 | |
2 | select * from demo where age=21 lock in share mode; -- 查询age=21的记录,并加读锁(共享锁) | |
3 | begin; -- 开始事务 | |
4 | .... 此处事务B操作,下方展示 | |
5 | commit ; -- 提交事务 |
∞ 事务A加锁变化过程
① 对普通索引 age 加上 next-key lock,临键锁本身是左开右闭的区间,即范围是(19,21],也就是说,除了锁定age=21 这条记录本身,还锁定了索引节点上 age=21 前面的那个间隙。
② 因为是非唯一索引,且查询的记录是存在的**(加锁规则1)**,所以还会加上Gap Lock间隙锁,规则是向下遍历到第一个不符合条件的值才能停止,因此间隙锁的范围是(21,24)
**➳ 结论:**事务A的普通索引 age上共有两个锁,分别是 next-key lock(19,21] 和 间隙锁(21,24) 。
2. 假设事务B的数据操作如下
得知age共有2个锁,其锁范围 (19,21]、(21,24),也就是总范围是:(19,24),如果另外一个事务B在此锁范围内进行数据的操作会如何?
» 测试第一把锁范围:age临键锁(19-21]
**示例a:**针对锁范围 (19,21],其上区间边界值age=19的数据修改
update demo set name='梅花十三' where age=19; -- 成功
**示例b:**针对锁范围 (19,21],其上区间边界值age=19,相同数据插入
insert into demo(id, age, name) VALUES (4,19,'张三'); insert into demo(id, age, name) VALUES (7,19,'张三'); insert into demo(id, age, name) VALUES (20,19,'张三');
-- 思考:为什么age相同的情况都是19,其id=4的记录能够插入成功,而id=7或20的插入失败!
**✦ 解析:插入的数据在区间的边界值,则根据主键来判断锁定范围。**针对锁范围 (19,21] ,其锁相对应的id范围是(5,8),如果新插入的数据,其age值是上区间边界值19,则其插入的主键必须是小于 age=19所对应的id值5。主键<5的可以插入,>5的不可以,否则插入失败。
**示例c:**插入/修改 (19,21] 区间内的数据——20
insert into demo(id, age, name) VALUES (4,20,'张三'); insert into demo(id, age, name) VALUES (7,20,'张三'); update demo set name='梅花' where age=20; 注:age=20在临键锁(19-21]范围内,因此插入的id是否大于其边界值[id=5,age=19]都是失败的
**示例d:**插入/修改 (19,21] 的后闭范围—— 21
insert into demo(id, age, name) VALUES (7,21,'张三'); update demo set name='梅花update' where age=21;
-- 注:21处于(19,21] 的后闭范围,因此age=21这条索引是被锁住的,无论修改还是新增都失败
» 测试第二把锁范围:age间隙锁(21-24)
**示例a:**针对间隙锁(21,24)范围内的区间值 ——22、23
insert into demo(id, age, name) VALUES (9,22,'张三'); insert into demo(id, age, name) VALUES (9,23,'张三');
**示例b:**针对间隙锁(21,24)的下区间边界值 ——24
update demo set name='梅花update' where age=24; insert into demo(id, age, name) VALUES (9,24,'张三'); insert into demo(id, age, name) VALUES (11,24,'张三');
-- 思考:为什么age相同的情况都是24,其id=9的记录失败了,而id=11的插入成功!
✦ 解析:插入的数据在区间的边界值,则根据主键来判断锁定范围。针对锁范围 (21,24),下区间边界值是24,其对应的id为10,如果新插入的数据,其age值是下区间边界值24,则其插入的主键必须是大于 age=24所对应的id值10。即主键>10的可以插入,<10不可以,否则插入失败。
**➳ 注意:**针对插入的数据在区间的边界值,则根据主键来判断锁定范围。这里需要区分一个点,是上区间还是下区间,如下图,即便插入的是边界值,最终它封锁的还是 (19,24) 这个范围。
☛ 等值查询:值不存在
针对非唯一索引等值查询,查询的记录不存在,则加 next-key lock,但会退化为Gap Lock间隙锁,即只会加一把锁。
1. 事务顺序如下
时间顺序 | 事务A | 事务B |
---|---|---|
1 | begin; -- 开始事务 | |
2 | select * from demo where age=17 lock in share mode; (查询一个不存在的记录) | |
3 | begin; -- 开始事务 | |
4 | .... 此处事务B操作,下方展示 | |
5 | commit ; -- 提交事务 |
∞ 事务A加锁变化过程
① 加锁查询age=17的记录,先会对普通索引 age 加上 next-key lock,范围是(16,19]。
② 由于查询的记录不存在**(加锁规则2)**,所以不会再额外加个间隙锁,但next-key lock 会退化为间隙锁,最终加锁范围是 (16,19)。
2. 假设事务B的操作如下
根据加锁规则2:非唯一索引等值查询记录不存在时,只会加next-key lock,并退化为Gap Lock间隙锁,即只会加一把锁。故查询age=17获得临键锁(16,19] ,并退化成间隙锁 (16,19)
» 测试锁范围:age间隙锁(16-19)
**示例a:**操作上区间边界值——16
update demo set name='梅花十三' where age=16; insert into demo(id, age, name) VALUES (-1,16,'张三'); insert into demo(id, age, name) VALUES (2,16,'张三');
-- 注:插入的数据在区间的边界值,则根据主键来判断锁定范围
**✦ 解析:**同上述例子,针对上区间边界值16,如果新插入的数据age值也是16,则其插入的主键id必须小于age=16所对应的id值 1,也就是说 主键<1 的可以插入, 否则插入失败。
**示例b:**操作间隙锁(16,19)区间范围的数据——17、18
insert into demo(id, age, name) VALUES (-1,17,'张三'); insert into demo(id, age, name) VALUES (2,17,'张三'); insert into demo(id, age, name) VALUES (2,18,'张三');
**示例c:**操作间隙锁(16,19)下区间边界值——19
insert into demo(id, age, name) VALUES (4,19,'张三'); insert into demo(id, age, name) VALUES (6,19,'张三');
**✦ 解析:**同理,针对下区间边界值19,如果新插入的数据age值也是19,则其插入的主键id必须大于age=19所对应的id值 5,也就是说 主键>5 的可以插入, 否则插入失败。
**示例d:**操作间隙锁(16,19)范围外的数据
insert into demo(id, age, name) VALUES (2,15,'张三'); insert into demo(id, age, name) VALUES (6,20,'张三');
▎非唯一索引的范围查询
仔细查看下面案例
1. 事务A进行范围查询,如下:
select * from demo where age>=19 and age<22 lock in share mode;
∞ 事务A加锁变化过程如下:
① 首先最开始要找的记录是 age>=19,因此产生的临键锁 next-key lock 范围为:(16,19]
② 由于是范围查找,则继续往后找存在的记录,查询条件age<22 最终产生2个next-key lock,分别是 (19,21]、(21, 24]
2. 假设事务B的操作如下
根据事务A产生的三把next-key lock,范围分别是:(16,19]、 (19,21]、(21, 24],总范围可以得出:(16,24]
另外,因为是范围锁,因此针对age符合条件的记录,也就是在此锁范围内(16,24]的数据,都是无法修改和插入的,因为被锁定了,具体的操作大家可以试一下,这里就不一一演示了,这里主要讲下上下区间边界的16、24的数据操作。
» 测试上区间边界值——16
update demo set name='梅花十三' where age=16; insert into demo(id, age, name) VALUES (-1,16,'张三'); insert into demo(id, age, name) VALUES (2,16,'张三');
-- 注:插入的数据在区间的边界值,则根据主键来判断锁定范围
➳ 两个注意点
- 16虽然是上区间边界值,但属于临键锁,前开后闭,16是前开,因此可以修改
- 新插入的数据,其age是上区间的边界值16,但id=-1的可以插入成功,上述也讲了很多遍了:插入的数据在区间的边界值,则根据主键来判断锁定范围。数据库存在age=16的记录,其id=1,插入的是上区间边界值,只要插入的主键 小于 其数据库age=16对应的id值 1 即可。
» 测试下区间边界值——24
update demo set name='梅花十三' where age=24; insert into demo(id, age, name) VALUES (9,24,'张三'); insert into demo(id, age, name) VALUES (11,24,'张三');
-- 注:插入的数据在区间的边界值,则根据主键来判断锁定范围
➳ 两个注意点
- 24属于临键锁(21,24] 的下区间边界值,属于后闭原则,该条记录被锁定了,无法修改
- 新插入的数据其age是下区间的边界值24,牢记:插入的数据在区间的边界值,则根据主键来判断锁定范围。数据库存在age=24的记录,其id=10,针对插入的是下区间边界值,只要插入的主键 大于 其数据库age=24 对应的id值 10 就能插入。
▎唯一索引的等值查询
同样,唯一索引进行等值查询的时候,查询的记录存不存在,锁的规则也会不同。
★ 加锁规则
- 查询的记录存在:在用「唯一索引进行等值查询」时,next-key lock 会退化成「记录锁」
- 查询的记录不存在:在用「唯一索引进行等值查询」时,next-key lock 会退化成「间隙锁」
☛ 等值查询:值存在
针对唯一索引的等值查询,如果查询记录存在,next-key lock 退化成行锁。
1. 假设下面3个事务,进行对数据的操作顺序如下
事务A | 事务B | 事务C |
---|---|---|
begin; | ||
select * from demo where id=8 lock in share mode; (查询 id=8的记录) | ||
update demo set name='梅花十三' where id=8; ( **阻塞:**修改 id=8 的记录) | ||
insert into demo(id, age, name) values (6,21,'张三'); ( **正常:**插入 id =6 的记录) | ||
commit ; |
∞ 事务A加锁变化过程
① 查询 id=8的数据,加锁的基本单位是 next-key lock,因此事务A的加锁范围是 id (5, 8];
② 唯一索引(包括主键索引) 进行等值查询,且查询的记录存在,所以next-key lock 退化成记录锁,最终加锁的范围就是 id = 8 这一行。
**➳ 解析:**由于id=8退化为行锁,因此事务B修改 id=8的这条记录失败,而事务C插入id=6能够成功
☛ 等值查询:值不存在
针对唯一索引的等值查询,如果查询记录不存在,next-key lock 退化成间隙锁。
1. 假设下面3个事务,进行对数据的操作顺序如下
事务A | 事务B | 事务C |
---|---|---|
begin; | ||
select * from demo where id=6 lock in share mode; (查询 id=6 不存在的记录) | ||
insert into demo(id, age, name) values (6,18,'张三'); insert into demo(id, age, name) values (7,18,'张三'); ( **阻塞:**插入 id=6、7的记录) | ||
update demo set name='梅花十三' where id=5; update demo set name='梅花十三' where id=8; (**正常:**修改 id=5、8 的记录) | ||
commit ; |
∞ 事务A加锁变化过程
① 查询 id=6不存在的数据,加锁基本单位next-key lock,因此事务A的加锁范围是 id (5, 8];
② 唯一索引进行等值查询,由于查询的记录不存在,next-key lock 退化成间隙锁,因此最终加锁的范围是 (5,8)。
**➳ 解析:**由于退化为间隙锁,因此事务B插入该间隙锁(5,8) 范围区间,id=6、7的数据都会失败,而事务C修改上下区间边界值id = 5、8的是能够成功的,因为是前开后开原则。
▎唯一索引的范围查询
范围查询和等值查询的加锁规则是不同的,仔细查看下面案例。
1. 事务A进行范围查询,如下:
select * from demo where id>=5 and id<7 lock in share mode;
∞ 事务A加锁变化过程如下:
① 首先最开始要找的记录是 id>=5,因此产生的临键锁 next-key lock 范围为:(1,5],但是由于 id 是唯一索引,且该记录是存在的,因此会退化成记录锁,也就是只会对 id = 5 这一行加锁;
② 由于是范围查找,就会继续往后找存在的记录,也就是会找到 id = 8 这一行停下来,然后加 next-key lock (5, 8],但由于 id = 8 不满足 id < 7,所以会退化成间隙锁,加锁范围变为 (5, 8)。
2. 假设事务B的操作如下
根据事务A加锁变化得出,目前有2把锁,行锁:id=5 , 间隙锁:(5,8)。
» 测试行锁——5
update demo set name='梅花十三' where id=5; 注:由于是行锁,锁定了该行,因此是无法修改的
» 测试(5,8) 间隙锁区间范围——6、7
insert into demo(id, age, name) VALUES (6,18,'张三'); insert into demo(id, age, name) VALUES (7,18,'张三'); 注:6、7都在间隙锁范围内,所以无法插入
» 测试下区间边界值——8
update demo set name='梅花十三' where id=8; 注:id=8是间隙锁(5,8)范围内的下区间边界值,由于是后开原则,因此是可以修改的
总结
✈ 唯一索引等值查询
- 当查询的记录是存在的,next-key lock 会退化成「记录锁」。
- 当查询的记录是不存在的,next-key lock 会退化成「间隙锁」。
✈ 非唯一索引等值查询
- 当查询的记录存在时,除了会加 next-key lock 外,还额外加间隙锁,也就是会加两把锁。
- 当查询的记录不存在时,只会加 next-key lock,然后会退化为间隙锁,也就是只会加一把锁。
✈ 非唯一索引和主键索引的范围查询的加锁规则不同之处在于
- 唯一索引在满足一些条件的时候,next-key lock 退化为间隙锁和记录锁。
- 非唯一索引范围查询,next-key lock 不会退化为间隙锁和记录锁。