
琪小新
2022/11/25阅读:38主题:丘比特忙
MySQL在不同隔离级别下的加锁情况

阅读完本篇文章预计需要6分钟
我们知道,谈及关系型数据库的基础知识是绕不开MVCC
和锁
的,而谈到MySQL,一般我们说的都是InnoDB引擎的锁相关内容。比如我们熟知的表锁、record lock、next-key lock 以及意向锁等吧啦吧啦一堆。
今天主要围绕行锁相关,分析在日常的使用过程中RR
或RC
下的加锁情况。因为平时使用中主要以RR和RC为主,当然RC偏多一些。在这两种隔离级别下分别介绍主键、唯一索引、普通索引和无索引字段上操作的情况。
比如我们有一张 mis_pointer
表,结构如以下:
CREATE TABLE `mis_pointer` (
`id` int NOT NULL,
`p_name` varchar(64) DEFAULT NULL,
`p_addr` int DEFAULT NULL,
`p_type` mediumint DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `p_addr` (`p_addr`),
KEY `p_type` (`p_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
表数据如下,后面介绍过程中数据可能发生变化哈。

在下面各种情况下介绍加锁规则
主键索引加锁

RR隔离级别下
使用以下语句进行范围更新时,LOCK_MODE显示加X锁,其实是加了Next key Lock(即锁住主键索引和前面的间隙),范围后面的11也加了X和GAP锁
set transaction_isolation='repeatable-read';
begin;
select * from mis_pointer mp where mp.id < 10 for update
加锁信息如下:

RC隔离级别下
set transaction_isolation='read-committed';
begin;
select * from mis_pointer mp where mp.id < 11 for update
只加记录锁,不会加GAP锁

但是在唯一查找条件下(非范围),无论是RR还是RC,只加对应主键索引记录的记录锁

唯一索引加锁

在唯一索引下加锁和主键索引类似,但是由于二级索引的特殊性,会有一些差异
RR隔离级别下
范围更新时,对应唯一索引记录加Next Key Lock(即记录锁外加前面的GAP锁),唯一索引对应主键索引加记录锁。和之前RR一下一样,范围后的值也会加锁,防止幻读。
set transaction_isolation='repeatable-read';
begin;
select * from mis_pointer mp where mp.p_addr < 1012 for update
加锁信息:

RC隔离级别下
set transaction_isolation='read-committed';
begin;
select * from mis_pointer mp where mp.p_addr < 1012 for update
可以看到不会加GAP,只是在对应唯一索引上加记录锁,相对应的主键索引加记录锁

同样,在唯一等值查找条件下(非范围),无论是RR还是RC,只加对应索引记录加记录锁,相对应的主键加记录锁
begin;
select * from mis_pointer mp where mp.p_addr = 1012 for update

普通索引加锁

RR隔离级别下
begin;
select * from mis_pointer mp where mp.p_type < 3 for update
范围条件下,索引记录加Next Key Lock(同样条件范围下一个索引记录也要加,这里的【3,9】),对应主键记录加记录锁。

RC隔离级别下
begin;
select * from mis_pointer mp where mp.p_type < 3 for update
范围条件下,索引记录,以及对应主键索引加记录锁。

在等值查找条件下(非范围),RR隔离级别下,索引记录加Next Key Lock,对应主键加记录锁
begin;
select * from mis_pointer mp where mp.p_type = 3 for update

在等值查找条件下(非范围),RC隔离级别下,索引记录和对应主键加记录锁
begin;
select * from mis_pointer mp where mp.p_type = 3 for update

无索引字段

目前测试用例如下

RC隔离级别下
范围条件下,不会锁全表,会锁定记录对应的主键索引,加记录锁,不会锁定间隙
该字段值条件范围内对应主键和其他字段的操作也会被阻塞
阻塞以该字段为条件的更新删除操作的会话
由于没有GAP锁的存在,并不影响写入操作
set transaction_isolation='read-committed';
begin;
select * from mis_pointer mp where mp.p_name > 'b' for update

等值条件下和范围条件一样,不会锁全表,会锁定字段记录对应的主键索引,即加记录锁
set transaction_isolation='read-committed';
begin;
select * from mis_pointer mp where mp.p_name = 'a' for update
加锁具体信息如下

RR隔离级别下
比如范围条件下
set transaction_isolation='repeatable-read';
begin;
select * from mis_pointer mp where mp.p_name > 'b' for update
这下就悲催了,所有主键索引加Next Key Lock,并且最大虚记录也加了锁,即相当于全表加锁

当然等值条件下,也会锁定全表
set transaction_isolation='repeatable-read';
begin;
select * from mis_pointer mp where mp.p_name = 'b' for update

今天就分享到这里了

作者介绍

琪小新
DBA