MySQL中的锁机制-京东云开发者社区

### 1 前言
锁是计算机协调多线程或进程并发访问某一资源的一种机制，在MySQL中，锁同样扮演者重要的角色。理解锁的原理有助于进行MySQL优化，提升系统性能。本文介绍了MySQL的锁机制，并结合简单实例进行验证。在阅读本文前，你需要具备的前置技术包含：MySQL中的索引、事务原理。

### 2 MySQL锁的分类
先来认识一下MySQL锁的分类，按不同纬度，锁的分类有：

![](//img1.jcloudcs.com/developer.jdcloud.com/2e873d5f-00da-4237-9711-3688e5fc510f20211201121645.png)

#### 2.1 按使用方式分类
可分为共享锁(S)、排他锁(X)。

1）共享锁(S)
针对同一份数据，多个读操作可以同时进行而不会互相影响，也称为读锁。大家一起可以分享这份数据，但是谁要修改它，抱歉，不可以~

2）排他锁(X)
排除任何其他事务的读写操作，也成为写锁。就是这么霸道，我要独享这份数据！

#### 2.2 按锁粒度分类
可分为表级锁、行级锁、页级锁。锁的目标范围不同。

1）表级锁
锁的范围为整张表。
- 优点：开销小，加锁快，不会出现死锁。
- 缺点：锁定粒度大，发生锁冲突的概率最高，并发度最低。

2）行级锁
锁定某一行。
- 优点：锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
- 缺点：资源开销大，加锁慢，易出现死锁。

3）页面锁
锁定数据页面，开销和加锁时间界于表锁和行锁之间，会出现死锁，锁定粒度界于表锁和行锁之间，并发度一般。

#### 2.3 存储引擎对锁的支持

MyISAM 和 MEMORY 存储引擎采用的是表级锁；
BDB 存储引擎采用的是页面锁，但也支持表级锁；
InnoDB 存储引擎既支持行级锁，也支持表级锁，但默认情况下是采用行级锁。

**---以上描述了MySQL中锁的分类，下面是常见存储引擎对锁的实现机制的介绍---**

### 3 MyISAM的锁模式
#### 3.1 表锁模式
分为读锁和写锁。其模式限制为：
- 对同一表读操作，不会阻塞其他线程的读操作，但会阻塞其他线程的写操作；共享读
- 对同一表的写操作，会阻塞其他线程读写操作。独占写

被阻塞的线程，会进入等待队列，获取到锁后才可以进行操作。

#### 3.2 锁兼容性
除读锁与读锁兼容外，读锁与写锁、写锁与写锁均不兼容。

![](//img1.jcloudcs.com/developer.jdcloud.com/8b66f080-eb2f-4bc0-ba97-2e5340aa29c720211201121824.png)

#### 3.3 如何加锁
在进行SQL的操作时，MySQL自动为表加锁，操作类型和锁对应关系如下：

![](//img1.jcloudcs.com/developer.jdcloud.com/be670070-f526-4d59-a07b-30d87fc5c64720211201121839.png)

除此之外，还可以使用LOCK TABLE命令加锁，用于保证多个SQL获取的数据版本内容一致性问题。

**问：是否会出现死锁？**
答：在加锁时，线程总是一次性获取到操作的全部锁，这不符合死锁的必要条件。因此，MyISAM引擎不会出现死锁的情况。

### 4 InnoDB锁模式
#### 4.1 共享锁（S）/排他锁（X）
InnoDB中的行锁分为共享锁和排他锁，作用在索引上。

共享锁(S)：
允许对同一行数据的加共享锁去读，阻止其他事务加排他锁。

排他锁(X)：
允许当前事务写操作，阻止其他事务加共享锁和排他锁。

案例：
假设有一张InnoDB表User，id为主键，除主键索引外无其他索引。

![](//img1.jcloudcs.com/developer.jdcloud.com/db96cb8e-ad64-4010-b305-898e1ef333a720211201121908.png)

场景1：使用索引时，验证是否加的行锁。

![](//img1.jcloudcs.com/developer.jdcloud.com/4e02b178-2080-46cd-a4e1-0976621e187120211201121927.png)

场景2：未使用索引时，验证是否会锁表。

![](//img1.jcloudcs.com/developer.jdcloud.com/baa6a529-e211-4566-94af-2a253275c56420211201121940.png)

#### 4.2 意向锁（IS/IX）
InnoDB支持多粒度锁机制，允许行锁和表锁共存。在InnoDB中，除表的排他锁和共享锁外，有两种意向锁也属于表锁，分别为：
- 意向共享锁(IS)：事务在给数据行加行共享锁之前，必须先取得该表的意向共享锁。
- 意向排他锁(IX)：事务在给数据行加行排他锁之前，必须先取得该表的意向排他锁。

意向锁是存储引擎自动维护的，用户无法手动控制意向锁。

意向锁解决的问题：
**如果一个事务想要在该表施加共享锁或排他锁，其不需要检查其他事务加在每一行或页上是否加锁，只需要检查表上的意向锁即可，极大提高了加锁效率。**

**锁兼容性：**
意向锁与任何行锁均兼容，这里的共享锁和独占锁指的是表锁。

![](//img1.jcloudcs.com/developer.jdcloud.com/bba52158-e2bd-4929-90f4-a8000b24e07220211201122013.png)

锁兼容性总结为：
**意向锁与意向锁均兼容，独占锁与任何锁都不兼容。共享锁与共享锁、意向共享锁兼容。**

**加锁方法：**
在执行常规增删改查SQL时，加锁情况如下：

![](//img1.jcloudcs.com/developer.jdcloud.com/266cdb75-d869-409b-9983-0cfa09bff6d420211201122034.png)

**关于for update和lock in share mode：**
- **for update：**使用这个语句查询记录时，会对访问索引上的行记录加排他锁，效果同update。其使用目的是排除其他更新记录操作，保证可以读到最新的结果。举例：在一个事务内需要先读取获取，再修改数据，这期间不允许其他事务对目标数据进行变更，此时使用for update查询，保证当前事务结束前，目标数据不会被其他事务修改。
- **lock in share mode：**确保当前事务读取的数据是最新的，允许其他事务同时读取目标记录，单不允许修改，当前事务也不会修改数据。

**InnoDB行锁实现方式：**
InnoDB的行锁是对索引上的数据加的锁，并不是针对数据块本身加锁。这意味着，只有使用了索引，才会有机会加行锁，否则，加表锁。

#### 4.3 间隙锁（Gap Lock）
在可重复读隔离级别下，当使用范围条件检索数据并加锁时，除了给符合条件的数据索引行加锁，还会给数据索引间隙加锁。间隙锁的目的是对于阻止其他事务在该间隙内插入新纪录，防止出现幻读。间隙锁与间隙锁本身兼容。

加锁范围：
根据检索条件向左寻找最靠近检索条件的记录值A，作为左区间，向右寻找最靠近检索条件的记录值B作为右区间，即锁定的间隙为(A,B)。

例如，有数据行id为1,2,3,6，执行如下SQL语句：
select * from t1 where id > 1 and id < 6 for update;

由于使用了当前读（for update），会给检索条件命中行加排他锁，例中，对id=2、3两行加了排他锁。虽然id=4不存在，但是也要加间隙锁，最终间隙锁区间为（1,6）。此时其他事务插入数据id=5，将会被阻塞。任何修改范围外的数据使其id=5也会阻塞。

案例：
存在表user：

![](//img1.jcloudcs.com/developer.jdcloud.com/075e218e-290d-4881-a0cd-d8f112ea3fec20211201133507.png)

现有A、B两个事务操作，操作同一范围内的数据：

![](//img1.jcloudcs.com/developer.jdcloud.com/64ebaec3-b2e6-42b0-83eb-bbc6db2aab2020211201133524.png)

#### 4.4 临键锁（Next-Key）
临键锁=行锁+间隙锁。在可重复读隔离级别下，如果使用for update或in share mode，会对区间和右侧行的索引加临键锁，是一个左开右闭的空间，如（3,5]。其作用是防止出现幻读。

### 5 锁优化建议

- 在允许的情况下，尽量使用较低的隔离级别。如RR隔离级别，会为了保证事务内数据一致性而阻塞其他修改目标行数据的事务；
- 尽量保证使用索引访问数据，避免造成锁表的情况；
- 尽量避免范围条件查询，这样会导致间隙锁锁住访问范围，导致其他事务等待；
- 控制事务大小，减少锁定的资源量和时间；

以上是个人在学习MySQL锁相关内容后的总结，由于篇幅问题，某些知识点未深入，欢迎感兴趣的小伙伴一起研究学习~

### 6 一次问题排查记录
在一次线上问题排查过程中，发现了一个数据库死锁导致的问题。现将问题还原如下。

表teacher，id为主键，t_no存在唯一索引。

```
CREATE TABLE `teacher` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `t_name` varchar(20) DEFAULT NULL,
  `t_no` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_t_no` (`t_no`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;
```

场景：并发插入两条数据并更新。

![](//img1.jcloudcs.com/developer.jdcloud.com/9b3682a5-d22a-4a06-85dc-d680fec6ce2a20211201133629.png)

问题跟踪：
按道理，插入不冲突的记录，然后修改这条记录，行锁不应该冲突。

查看MySQL操作日志：
show engine innodb status;

![](//img1.jcloudcs.com/developer.jdcloud.com/3a05fe2a-10a4-400e-b128-3a088f33af5520211201133717.png)

从日志中可以看出，事务20712在执行update时请求锁表，而事务20713持有行锁，不允许锁表，因此事务20712进入了锁等待状态。事务20713在执行update是也请求锁表，此时，发现事务20712持有行锁，不允许锁表，这就构成了死锁的条件：占有资源且循环等待。

那么问题来了，为什么update会导致锁表，更新的字段已经设置唯一索引了啊？

于是使用explain检查执行计划：
explain update teacher set t_no=21001 where t_no=2101;

![](//img1.jcloudcs.com/developer.jdcloud.com/03cf6bbc-4d3e-4d44-8c59-ccbe3b12e22820211201133738.png)

rows一列竟然为3，也就是说update未走索引，扫描了全表。重新检查执行的sql后发现，t_no为varchar类型，而更新的sql条件是数值，怀疑是类型不匹配导致全表扫描。修改sql后解决了死锁的问题。
explain update teacher set t_no=21001 where t_no='2101';

![](//img1.jcloudcs.com/developer.jdcloud.com/523829f3-1b6c-448e-914e-3f08261ac30e20211201133755.png)

至此，问题解决。

**参考：《高性能MySQL》、《深入浅出MySQL》**

------------
###### 自猿其说Tech-京东物流技术发展部
###### 作者：李猛