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隔离级别下
  • RC隔离级别下
• 唯一索引加锁
  • RR隔离级别下
  • RC隔离级别下
• 普通索引加锁
  • RR隔离级别下
  • RC隔离级别下
• 无索引字段
  • RC隔离级别下
  • RR隔离级别下

主键索引加锁

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

今天就分享到这里了