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;
此时命中的临键区间的关系如下图：
此时正好命中临键锁 (-∞, 3] ，数据范围也正好与临键锁 (-∞, 3] 完全匹配，最终就会加上临键锁。
查看下锁：
第二行中，LOCK_DATA=X， LOCK_DATA=3说明此锁是一个临键锁，临键值（最近最大上限）= 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会命中临键区是 (-∞, 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锁。 下图为兼容性测试：
如果查询一个不存在的记录，那么引擎会找比当前条件主键值大且最近的索引记录，比如查询条件是id = 7。
select * from t1 where id = 7 for update;
上图中查询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这两个记录能否被插入到表中。
查看下锁信息：
LOCK STATUS=WAITING说明确实无法插入id=7的记录。
那么id=8的记录是不是也无法插入呢？
没错确实能够正常插入。
等值查询、记录不存在，并且上边界索引Key值不存在的时候呢，会加什么锁呢？比如我操作id=10的记录。
查询条件id=10所在的临键区间，命中情况如下：
没有边界问题，所有无需退化，使用临键锁（这个临键锁比较特殊，它的上界值是supremum pseudo-record，并不存在于索引树中）
那么索引key大于9的记录都不允许被Insert，比如下图中id = 10的记录是无法插入的。
在测试给id = 100的记录，也是无法被插入，看下图：
那么id=9这个B+树中最大的主键值能够修改吗？答案是能，左开区间嘛。
再看一个跨区间的情况，比如id <= 4。
select * from t1 where 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;
临键区命中情况应该如下（实际上下图是错的）：
此时正好命中键区 (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
二级唯一索引
二级唯一索引聚簇索引都是使用临键锁区搜索和索引数据。使用id_nbr字段作为查询条件id_nbr字段创建索引的时候默认使用的升序，并且排序字符集是utf8mb4_0900_ai_ci。通过id_nbr字段来升序排列后，顺序如下：
根据上图可以知道一个B+树叶子节点图大致如下（为了便于理解，我增加了很多空隙）。
为什么会有这么多空隙呢？这是有因为数据类型是字符串的，那么字符串与字符串之间肯定能够再放入其他字符串，比如110101190007287516和110101190007287517之间，就可以放入类似110101190007287516XXXX任意多X的数据（不超过字段长度）。
因此也就能获取到如上图中所示的临界区。
非聚簇唯一索引范围查询：InnoDB存储引擎使用临键锁搜索数据，会搜索到下一个不满足条件的索引KEY，如果进入到下一个临键区，则会将下一个临键区加上临键锁（任何时候都不会退化，这跟主键索引是不同的）
开始验证：
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;
引擎会使用临键锁搜索索引数据，搜索到id_nbr = '110101190007287516'的时候停止，不会继续想后搜索，所以只会命中一个临建区，查询条件中的110101190007287516正好是该区域的上界值，所以不会退化为间隙锁。
查看锁情况：
验证了确实不会退化。
如果条件只是等于一个已经存在的记录110101190007287516，则会退化为记录锁。
select * from t1 where id_nbr = '110101190007287516' for update;
查询条件命中临键区情况如下：
等值查询，不会继续向下搜索第一个不满足条件的索引，并且不锁住(-∞, 110101190007287516)左开右开区间也不会导致幻读问题，所以临建锁退化为记录锁。
锁情况如下：
有两个记录锁，一个是非聚簇唯一索引的记录锁，另外一个是该记录对应的聚簇索引中的记录锁。
如果等值查询，记录不存在的时候呢？比如查询id_nbr = '110101190007287515'，则会退化为间隙锁。
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;
此时临键区命中情况为
命中了两个临键区，所以会加上两个临建锁，因为会查出来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;
首先会命中临键区 (-∞, caocao] ，依然会继续向下搜索（找到第一个不等于caocao的索引），会命中第二个临键区 (caocao, liubei] ，所以会加两个二级索引的临键锁，并且caocao对应的记录是存在的，也需要给这个记录加一个聚簇索引中的记录锁。
锁情况如下：
如果查询条件不包含等号，也就是变为name < 'caocao'呢？
select * from t1 where name < 'caocao' for update;
命中临键区情况如下图：
引擎扫描到caocao就停止了，不会进入下一个临键区，所有只需要加一个临键锁即可。
查看锁情况：
确实如此。
如果是等值查询caocao呢？
select * from t1 where name = 'caocao' for update;
正常来说他会命中临键区 (-∞, caocao] ，然后退化为记录锁：
但是实际情况并非如此，假设我们只锁定上图中的绿色部分，这能解决幻读问题吗？不能！！！，为什么呢？因为name的索引是普通索引，在索引树中name的值是允许重复的，那么我在上图绿色部分左右间隙插入name=caocao的数据是一定能够插入的，这就出现了幻读，解决办法就是将两侧的间隙锁住，此时命中临键区的情况就变为了下图如下：
那么第一个临建锁不退化，第二个退化为间隙锁。
锁情况如下：
如果等值查询，但是记录不存在呢？
select * from t1 where name = 'caocaa' for update;
命中临键区 (-∞, caocao] 。
但是查询条件中并未出现caocao索引KEY，所以退化为间隙锁。
锁情况如下：
总结
- MySQL InnoDB中的行级锁，优先使用临键锁，根据情况退化为间隙锁和记录锁。
- 索引上的等值查询，如果记录不存在，则优化为间隙锁，但是当记录索引KEY值大于B+树中最大索引KEY的时候，依然保持临建锁，临键值=
supremum pseudo-record。 - 对于聚集索引，范围查询，如果查询条件中包含临键值（临键区最大索引值）的时候，保持临键锁，否则退化为间隙锁。
- 普通索引等值查询时，如果索引记录存在，会在二级索引上加该索引前间隙加临键锁和后间隙退化为间隙锁，在聚簇索引上对该索引记录加记录锁。
- 二级索引，会访问到第一个不满足条件的值为止。
165
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
