琪小新

V1

2022/11/25阅读:38主题:丘比特忙

MySQL在不同隔离级别下的加锁情况

阅读完本篇文章预计需要6分钟

我们知道,谈及关系型数据库的基础知识是绕不开MVCC的,而谈到MySQL,一般我们说的都是InnoDB引擎的锁相关内容。比如我们熟知的表锁、record lock、next-key lock 以及意向锁等吧啦吧啦一堆。

今天主要围绕行锁相关,分析在日常的使用过程中RRRC下的加锁情况。因为平时使用中主要以RR和RC为主,当然RC偏多一些。在这两种隔离级别下分别介绍主键、唯一索引、普通索引和无索引字段上操作的情况。

比如我们有一张 mis_pointer 表,结构如以下:

CREATE TABLE `mis_pointer` (
  `id` int NOT NULL,
  `p_name` varchar(64DEFAULT 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

今天就分享到这里了

分类:

后端

标签:

数据库

作者介绍

琪小新
V1

DBA