MySQL8 行级锁

程序员Mark1024

已于 2024-03-26 14:24:47 修改

阅读量931

点赞数 8

分类专栏： MySQL 文章标签： adb

于 2024-03-26 14:21:23 首次发布

本文链接：https://blog.csdn.net/programtalk1024/article/details/137045104

版权

MySQL 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

MySQL8 INNODB行级锁

MySQL8 行级锁

版本：8.0.34

基本概念

行级锁（Row-Level Locking）是MySQL InnoDB引擎特有的特性，行级锁的粒度小，并发性能高，发生死锁的概率高。

从锁的兼容性角度来看，行级锁主要包含共享锁（S锁）和排他锁（X锁）。

共享锁（S锁）：一个事务去加共享锁后，同时也允许其他事务读，但是排斥其他事务获取排他锁（X锁）
排他锁（X锁）：一个事务加排他锁后，其他事务不允许加X锁，也不允许加S锁。

当前所类型\其他请求锁类型	S锁	X锁
S锁	兼容	互斥
X锁	互斥	互斥

不同的语句的会加上不同的锁，以下是加锁类型说明：

SQL	记录锁类型
INSERT、UPDATE、DELETE、SELECT…FOR UPDATE	X锁
SELECT（通用）	不会加锁，快照读
SELECT…FOR SHARE	S锁

按照锁的粒度来划分，MySQL中行级锁主要有记录锁（Record Lock）、间隙锁（Gap Locks）、临键锁（Next-Key Locks）。

行级锁并非是将锁加到记录上，而是加到了索引上。

记录锁（Record Lock）：记录锁宏观上看确实是锁在了记录上，但实际上锁在索引上，当我们开启一个事务，使用SELECT...FOR UPDATE、INSERT、UPDATE、DELETE语句操作某些已经存在的记录上的时候，就会加上记录锁。
间隙锁（Gap Locks）：间隙锁是一种范围锁，锁定的是一个区间（左开右开），他的作用就是确保索引记录之间不能够插入值（Insert操作），避免产生幻读，在RR事务隔离级别下支持。间隙锁是为了避免幻读的发生。
临键锁（Next-Key Locks）行锁和间隙锁的组合，同时锁住临界记录和间隙（左开右闭），在RR事务隔离级别下支持。

InnoDB引擎在RR事务隔离级别下使用临键锁搜索和索引扫描，从而防止幻读，该给索引记录上什么样的锁，要根据具体情况而定，不过MySQL都是首先考虑临键锁，根据不同的情况退化为记录锁或者间隙锁。

主键索引抽象图

在上图中显示表t1中有4条记录，主键分别是3、5、6、9，MySQL会根据这四个值构建一个聚簇索引，图中虚线部分是不存在的，但是这些地方就是数据索引中的间隙，加行级锁的时候就是考虑这些间隙，从而形成一套加行级锁的规则。

上面的数据临键锁的区域划分如下，此图特别重要！！！ ：

临键锁区域

行级锁的锁信息会被放入到performance_schema.data_locks中，可以通过查询该表来了解详细加锁情况，该表的列含义在文章MySQL8表级锁 - 超哥编程说 (programtalk.cn)中已经说过，这里不再做赘述。

SELECT OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA FROM performance_schema.data_locks;

准备数据

mysql> CREATE TABLE `t1` (
    ->   `id` int NOT NULL AUTO_INCREMENT,
    ->   `name` varchar(10) NOT NULL,
    ->   `id_nbr` varchar(19) DEFAULT NULL,
    ->   `age` int NOT NULL,
    ->   PRIMARY KEY (`id`),
    ->   UNIQUE KEY `idx_uk_id_nbr` (`id_nbr`),
    ->   KEY `idx_name` (`name`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t1 values(3, '刘备', '110101193007282815', 93), (5, '孙权', '110101194007281016', 93), (6, '曹操', '110101191807288714', 95), (9, '王朗', '110101190007287516', 123);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from t1;
+----+--------+--------------------+-----+
| id | name   | id_nbr             | age |
+----+--------+--------------------+-----+
|  3 | 刘备   | 110101193007282815 |  93 |
|  5 | 孙权   | 110101194007281016 |  93 |
|  6 | 曹操   | 110101191807288714 |  95 |
|  9 | 王朗   | 110101190007287516 | 123 |
+----+--------+--------------------+-----+
4 rows in set (0.00 sec)

我准备了一个表t1，id是主键，姓名name（二级非唯一索引），身份证号（唯一索引），年龄age无索引。

开始验证

在开始验证之前，我们再次强调下：InnoDB引擎在RR事务隔离级别下使用临键锁搜索和索引扫描，从而防止幻读，索引当分析一个SQL加什么行级锁的时候要使用考虑以下几件事：

临键锁区间情况
通过条件确定索引数据范围，判断命中了哪些临键锁区间。
判断是否可能退化为记录锁或者间隙锁。

聚簇索引

InnoDB引擎以临键锁搜索和索引扫描，完全匹配临键锁区间的时候，就使用临键锁，否则考虑是否退化成为记录锁或者间隙锁。多个临键区间单独考虑加锁情况。

开始验证：

select * from t1 where id <= 3 for update;

select * from t1 where id <= 3 for update;

此时命中的临键区间的关系如下图：

命中情况

此时正好命中临键锁 (-∞, 3] ，数据范围也正好与临键锁 (-∞, 3] 完全匹配，最终就会加上临键锁。

查看下锁：

临键锁

第二行中，LOCK_DATA=X， LOCK_DATA=3说明此锁是一个临键锁，临键值（最近最大上限）= 3。

临键锁范围内是不允许插入数据的，临键值是不允许修改（删除）的，比如不允许插入id=2的记录，也不允许修改（删除）id=3的记录

不允许插入id=2的记录

不允许修改（删除）id=3的记录

如果查询条件是是id < 3那么临键锁就会退化为间隙锁。

select * from t1 where id < 3;

id < 3所在的临键锁区是 (-∞, 3]

命中情况

当时条件中不包含上界值3，所以退化为间隙锁。

锁信息如下图：

退化为间隙锁

如果查询条件变为id = 3则临键锁要退化为记录锁

select * from t1 where id = 3 for update;

查id=3的记录查看下临键区间命中情况，id = 3会命中临键区是 (-∞, 3]

命中情况

但是查询条件中只有3，那么就无需负无穷区，所以此时临键锁就退化为记录锁，最终只锁定3的索引记录。

查看下锁信息：

记录锁

图中第一行是一个意向共享锁，本篇幅不讲解，第二行是行锁的记录，其INDEX_NAME=PRIMARY代表是对主键索引进行加锁，LOCK_TYPE=RECORD代表此锁是行级锁，LOCK_MODE=S,REC_NOT_GAP代表此锁是一个行锁、共享锁，不是一个间隙锁，LOCK_DATA=3代表此锁锁的数据是索引key=3，印证了分析和结果是一致的。

引擎对索引key=3加了S锁、记录锁，所以其他会话只能获取S锁，不能无法获取X锁。 下图为兼容性测试：

其他会话只能获取S锁，不能无法获取X锁

如果查询一个不存在的记录，那么引擎会找比当前条件主键值大且最近的索引记录，比如查询条件是id = 7。

select * from t1 where id = 7 for update;

查id=7的记录，此记录不存在

上图中查询id=7的记录，但是此记录不存在，索引继续向后搜索临键，最终得到9这个临键，最终引擎确定7所在的临键区是 （6， 9]

命中情况

但是查询条件里没有9，因此退化为间隙锁，锁得范围就确定为(6,9) ，也就是对7和8的索引记录加锁，主键为7和8是不允许插入记录的。

查看锁记录验证下：

锁信息

图中第一行是一个意向共享锁，本篇幅不讲解，第二行是行锁的记录，其INDEX_NAME=PRIMARY代表是对主键索引进行加锁，LOCK_TYPE=RECORD代表此锁是行级锁，LOCK_MODE=X,GAP代表此锁是一个行锁、排他锁，GAP代表是一个间隙锁，LOCK_DATA=9代表此锁的上边界索引Key是9（实际锁的范围中不包含9）。

此间隙锁的范围是(6,9)，故而id=7和id=8的记录无法被Insert，再开启一个会话来验证下id=7和id=8这两个记录能否被插入到表中。

id=7无法插入间隙

查看下锁信息：

查看锁状态

LOCK STATUS=WAITING说明确实无法插入id=7的记录。

那么id=8的记录是不是也无法插入呢？

id=8的记录无法插入到间隙

没错确实能够正常插入。

等值查询、记录不存在，并且上边界索引Key值不存在的时候呢，会加什么锁呢？比如我操作id=10的记录。

id=10

查询条件id=10所在的临键区间，命中情况如下：

命中情况

没有边界问题，所有无需退化，使用临键锁（这个临键锁比较特殊，它的上界值是supremum pseudo-record，并不存在于索引树中）

supremum pseudo-record

那么索引key大于9的记录都不允许被Insert，比如下图中id = 10的记录是无法插入的。

id=10不允许插入

在测试给id = 100的记录，也是无法被插入，看下图：

id=100不允许插入

那么id=9这个B+树中最大的主键值能够修改吗？答案是能，左开区间嘛。

再看一个跨区间的情况，比如id <= 4。

select * from t1 where id <= 4;

id < 4

id < 4命中的临键区如下图：

命中情况

此时命中了两个临键区 (-∞, 3] 和 (3, 5] ，第一个临键区不会退化，所以会加上一个上界=3的临键锁，对于第二个临键区，查询条件中不包含5，所以退化为间隙锁 (3, 5) .

查看加锁情况：

加锁情况 id=4的记录无法Insert的，看下图：

有一个比较特殊的情况存在，有人说这是一个BUG，当条件是id > 6 and id <= 9的时候

select * from t1 where id > 6 and id <= 9 for update;

id > 6 and id <= 9

临键区命中情况应该如下（实际上下图是错的）：

命中情况

此时正好命中键区 (6, 9] ，应该加上一个临键锁即可（实际上并不是这样！！！ ）

查看锁信息：

锁信息

可以看到了还有一个LOCK_DATA=supremum pseudo-record的临键锁，supremum pseudo-record的意思是伪记录。

很奇怪，对不对？这是因为：唯一索引上的范围查询，如果记录中的最大值在查询范围内，会访问到不满足条件的第一个值（这个值其实就是supremum pseudo-record）为止。），插条条件是id <=9 ,这个9就是最大索引记录值，并且在查询条件值，所以当扫描到9之后，还会继续向后扫描。先后扫描就进入了(9, +∞)这个临键区了，并且还不会退化。

如此，命中临键区间就变为了下图：

命中情况

LOCK_DATA=supremum pseudo-record并非只有上述情况才会出现，当id > 9这个B+树中最大索引键值的时候也是会出现的

select * from t1 where id > 9

select * from t1 where id > 9

二级唯一索引

二级唯一索引聚簇索引都是使用临键锁区搜索和索引数据。使用id_nbr字段作为查询条件id_nbr字段创建索引的时候默认使用的升序，并且排序字符集是utf8mb4_0900_ai_ci。通过id_nbr字段来升序排列后，顺序如下：

id_nbr升序

根据上图可以知道一个B+树叶子节点图大致如下（为了便于理解，我增加了很多空隙）。

临键锁区域

为什么会有这么多空隙呢？这是有因为数据类型是字符串的，那么字符串与字符串之间肯定能够再放入其他字符串，比如110101190007287516和110101190007287517之间，就可以放入类似110101190007287516XXXX任意多X的数据（不超过字段长度）。

因此也就能获取到如上图中所示的临界区。

非聚簇唯一索引范围查询：InnoDB存储引擎使用临键锁搜索数据，会搜索到下一个不满足条件的索引KEY，如果进入到下一个临键区，则会将下一个临键区加上临键锁（任何时候都不会退化，这跟主键索引是不同的）

开始验证：

select * from t1 where id_nbr <= '110101190007287516' for update;

select * from t1 where id_nbr <= '110101190007287516' for update;

命中临键区如下：

命中临键区

第一个临键区被命中没有问题，数据也确实在这个范围内，但是第二个临键区为什么也命中了呢？

这是因为非聚簇唯一索引，InnoDB存储引擎使用临键锁搜索数据的时候，会搜索到下一个不满足条件的索引KEY，下一个Key肯定是大于110101190007287516的，那么就进入了后面的临键区(110101190007287516, 110101191807288714]中，对该临键区加临键锁

所以就命中了两个临键区。查看锁情况：

锁情况

可以看到有四个锁，第一个锁是意向排他锁，略过。第二行和第三行是两个临键锁，不同于聚簇索引，非聚簇索引的LOCK_DATA会记录索引的值，以及该记录对应的主键值。

特别要注意的是，第四行还加了一个聚簇索引树中的记录锁，LOCK_DATA=9。因为条件中有等值条件且查询到记录。

已经对非聚簇唯一索引加了临键锁，为什么还要对聚簇索引加记录锁呢？首先我们应该知道非聚簇索引树与聚簇索引树并不是一棵树，如果有其他事务执行delete from t1 where id_nbr = '110101190007287516'或者是delete from t1 where name = '王朗'，那么首先要在非聚簇索引上找到记录的主键id，然后表，更新id=9的记录，如果不对主键索引加锁，并发操作就能通过id_nbr之外的条件修改id_nbr = '110101190007287516'的记录。

如果去掉等号，那么临键锁搜到到110101190007287516就会停止，虽然条件中不包含110101190007287516，但是临键锁不会退化。

select * from t1 where id_nbr < '110101190007287516' for update;

select * from t1 where id_nbr < '110101190007287516' for update;

引擎会使用临键锁搜索索引数据，搜索到id_nbr = '110101190007287516'的时候停止，不会继续想后搜索，所以只会命中一个临建区，查询条件中的110101190007287516正好是该区域的上界值，所以不会退化为间隙锁。

命中情况

查看锁情况：

锁情况

验证了确实不会退化。

如果条件只是等于一个已经存在的记录110101190007287516，则会退化为记录锁。

select * from t1 where id_nbr = '110101190007287516' for update;

select * from t1 where id_nbr = '110101190007287516' for update;

查询条件命中临键区情况如下：

命中情况

等值查询，不会继续向下搜索第一个不满足条件的索引，并且不锁住(-∞, 110101190007287516)左开右开区间也不会导致幻读问题，所以临建锁退化为记录锁。

锁情况如下：

记录锁

有两个记录锁，一个是非聚簇唯一索引的记录锁，另外一个是该记录对应的聚簇索引中的记录锁。

如果等值查询，记录不存在的时候呢？比如查询id_nbr = '110101190007287515'，则会退化为间隙锁。

select * from t1 where id_nbr = '110101190007287515' for update;

select * from t1 where id_nbr = '110101190007287515' for update;

命中临键区情况如下：

命中情况

110101190007287515不存在，临建区(-∞, 110101190007287516]中的110101190007287516不在查询条件中，所以退化为间隙锁。

查看锁情况：

退化为间隙锁

确实是间隙锁。

id_nbr > '110101194007281016’的时候（110101194007281016是索引树中最大的索引KEY）

select * from t1 where id_nbr > '110101194007281016' for update;

那么命中临键区情况如下：

命中情况

此时正好命中一个临建锁区间，加临建锁。

查看锁情况：

加临建锁

如果在上面的查询条件id_nbr > '110101194007281016'加上等号，变为id_nbr >= '110101194007281016'会加什么样的锁呢？

select * from t1 where id_nbr >= '110101194007281016' for update;

select * from t1 where id_nbr >= '110101194007281016' for update;

此时临键区命中情况为

命中情况

命中了两个临键区，所以会加上两个临建锁，因为会查出来id=5的记录，防止修改，会加上主键索引树中索引key=5的记录锁。

查看锁情况：

锁情况

没错，确实如此。

二级普通索引

首先来看下表数据：

表中name是普通索引，中文排序不太容易观察，为了演示方便，将name字段改为拼音。

mysql> update t1 set name = 'liubei' where id = 3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update t1 set name = 'sunquan' where id = 5;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update t1 set name = 'caocao' where id = 6;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update t1 set name = 'wanglang' where id = 9;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

修改后数据如下：

表数据

这个表中name字段的普通索引树以及临建锁区域如下：

临键锁区域

开始验证：

select * from t1 where name <= 'caocao' for update;

select * from t1 where name <= 'caocao' for update;

首先会命中临键区 (-∞, caocao] ，依然会继续向下搜索（找到第一个不等于caocao的索引），会命中第二个临键区 (caocao, liubei] ，所以会加两个二级索引的临键锁，并且caocao对应的记录是存在的，也需要给这个记录加一个聚簇索引中的记录锁。

命中情况

锁情况如下：

如果查询条件不包含等号，也就是变为name < 'caocao'呢？

select * from t1 where name < 'caocao' for update;

select * from t1 where name < 'caocao' for update;

命中临键区情况如下图：

命中情况

引擎扫描到caocao就停止了，不会进入下一个临键区，所有只需要加一个临键锁即可。

查看锁情况：

确实如此。

如果是等值查询caocao呢？

select * from t1 where name = 'caocao' for update;

select * from t1 where name = 'caocao' for update;

正常来说他会命中临键区 (-∞, caocao] ，然后退化为记录锁：

命中情况

但是实际情况并非如此，假设我们只锁定上图中的绿色部分，这能解决幻读问题吗？不能！！！，为什么呢？因为name的索引是普通索引，在索引树中name的值是允许重复的，那么我在上图绿色部分左右间隙插入name=caocao的数据是一定能够插入的，这就出现了幻读，解决办法就是将两侧的间隙锁住，此时命中临键区的情况就变为了下图如下：

命中情况

那么第一个临建锁不退化，第二个退化为间隙锁。

锁情况如下：

锁情况

如果等值查询，但是记录不存在呢？

select * from t1 where name = 'caocaa' for update;

select * from t1 where name = 'caocaa' for update;

命中临键区 (-∞, caocao] 。

命中情况

但是查询条件中并未出现caocao索引KEY，所以退化为间隙锁。

锁情况如下：

退化为间隙锁

总结

MySQL InnoDB中的行级锁，优先使用临键锁，根据情况退化为间隙锁和记录锁。
索引上的等值查询，如果记录不存在，则优化为间隙锁，但是当记录索引KEY值大于B+树中最大索引KEY的时候，依然保持临建锁，临键值=supremum pseudo-record。
对于聚集索引，范围查询，如果查询条件中包含临键值（临键区最大索引值）的时候，保持临键锁，否则退化为间隙锁。
普通索引等值查询时，如果索引记录存在，会在二级索引上加该索引前间隙加临键锁和后间隙退化为间隙锁，在聚簇索引上对该索引记录加记录锁。
二级索引，会访问到第一个不满足条件的值为止。

程序员Mark1024

关注

8
点赞
踩
28

收藏

觉得还不错? 一键收藏
0
评论
MySQL8 行级锁

MySQL InnoDB中的行级锁，优先使用临键锁，根据情况退化为间隙锁和记录锁。索引上的等值查询，如果记录不存在，则优化为间隙锁，但是当记录索引KEY值大于B+树中最大索引KEY的时候，依然保持临建锁，临键值=。对于聚集索引，范围查询，如果查询条件中包含临键值（临键区最大索引值）的时候，保持临键锁，否则退化为间隙锁。普通索引等值查询时，如果索引记录存在，会在二级索引上加该索引前间隙加临键锁和后间隙退化为间隙锁，在聚簇索引上对该索引记录加记录锁。二级索引，会访问到第一个不满足条件的值为止。
复制链接

扫一扫