# 前言

本篇只介绍 MySQL 锁的基本知识。

我的 MySQL 版本是 MySQL 5.7.34, 建议使用 MySQL 5.6 及之后的版本。

## 先上一个图

---

## 为什么要使用锁？

个人理解： 使用锁就是实现事务的原子性与隔离性、数据的一致性。

## 扩展阅读

DDL : 数据定义语言（Data Definition Language）, 使用 create 、 alter、drop 来定义数据库的 库、表、列 等, 操作的是数据表结构。

DML : 数据操纵语言（Data Manipulation Language）, 使用 select、insert、update、delete 操作数据库中的数据。

DQL : 数据控制语言（Data Query Language）, 使用 select 、from、where、group by 等查询数据库中的数据。

点我查看 - MySQL - 一文快速了解 MySQL 全局锁、表级锁、行级锁、元数据锁、自增锁、意向锁、共享锁、独占锁、记录锁、间隙锁、临键锁、死锁

点我查看 - MySQL 常用存储引擎简介

点我查看 - MySQL - 可重复读隔离级别在哪种情况下会出现幻读

点我查看 - MySQL 三范式理解

---

一、MySQL 全局读锁

1.1 全局读锁的基本概念

全局锁 就是对整个数据库的表加读锁。加全局读锁后, 增删改、建表、修改表结构 等操作都会被阻塞。

---

1.2 全局读锁的使用场景

• 备份数据库
• 我也就这个场景实战用过, 当时没有从库且项目是内部使用, 项目启动维护可控, 老板一句话的事儿 🌝
• 其它的欢迎各位补充啊

---

1.3 全局读锁的加锁与解锁

# 需要你连接数据库并使用数据库, 以下为示例

# mysql -u root -p

# use testDatabase;

# 加读锁
flush tables with read lock;

# 解锁
unlock tables;
1
2
3
4
5
6
7
8
9
10
11

---

1.4 数据库的备份或导表

请看我另一篇文章 MySQL 使用命令行导出导出数据库、数据表、表结构

---

二、MySQL 表锁

2.1 元数据锁（Matadata Lock - MDL）

点我查看 MySQL 官网 - 元数据锁

---

2.1.1 MDL 锁基本信息

相信你一定见过这段提示 : Waiting for table metadata lock。

MySQL 使用 元数据锁 来管理对数据库对象的并发访问并确保数据一致性。

• MySQL5.5 版本起明确 MDL 概念

• DDL 操作加 MDL写锁

• DML 操作加 MDL读锁

• 不用显示使用, 在访问时系统会自动添加 MDL锁

• 如果有多个 DDL、DML、DQL 操作语句, 则默认首先满足最高优先级的锁请求

• 默认情况下, 写锁请求的优先级高于读锁请求

• 一个一个的获取 元数据锁 语句, 并进行死锁检测

2.1.2 MDL 简单实例

说白话 : 当事务操作开启后, 对数据表进行 DDL、DML、DQL 操作时, 会给数据表加上 MDL 锁, 不可以对数据表结构进行操作, 从而保护数据的一致性。

简单实例截图 : (RR 隔离级别)

---

2.2 自增锁（AUTO-INC 锁）

点我查看 MySQL 官网 - 自增锁

自增锁是一种特殊的表级锁, 常用情况由插入到具有 AUTO_INCREMENT 列的数据表中的事务使用。

在实际工作当前, 比如 ID 字段为自增字段, 在插入数据时, 就可以不指定 ID 的值, 而由系统自动递增赋值, 想一想, 是不是这样。

2.2.1 自增锁 - 锁定模式

AUTO_INCREMENT 生成的自动递增锁定模式由 innodb_autoinc_lock_mode 变量进行配置。

innodb_autoinc_lock_mode有三种设置值, 分别为 0、1、2 。

• MySQL 8.0 前, 默认模式为 innodb_autoinc_lock_mode=1
• MySQL 8.0 起, 默认模式为 innodb_autoinc_lock_mode=2

2.2.2 自增锁 - 自我理解

以下自增锁的三种模式的名称与个人理解的知识整理来自 MySQL 官网。

• 传统锁定模式 : innodb_autoinc_lock_mode = 0

  
  

  理解一 :

  • 向含有 AUTO_INCREMENT 列插入数据时, 为每行记录的自增键赋值自增的值, 并分配一个 AUTO-INC 表锁。

  • 并且执行完插入语句后就会释放而不是等提交事务后才释放, 确保自动递增的值都是连续的。

  
  

  理解二 :

  • 当 从数据库 复制 主数据库 的 SQL 语句时, 会保持与主数据库 相同的数据, 因为 INSERT 的执行条数与顺序是确定性的。
  • 再结合官网插入 1000 行的语句实例, 也就是 当预先不确定执行的 INSERT 条数并且需要保证自增的顺序时, 就采用 AUTO-INC 表锁。

  
  

• 连续锁定模式 :innodb_autoinc_lock_mode = 1

  
  

  理解一 :

  • 当预先不确定执行的 INSERT 条数并且需要保证自增的顺序时, 就采用 AUTO-INC 表锁
  • 当预先确定执行的 INSERT 条数, 会采用 互斥锁(一种轻量级锁)

  
  

• 交错锁定模式 : innodb_autoinc_lock_mode = 2

  
  

  理解一 :

  • 不使用 AUTO-INC 表锁
  • 执行效率在这三种模式之间是最好的
  • 当批量插入时, 自动增量值之间可能会存在 间隙或错乱

  理解二 :

  • 如果在并发的情况下, 并发 INSERT 的执行结果不确定, 则最后生成的自增值可能不是连续的
  • 在主从数据库中应用时, 该模式无法保证可靠的从主数据库复制

  
  

• 产生序列间隙 和 “丢失”自动增量值

  
  

  第一种情况（未重启 MySQL服务）:

  • 这三种模式中, 如果已生成自增值, 并且事务回滚, 则事务内已生成的自增值将会 丢失且不可被重用。

  • 实例图 :

    

  
  

  第二种情况 :

  • MySQL 5.7 及之前版本, 事务回滚后并重启 MySQL 服务, 则自增的 ID 值将重新分配给回滚之前最大的自动 ID 值。
  • MySQL 8.0 中, 自增 ID 值将被持久化, 防止重复使用以前分配的值。

---

2.3 意向锁（隐式）

意向锁 是 表级锁, 作用是事务即将操作的记录需要哪种类型的锁（共享锁或独占锁）。

意向锁有两种类型 : 意向共享锁（IS） 和 意向独占锁（IX）。

加锁必须保证当前表没有任何锁, 如果每一行都检查, 效率特别低, 这时只需要检查是否存在 意向锁 就行了。

2.3.1 意向共享锁（IS）

意向共享锁 :

• 表示事务打算在表中记录上设置 共享锁。
• 在事务获取表中记录的 共享锁 之前, 需要先获取数据表中的 意向共享锁或更高级别的锁。

2.3.2 意向独占锁（IX）

意向独占锁 :

• 表示事务打算在表中记录上设置 独占锁

• 在事务获取表中记录的 独占锁 之前, 需要先获取数据表中的 意向独占锁。

2.3.3 意向锁的兼容性

---

如果锁与现有锁兼容, 则将授予事务权限, 如果冲突, 事务阻塞直到释放锁冲突。

锁类型	X 锁	IX 锁	S 锁	IS 锁
X 锁	冲突	冲突	冲突	冲突
IX 锁	冲突	兼容	冲突	兼容
S 锁	冲突	冲突	兼容	兼容
IS 锁	冲突	兼容	兼容	兼容

---

2.4 表共享锁（S）

在任何隔离级别中, 只要加了 表共享锁 , 所有客户端都可 DQL 操作, 但不可 DDL、DML 操作。

2.4.1 表共享锁操作命令

# 加表共享锁
lock tables 表名 read;

# 解锁
unlock tables;
1
2
3
4
5

2.4.2 表共享锁实例图

---

2.5 表独占锁（X）

在任何隔离级别中, 只要加了 表独占锁 , 其他客户端都不可以进行 DDL、DML、DQL 操作。

2.5.1 表独占锁操作命令

# 加表独占锁
lock tables 表名 write;

# 解锁
unlock tables;
1
2
3
4
5

2.5.2 表独占锁实例图

---

三、MySQL 行锁

InnoDB 索引引擎支持行锁与表锁, 前提是所使用的字段必须建立索引, 锁必须打在索引上, 否则会转为表锁。

3.1 行共享锁（S）

点我查看 MySQL 官网 - InnoDB 共享锁

• 共享锁又称读锁, 简称 S 锁。

• 行共享锁在普通查询时不会加锁, 只有在事务当中才会加锁, 加行共享锁后, 非 Serializable 隔离级别下, 其他事务只能读, 不能更新删除。

  例 : 在事务 A 当中, 查询一行, 产生了 行共享锁, 其他事务可以获取同样的 行共享锁, 但不能获取 行独占锁, 只有 事务 A 释放 行共享锁 后事务 B 才可以获得 行独占锁。

3.1.1 加行共享锁

# 在 select 查询语句末尾加 lock in share mode
# 例:
select name from user where id = 5 lock in share mode;
1
2
3

3.1.2 兼容表格

模式	行共享锁	行独占锁
行共享锁	兼容	互斥
行独占锁	互斥	互斥

---

3.2 行独占锁（X）

点我查看 MySQL 官网 - InnoDB 独占锁

• 独占锁又称写锁、排它锁, 简称 S 锁。
• 事务加行独占锁后, R-UC、RC、RR 隔离级别下, 其他事务中 可读 、不能更新, 只能等待释放。
• 事务加行独占锁后, Serializable 隔离级别下, 其他事务中 不可读、不能更新, 只能等待释放。

3.2.1 加行独占锁

# 在语句末尾加 for update
# 例:
select name from user where id = 5 for update;
1
2
3

3.2.2 兼容表格

模式	共享锁	独占锁
共享锁	兼容	互斥
独占锁	互斥	互斥

---

3.3 记录锁（Record Locks）

点我查看 MySQL 官网 - InnoDB 记录锁

• 记录锁是对索引记录的锁, 记录锁都是独占锁。
• 如果该记录存在, 即使定义的表没有索引, InnoDB 也会创建一个隐藏的聚集索引并将该索引用于记录锁定, 但如果作用锁的列没有索引, 则进行锁表。
• 点我查看 - MySQL官网 聚集索引

---

3.4 间隙锁（Gap Locks）

点我查看 MySQL 官网 - InnoDB 间隙锁

• 只有在可重复读的隔离级别才会有间隙锁！

• 加间隙锁后, 其他事务不可在该间隙内插入数据, 解决 幻读 的情况。

• 间隙锁可以共存, 事务 A 不会阻止事务 B 在同一间隙上使用间隙锁。

• 间隙锁是存在于索引记录之间、第一条索引记录之前或最后一条索引记录之后的间隙上的锁。

  • 范围查询: 查询某范围内的数据（比如 10 < id < 20）会产生间隙锁。
  • 单条查询: 查询单条数据（比如 id = 5）, 如果记录存在, 则加记录锁；记录不存在, 则加记录锁与间隙锁。

  • 普通索引: 任何情况下都会产生间隙锁
  • 唯一索引: 有普通索引时, 优先根据普通索引排序, 再根据唯一索引排序
  • 点我查看 - 普通索引与唯一索引结论 出处文章

3.4.1 插入意向锁

插入意向锁 :

• 插入意向锁 是在执行插入操作之前设置的一种 间隙锁。
• 即多个事务之间, 数据如果插入到同一索引间隙中但是位置不同, 则不需要相互等待。
  1. 例如: 有10 和 20 的索引记录（中间无数据）
  2. 有两个插入 12 和 15 的数据的事务
  3. 两个事务在获得 X 锁之前, 产生 10 与 20 之间的插入意向锁间隙, 不会发生阻塞
  4. 因为12 与 15 的行不冲突

---

3.5 临键锁（Next-Key Locks）

点我查看 MySQL 官网 - InnoDB 临键锁

• 临键锁是 记录锁 与 间隙锁 的组合。

---

四、MySQL 死锁

点我查看 MySQL 官网 - InnoDB 死锁

• 死锁因 写操作 发生, 当事务之间的锁互相依赖, 都在等待释放时, 就发生了死锁。
• MyISAM 不会发生死锁, 因为 MyISAM 不支持事务, 当需要获取锁时, 需要等待之前使用的锁释放。但从网上别的文章中看, 当并发量高且处理多张表时, 可能会出现死锁, 本人也没有实验过。
• InnoDB 支持事务, 业务逻辑复杂时, 很可能发生死锁。

4.1 死锁示例

4.1.1 死锁超时释放

• 第一步: 开启事务 A
• 第二步: 在事务 A 中查询 id = 17 并加共享锁, 此时其它事务只能读取不能修改删除
• 第三步: 开启事务 B
• 第四步: 在事务 B 中删除 id = 17 的数据
• 第五步: 此时不做任何操作, 超时会自动回滚小事务
  • InnoDB 使用 innodb_lock_wait_timeout 变量, 默认回滚超时为 50 秒。
  • 只回滚当前语句, 而不是整个事务。
  • 要想回滚整个事务, 请点我查看 - innodb_rollback_on_timeout

4.1.2 死锁冲突释放

• 第一步: 开启事务 A
• 第二步: 在事务 A 中查询 id = 17 并加共享锁, 此时其它事务只能读取不能修改删除
• 第三步: 开启事务 B
• 第四步: 在事务 B 中删除 id = 17 的数据
  • 因为删除需要 X 锁, S 锁与 X 锁互斥, 所以发生阻塞, 事务 B 需要等事务 A 释放 id = 17 的 S 锁
• 第五步: 在事务 A 中也删除 id = 17 的数据
  • 因为删除需要 X 锁, X 锁与 X 锁互斥, 事务 B 此时已经有删除动作, 已产生 X 锁, 事务 A 的删除 X 锁等待事务 B 的删除 X 锁释放, 而事务 B 的 X 锁又等待事务 A 的 S 释放, 此时就产生了死锁。
• 第六步: InnoDB 检测到死锁, 所以回滚事务当中的小事务, 从而打破死锁。
  • 只回滚当前语句, 而不是整个事务。
  • 要想回滚整个事务, 请点我查看 - innodb_rollback_on_timeout

---

4.2 死锁检测

• 使用 innodb_deadlock_detect 配置选项禁用死锁检测, 默认是开启状态。
• 启动 死锁检测时, 在高并发时, 多个线程等待同一个锁, 死锁检测 会导致速度非常慢。
• 启动 死锁检测 时, InnoDB 自动检测事务 死锁 并回滚一个或多个事务从而打破死锁。
• 事务的大小从插入、更新或删除的行数决定。

---

4.3 最小化处理死锁

• 减少事务当中不必要的 SQL 操作, 这样事务持续的时间缩短, 减少发生死锁的可能性。
• 在业务支持的情况下, 进行相关更改后立即提交事务, 降低发生死锁的可能性。
• 根据业务实际情况, 使用表级锁会防止并发更新, 操作按顺序执行, 避免死锁。但会降低系统的响应速度。
• 使用 SHOW ENGINE INNODB STATUS 来查看最近的死锁原因。
• 启用 innodb_print_all_deadlocks 收集更广泛的死锁信息, 记录在错误日志当中。
• 当看到死锁原因后, 请调整优化业务逻辑代码, 以避免再次发生死锁。