【MySQL】深入分析 锁机制（一）行锁 加锁规则 之 等值查询

2022-12-11 09:16:46 浏览数 (1)

文章目录

  • 前言
  • 一、共享锁（S）和排它锁（X）
  • 二、行锁的3种算法
    • Record Lock
    • Gap Lock
    • Next-key Lock
  • 三、加锁规则 之 等值查询
    • 分析数据准备
    • 3.1 聚集索引
      • 有匹配索引
      • 无匹配索引
    • 3.2 唯一索引
      • 有匹配索引
      • 无匹配索引
    • 3.3 普通索引
      • 有匹配索引
      • 无匹配索引
  • 总结

前言

如何控制并发是数据库领域中非常重要的问题之一，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时，那么索引就存在如下间隙（圆括号表示不包括区间点）：

代码语言：javascript复制
	(下界限, 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 Lock：Record Lock Gap Lock 的组合，既锁 索引记录 又锁 间隙，很多地方都称它是临键锁或邻键锁，但我觉得直接翻译成下一个键锁会更好理解，意思是锁由“下一个键负责”，原则：左开右闭 或称 前开后闭 。 上面的例子的区间为（圆括号表示不包括区间点，方括号表示包括区间点）：
代码语言：javascript复制
	(下界限, 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）

分析数据准备

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

代码语言：javascript复制
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);

预览下数据：

代码语言：javascript复制
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）事务隔离级别
代码语言：javascript复制
mysql> select @@tx_isolation;
 ----------------- 
| @@tx_isolation  |
 ----------------- 
| REPEATABLE-READ |
 -----------------
  • 如果不是，需要在各Session中设置一下：
代码语言：javascript复制
set tx_isolation='repeatable-read';
  • Session1中开启锁的监视器：
代码语言：javascript复制
SET GLOBAL innodb_status_output=ON;
SET GLOBAL innodb_status_output_locks=ON;
  • 查询是否开启：
代码语言：javascript复制
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)：

代码语言：javascript复制
begin;
update ct set remark = '怀念2002年, 巴西夺冠, 中国进世界杯' 
where id = 10;

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

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

代码语言：javascript复制
show engine innodb statusG;

我们主要看TRANSACTIONS这段，如下图：

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

  1. 1 row lock(s)就代表上了1个行锁（不要理解成只锁了1行
    sql 数据库 云数据库SQLServer

    0 人点赞