【MySQL】说透锁机制（一）行锁 加锁规则 之 等值查询

---

前言

如何控制并发是数据库领域中非常重要的问题之一MySQL为了解决并发带来的问题设计了事务隔离机制、锁机制、MVCC机制等等用一整套机制来解决并发问题接下来会分几篇来分析MySQL5.7版本InnoDB引擎的锁机制。

由于锁机制的内容很多一篇写完字数太多所以我决定分几篇来逐步更新。行锁更重要优先从行锁说起然后再说表锁。
对于行锁行锁的S/X模式和3种算法是最基础的然后再深入分析行锁的加锁规则等等几篇本文主要深入分析行锁的加锁规则中的等值查询。

---

一、共享锁S和排它锁X

行级锁从锁的模式(lock_mode)可以分为共享锁和排它锁

• 共享锁简称S锁(Shared)也称为读锁读读兼容当前事务获取S锁后其它事务也可以获得S锁但会阻塞其它事务获得X锁
• 排它锁简称X锁(eXclusive)也称为写锁读写/写写均不兼容当前事务获取X锁后会阻塞其它事务获取S锁和X锁。

SQL语句对应上的行锁说明如下

操作	锁的模式	说明
普通select语句	无行锁	在上文MVCC机制讲过普通的 select 语句属于快照读
select…lock in share mode	S	显示(explicit)读锁 上锁后其它事务对锁定的索引记录仍可以上S锁但阻塞其它事务对锁定的索引记录上X锁
select…for update	X	显式(explicit)写锁上锁后阻塞其它事务对锁定的索引记录上S或X锁
insert/update/delete	X	隐式(implicit)写锁上锁后阻塞其它事务对锁定的索引记录上S或X锁

---

二、行锁的3种算法

InnoDB引擎有3种行锁的算法都是锁定的索引

Record Lock

• Record Lock 记录锁锁定的是单个索引记录;
  如果没有设置任何一个索引那么上文也提到过有个隐式主键就会通过隐式主键来锁定。
  

Gap Lock

• Gap Lock间隙锁是指索引记录之间的间隙上的锁或者是在第一条之前或最后一条索引记录之后的间隙上的锁。
  锁定的是索引记录 之前 的间隙白话说就是每个索引值管着前面的间隙;

举个例子当索引的值有10,20,30,40时那么索引就存在如下间隙圆括号表示不包括区间点

	(下界限, 10)
	(10, 20)
	(20, 30)
	(30, 40)
	(40, 上界限supremun)

因为是锁定索引之前的间隙所以就存在如下间隙锁

间隙范围	索引记录
(下界限, 10)	10
(10, 20)	20
(20, 30)	30
(30, 40)	40
(40, 上界限supremun)	supremun

特殊说明由于间隙锁是为了解决幻读问题所以在读已提交RC事务隔离级别是显示禁用间隙锁的。

Next-key Lock

• Next-key LockRecord Lock + Gap Lock 的组合既锁 索引记录 又锁 间隙很多地方都称它是临键锁或邻键锁但我觉得直接翻译成下一个键锁会更好理解意思是锁由“下一个键负责”原则左开右闭 或称 前开后闭 。
  上面的例子的区间为圆括号表示不包括区间点方括号表示包括区间点

	(下界限, 10]
	(10, 20]
	(20, 30]
	(30, 40]
	(40, 上界限supremun)

当给索引值20加上了Next-key Lock那么这个范围是 (10,20] 包括20 而不包括10。

由于上界限supremun实际是个伪值所以上界限并不是真正的索引记录。因此实际上这个Next-key Lock只锁定最大索引值之后的间隙。

---

三、加锁规则 之 等值查询

明白了3种算法那么这3种算法又是怎么落地的呢
实际上默认使用的是Next-key Lock也就是 索引记录 和 间隙 全锁上。但也会在不同场景下降级优化为Gap Lock或Record Lock。那我们就来分析一下
由于在读已提交RC事务隔离级别下间隙锁是禁用的(官方说是仅用于外键约束检查和重复键检查这不是重点所以本文主要深入分析在默认的可重复读RR事务隔离级别下的加锁规则 之 等值查询。

等值查询也就是where条件 = 因为行锁都是对索引上锁所以我们主要分析InnoDB引擎常见的3类索引

• 聚集索引主键简称pk
• 唯一索引简称uk
• 普通索引简称idx

分析数据准备

准备一个ctcountry team 国家队表id 是自增主键abc是普通索引abc_uk是唯一索引
并插入4条初始数据

CREATE TABLE `ct` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `abc` int(10) unsigned NOT NULL,
  `abc_uk` int(10) unsigned NOT NULL,
  `remark` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_abc_uk` (`abc_uk`) USING BTREE,
  KEY `idx_abc` (`abc`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;
INSERT INTO `ct`
(`id`, `name`, `abc`, `abc_uk`, `remark`) 
VALUES 
(10, '巴西', 10, 10, NULL),
(20, '阿根廷', 20, 20, NULL),
(30, '葡萄牙', 30, 30, NULL),
(40, '法国', 40, 40, NULL);

预览下数据

mysql> select * from ct;
+----+--------+-----+--------+--------+
| id | name   | abc | abc_uk | remark |
+----+--------+-----+--------+--------+
| 10 | 巴西   |  10 |     10 | NULL   |
| 20 | 阿根廷 |  20 |     20 | NULL   |
| 30 | 葡萄牙 |  30 |     30 | NULL   |
| 40 | 法国   |  40 |     40 | NULL   |
+----+--------+-----+--------+--------+
4 rows in set (0.00 sec)

我们新建Session1做以下基本设置

• 先确认是可重复读RR事务隔离级别

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+

• 如果不是需要在各Session中设置一下

set tx_isolation='repeatable-read';

• Session1中开启锁的监视器

SET GLOBAL innodb_status_output=ON;
SET GLOBAL innodb_status_output_locks=ON;

• 查询是否开启

mysql> show variables like '%innodb_status_output%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| innodb_status_output       | OFF   |
| innodb_status_output_locks | OFF   |
+----------------------------+-------+

我操作的步骤如下图

这个Session1就留着我们分析锁来用具体执行SQL我们新开另一个Session2好了准备开始~

3.1 聚集索引

我们先从聚集索引开始说起那么这里也分等值条件有匹配和无匹配索引两种情况对应上的锁也是不同的让我们来分别瞧一瞧

有匹配索引

在Session2执行SQL如下(按id=10)

begin;
update ct set remark = '怀念2002年, 巴西夺冠, 中国进世界杯' 
where id = 10;

注意不要commit或rollback以便于我们分析行锁

然后我们在"Session1"查看锁的详细信息

show engine innodb status\G; 

我们主要看TRANSACTIONS这段如下图

我们来分析一下上图中包含的信息

1. 1 row lock(s)就代表上了1个行锁不要理解成只锁了1行🐼
2. 具体的行锁信息从RECORD LOCKS开始
   每个RECORD LOCKS都会标明上锁的索引就是index后面的当前是PRIMARY即代表上锁的索引是聚集索引
   可能有多条RECORD LOCKS(当前只有一条)
3. RECORD LOCKS下面紧跟着是它所有的Record lock记录
   每条Record lock下面是具体的索引物理记录第0个就是索引记录的key当前hex 0000000a是指十六制的10所以可以得知这个行锁 锁的是id=10的聚集索引记录
   我们以第0个来识别是哪个索引key就可以了下面的1~6是索引记录上携带的数据聚集索引保存了所有字段信息所以比较多其它索引只有2行索引值和聚集索引的值
   另外Record Lock也可能有多条这里只上了1个行锁所以只有一条Record lock, heap no。。。

小结

等值查询 匹配到 聚集索引 时行级锁 会上一把 无间隙的Record Lock。
这里是因为聚集索引id具有唯一性所以Next-key Lock降级优化为Record Lock。

无匹配索引

先在Session2 rollback上一个SQL再执行SQL如下按id=11 不存在

begin
update ct set remark = '没有id=11的记录~~' 
where id = 11;

注意不要commit或rollback以便于我们分析行锁

然后我们在"Session1"查看锁的详细信息

show engine innodb status\G; 

我们主要看TRANSACTIONS这段如下图

小结

等值查询 未匹配到 聚集索引 时行级锁 会上一把 间隙锁

为什么是对 id=20 加的锁而不是对 id=11 加的锁呢

我们来分析一下

1. 行锁都是对索引记录加锁除了伪值上界限supremun因为id=11的索引不存在所以无法对id=11加锁。
2. 索引都是排好序的按顺序从左向右扫描直到找到 id=20 时才可以确定 id=11 不存在也就是说id=20 是 id =11 的next key所以是对id=20的索引加锁这里不是Next-key Lock而是间隙锁我觉得也是合理的毕竟只锁间隙就可以了范围是(10,20)不包括20。

按这么说可能有同学又有疑问如果id大于最大索引值锁哪个索引记录
咱们直接看结果锁的伪值上界限supremum范围是(40, supremum)不包括40.

update ct set remark = '比最大id还要大' 
where id = 41;

3.2 唯一索引

有匹配索引

先在Session2 rollback上一个SQL再执行SQL如下(按abc_uk=10)

begin;
update ct set remark = '怀念2002年, 巴西夺冠, 中国进世界杯' 
where abc_uk = 10;

注意不要commit或rollback以便于我们分析行锁

然后我们在"Session1"里查看锁的详细信息

show engine innodb status\G; 

我们主要看TRANSACTIONS这段如下图

和聚集索引非常类似不做赘述但这里是上了2个行锁所以有两条Record lock, heapno。。。

小结

等值查询 匹配到 唯一索引 时行级锁上了2把锁

1. 锁了一条唯一索引记录abc_uk=10
2. 锁了一条聚集索引记录id=10

因为唯一索引具有唯一性所以都是无间隙的Record Lock这里也是Next-key Lock降级优化为Record Lock。

无匹配索引

先在Session2 rollback上一个SQL再执行SQL如下(按abc_uk=35)

begin
update ct set remark = '没有abc_uk=35的记录~~' 
where abc_uk = 35;

注意不要commit或rollback以便于我们分析行锁

然后我们在"Session1"查看锁的详细信息

show engine innodb status\G; 

我们主要看TRANSACTIONS这段如下图

小结

等值查询 未匹配到 唯一索引 时行级锁 会上一把 间隙锁与聚集索引规则相同具体不做赘述。

3.3 普通索引

有匹配索引

先在Session2 rollback上一个SQL再执行SQL如下(按abc=10)

begin;
update ct set remark = '怀念2002年, 巴西夺冠, 中国进世界杯' 
where abc = 10;

注意不要commit或rollback以便于我们分析行锁

然后我们在Session1里查看锁的详细信息

show engine innodb status\G; 

我们主要看TRANSACTIONS这段如下图

我们来分析一下
这里就有意思了上了3个行锁还是3种不同的行锁3种算法都齐了咱们统一说一下怎么区分

1. RECORD LOCKS后面带locks rec but not gap这说明是无间隙的Record Lock
2. RECORD LOCKS后面带locks gap before rec这说明是间隙锁Gap Lock
3. RECORD LOCKS后面不带1和2的就说明是默认的Next-key Lock

小结

等值查询 匹配到 普通索引 时行级锁上了3把锁

1. abc=10的普通索引记录上了Next-key Lock这里的范围是(下界值, 10]
2. id=10的聚集索引记录上了Record Lock单条
3. abc=20的普通索引记录上了Gap-key Lock这里的范围是(10, 20)

可以这样说一个普通索引的等值查询update时相当于把这条索引记录前后的空隙都锁上了~

这和聚集索引、唯一索引有着很大的不同你知道这是为什么吗
思考一下
我们新开一个Session3先来验证一下吧

特殊说明
正常的锁超时异常是ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
^C – query aborted 这是我不想等锁超时按Ctrl+C中止了🐼

验证第1把Next-key Lock

• 插入abc=1和9的索引记录会阻塞直至超时异常

INSERT INTO `ct`(`id`, `name`, `abc`, `abc_uk`, `remark`) 
 VALUES (11, '英国', 1, 21, NULL);

INSERT INTO `ct`(`id`, `name`, `abc`, `abc_uk`, `remark`) 
 VALUES (21, '英国', 9, 11, NULL);

• 更新abc=10的索引记录会阻塞直至超时异常

update ct set remark = '怀念2002年, 巴西夺冠, 中国进世界杯' 
where abc = 10;

验证第2把Record Lock

• 更新id=10的索引记录会阻塞直至超时异常

update ct set remark = '怀念2002年, 巴西夺冠, 中国进世界杯' 
where id = 10;

验证第3把Gap Lock

• 插入abc=11和19的记录会阻塞直至超时异常

INSERT INTO `ct`(`id`, `name`, `abc`, `abc_uk`, `remark`)
 VALUES (1, '英国', 11, 21, NULL);
 
INSERT INTO `ct`(`id`, `name`, `abc`, `abc_uk`, `remark`)
 VALUES (21, '英国', 19, 1, NULL);

验证修改abc=20的索引记录不会阻塞

update ct set remark = '梅西将迎卡塔尔世界杯首秀这是我最后一届世界杯' 
where abc = 20;

我们来分析为什么 按abc=10 更新时 却上了3把锁

1. 匹配上的索引记录需要上锁所以 abc=10的索引上了锁这里没有降级就是使用默认的Next-key Lock;
2. 给匹配索引记录的聚集索引上锁这个与唯一索引是相同的规则对应id=10的聚集索引记录上了Record Lock;
3. 大家可能好奇为什么还锁abc=20的索引记录是这样的
   普通索引不具有唯一性当在索引树从左向右扫描时即使匹配到了记录也依然不能停止因为可能有多条匹配的记录所以依然需要继续向右扫描直到 abc = 20出现为止这样abc = 20 作为 next key也需要上锁这里上间隙锁也是可以理解的毕竟只锁间隙就可以了。

通过这样分析你是不是发现了上面说的不够严谨

没错我们再加一条记录让abc = 10的记录不止一条

INSERT INTO `ct`
(`id`, `name`, `abc`, `abc_uk`, `remark`) 
VALUES 
(15, '克罗地亚', 10, 15, NULL);

我们再确认一下现在的记录一直没有提交

mysql> select * from ct;
+----+----------+-----+--------+--------+
| id | name     | abc | abc_uk | remark |
+----+--------- +-----+--------+--------+
| 10 | 巴西     |  10 |     10 | NULL   |
| 15 | 克罗地亚 |  10 |     15 | NULL   |
| 20 | 阿根廷   |  20 |     20 | NULL   |
| 30 | 葡萄牙   |  30 |     30 | NULL   |
| 40 | 法国     |  40 |     40 | NULL   |
+----+----------+-----+--------+--------+
5 rows in set (0.00 sec)

先在Session2 rollback上一个SQL再执行SQL如下(按abc=10)

begin;
update ct set remark = '怀念2002年, 巴西夺冠, 中国进世界杯' 
where abc = 10;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

这里看到已经是2行受影响了
注意不要commit或rollback以便于我们分析行锁

然后我们在Session1里查看锁的详细信息

show engine innodb status\G; 

我们主要看差异如下图

一共上了5把锁多的2把就是我们新增的那条id =15, abc = 10记录对应的普通索引和聚集索引上的锁。

更严谨的小结

等值查询 匹配到 普通索引 时行级锁会上2m+1把锁m是匹配的记录数
上面例子匹配了2条记录所以上了2*2+1 = 5把锁分别是

1. abc=10, id =10 的普通索引记录上了Next-key Lock这里的范围是(下界值, 10]
2. abc=10, id =15 的普通索引记录上了Next-key Lock这里的范围是(下界值, 10]
3. id=10的聚集索引记录上了Record Lock单条
4. id=15的聚集索引记录上了Record Lock单条
5. abc=20的普通索引记录上了Gap-key Lock这里的范围是(10, 20)

无匹配索引

先在Session2 rollback上一个SQL再执行SQL如下(按abc=1)

begin
update ct set remark = '没有abc=1的记录~~' 
where abc = 1;

注意不要commit或rollback以便于我们分析行锁

然后我们在"Session1"查看锁的详细信息

show engine innodb status\G; 

我们主要看TRANSACTIONS这段如下图

小结

等值查询 未匹配到 普通索引 时行级锁 会上一把 间隙锁与聚集索引和唯一索引的规则相同具体不做赘述。

总结

再次说明本文分析加锁规则的事务隔离级别为默认的可重复读RR事务隔离级别。

有匹配索引
有唯一性的索引都会降级为Record Lock。

• 聚集索引对唯一匹配的 索引记录 上了 Record Lock这里是Next-key Lock降级优化为 Record Lock;
• 唯一索引对唯一匹配的 索引记录 上了 Record Lock对应的 聚集索引记录 也上了Record Lock都是Next-key Lock降级优化为 Record Lock;
• 普通索引对所有匹配的 索引记录 都上了 Next-key Lock对应的 聚集索引记录 都上了Record Lock 另外对匹配索引记录的next key记录上了Gap Lock。相当于把自身和前后的间隙都加锁了

无匹配索引

• 对于聚集索引、唯一索引、普通索引都只上了一把Gap Lock间隙锁锁的是 若条件值存在 的 next key索引记录。

如果感觉不错请收藏本专栏后面还有更详细的锁机制陆续放出。
关注我 天罡gg 分享更多干货 https://blog.csdn.net/scm_2008
大家的「关注 + 点赞 + 收藏」就是我创作的最大动力

同类文章
https://blog.csdn.net/weixin_48460141/article/details/124284443
https://blog.csdn.net/qq_39360632/article/details/127013702