5.1 概述

锁是一种用于协调多个进程或线程并发访问共享资源的机制。在数据库中，锁用于保证数据并发访问的一致性和有效性。锁冲突是影响数据库并发访问性能的重要因素。

MySQL 中的锁按照锁的粒度可以分为以下三类：

• 全局锁：锁定数据库中的所有表。
• 表级锁：每次操作锁定整张表。
• 行级锁：每次操作锁定对应的行数据。

5.2 全局锁

5.2.1 介绍

全局锁是一种对整个 MySQL 数据库实例加锁的操作。一旦施加全局锁，整个实例将进入只读状态。在这种状态下，所有后续的数据操作语言（DML）写语句（例如 INSERT、UPDATE、DELETE）、数据定义语言（DDL）语句（例如 CREATE、ALTER、DROP）以及涉及更新操作的事务提交语句都将被阻塞，无法执行。

全局锁最典型的应用场景是执行全库的逻辑备份。通过锁定数据库中的所有表，可以获得一个一致性的数据视图，从而确保备份数据的完整性。

为了更好地理解全局锁的作用，考虑以下示例：

示例

假设数据库中存在三张表：tb_stock（库存表）、tb_order（订单表）和 tb_orderlog（订单日志表）。如果在没有全局锁的情况下进行数据备份，可能出现以下情况：

1. 首先备份 tb_stock 库存表。
2. 在备份过程中，业务系统执行了下单操作，导致库存扣减，并生成订单（即更新 tb_stock 表，并插入数据到 tb_order 表）。
3. 然后备份 tb_order 表。
4. 业务系统执行插入订单日志操作。
5. 最后备份 tb_orderlog 表。 在这种情况下，备份的数据将是不一致的。因为 tb_stock 表和 tb_order 表的数据不一致，tb_stock 表的库存数据可能未反映最新的订单信息。

为了避免此类问题，可以使用 MySQL 的全局锁。在执行逻辑备份之前，首先对整个数据库施加全局锁。一旦施加全局锁，所有其他的 DDL 和 DML 操作都将被阻塞。但是，数据查询语言（DQL）语句（例如 SELECT）仍然可以执行，而数据备份本质上就是查询操作。这样可以确保在逻辑备份的过程中，数据库中的数据不会发生任何变化，从而保证数据的一致性和完整性。

5.2.2 语法

1. 加全局锁：

   flush tables with read lock;

2. 数据备份：

   mysqldump -uroot -p1234 itcast > itcast.sql

3. 释放锁：

   unlock tables;

5.2.3 特点

在数据库中添加全局锁是一种较为重量级的操作，它会带来以下问题：

• 主库备份影响更新操作：如果在主数据库上进行备份，那么在备份的整个过程中，数据库将无法执行任何更新操作，这会导致业务停顿。
• 从库备份导致主从延迟：如果在从数据库上进行备份，那么在备份期间，从库将无法同步来自主库的 二进制日志 (binlog)，这会导致主从延迟现象。

为了解决上述问题，InnoDB 存储引擎提供了一种在备份时无需加锁即可实现数据一致性备份的方法，即使用 --single-transaction 参数。

mysqldump --single-transaction -uroot -p123456 itcast > itcast.sql

• mysqldump：MySQL 自带的备份工具，用于将数据库导出为 SQL 文件。
• --single-transaction：该参数告诉 mysqldump 在备份数据之前先开启一个事务。由于 InnoDB 引擎支持多版本并发控制 (MVCC)，因此在事务开始后，所有查询都将看到一致性的快照数据。这意味着，即使在备份过程中有新的数据变更，备份的数据仍然是事务开始时的状态，从而保证了数据的一致性。

5.3 表级锁

5.3.1 介绍

表级锁每次操作锁住整张表，锁定粒度大，发生锁冲突的概率最高，并发度最低。表级锁应用在 MyISAM、InnoDB、BDB 等存储引擎中。

对于表级锁，主要分为以下三类：

• 表锁 (Table Lock)
• 元数据锁 (Meta Data Lock, MDL)
• 意向锁 (Intention Lock)

5.3.2 表锁

表锁包括两类：

• 表共享读锁 (Read Lock)：允许多个客户端同时读取数据
• 表独占写锁 (Write Lock)：独占锁定，阻止其他客户端读写

表锁的加锁与释放使用如下：

1. 加锁

   lock tables 表名... read/write;

2. 释放锁

   unlock tables;

当一个客户端添加读锁时，其他客户端可执行读操作但写操作会被阻塞：客户端一持有读锁不影响客户端二的读取，但会阻塞其写入。

当一个客户端添加写锁时，其他客户端的读写操作均被阻塞：客户端一的写锁会阻塞客户端二的读写请求。

因此，读锁阻塞写操作但不阻塞读操作；写锁同时阻塞读与写操作。

5.3.3 元数据锁

元数据，简单來说，就是描述数据的数据。例如，表结构信息（列名、数据类型等等）就是元数据。数据库在执行 SQL 语句时，为了确保数据的一致性和并发控制，会对元数据加锁。 这种锁就称为元数据锁。

元数据锁由系统自动管理，用于维护表结构（元数据）一致性，防止数据操作语言 (DML) 与数据定义语言 (DDL) 的冲突。例如，未提交的事务中不能修改表结构。该锁机制在 MySQL 5.5 引入，行为如下表所示：

对应 SQL 操作	锁类型	说明
lock tables xxx read / write	SHARED_READ_ONLY / SHARED_NO_READ_WRITE
select ... lock in share mode	SHARED_READ	与 SHARED_WRITE 兼容，与 EXCLUSIVE 互斥
insert、update、delete、select ... for update	SHARED_WRITE	与 SHARED_READ、SHARED_WRITE 兼容，与 EXCLUSIVE 互斥
alter table ...	EXCLUSIVE	与所有其他 MDL 锁互斥

SELECT 使用共享读锁（SHARED_READ），而 INSERT、UPDATE、DELETE 使用共享写锁（SHARED_WRITE）。SHARED_READ 锁和 SHARED_WRITE 锁之间，以及不同的 SHARED_WRITE 锁之间，可以并行存在，不会互相阻塞。 多个事务可以同时读取相同的元数据，或者同时修改不同的元数据，而不会产生死锁或数据不一致的问题。

如果存在一个元数据读锁（SHARED_READ），那么其他事务就不能获取该元数据的排他锁（EXCLUSIVE）。反之亦然，如果已经有一个元数据的排他锁，那么其他事务就不能获取该元数据的共享读锁。

可通过以下 SQL 查询元数据锁状态，便于监控锁冲突：

select object_type,object_schema, object_name,lock_type, lock_duration from
performance_schema.metadata_locks ;

5.3.4 意向锁

为了避免 DML 在执行时，加的行锁与表锁的冲突，在 InnoDB 中引入了意向锁，使得表锁不用检查每行数据是否加锁，使用意向锁来减少表锁的检查。

• 无意向锁场景：客户端一添加行锁后，客户端二需检查所有行才能加表锁（效率低）。
• 有意向锁场景：客户端一在添加行锁时自动附加意向锁；客户端二只需检查意向锁即可判断表锁可行性（高效）。

意向锁可分为：

• 意向共享锁 (IS):
  • 由 select ... lock in share mode 添加。
  • 兼容性：与表读锁兼容，但与表写锁互斥。
• 意向排他锁 (IX):
  • 由 insert, update, delete, select ... for update 添加。
  • 兼容性：与表读锁及写锁均互斥。

一旦事务提交了，意向共享锁、意向排他锁，都会自动释放。

可以通过以下 SQL，查看意向锁及行锁的加锁情况：

select object_schema, object_name, index_name,lock_type, lock_mode, lock_data from performance_schema.data_locks;

5.4 行级锁

5.4.1 介绍

行级锁是每次操作锁住对应的行数据，具有最小的锁定粒度，发生锁冲突的概率最低，并发度最高。行级锁主要应用于 InnoDB 存储引擎。InnoDB 的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录本身加锁。

行级锁主要分为以下三类：

• 行锁 (Record Lock)：锁定单个行记录的锁，防止其他事务对此行进行 update 和 delete 操作。在 RC (Read Committed)、RR (Repeatable Read) 隔离级别下都支持。
• 间隙锁 (Gap Lock)：锁定索引记录间隙（不含该记录），确保索引记录间隙不变，防止其他事务在这个间隙进行 insert 操作，从而避免幻读。在 RR 隔离级别下支持。
• 临键锁 (Next-Key Lock)：行锁和间隙锁的组合，同时锁住数据和数据前面的间隙 Gap。在 RR 隔离级别下支持。

5.4.2 行锁

行锁是 InnoDB 的核心锁机制，用于控制共享和排他访问。

InnoDB 实现了以下两种类型的行锁：

• 共享锁 (S)：允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁。
• 排它锁 (X)：允许获得排它锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排它锁。

两种行锁的兼容情况如下：

当前锁类型\请求锁类型	S（共享锁）	X（排他锁）
S（共享锁）	兼容	冲突
X（排他锁）	冲突	冲突

常见的 SQL 语句在执行时，所加的行锁如下：

SQL	行锁类型	说明
INSERT ... UPDATE ... DELETE ...	排他锁	自动加锁
SELECT ...	不加锁	-
SELECT ... LOCK IN SHARE MODE	共享锁	需手动添加 LOCK IN SHARE MODE
SELECT ... FOR UPDATE	排他锁	需手动添加 FOR UPDATE

默认情况下，InnoDB 在 REPEATABLE READ 事务隔离级别运行，并使用临键锁进行搜索和索引扫描，以防止幻读。以下两条规则，说明了在不同检索条件下，InnoDB 会如何简化或升级临键锁机制：

1. 唯一索引等值检索时自动用行锁

   • 场景：当你的查询使用了一个唯一索引（UNIQUE 或 PRIMARY KEY），并且检索条件是“=某个具体值”这种等值匹配。
   • 行为：InnoDB 可以确定最多只有一行满足条件。
   • 结果：InnoDB 会直接对该行加行锁，而不会使用临键锁。从而减少锁的范围，提高并发性能，并且允许其他事务在已锁定行之间的间隙中插入新的行。

2. 无法使用索引时退化为“表锁”效果

   • 场景：如果查询条件无法使用任何索引，或者使用了非索引字段，InnoDB 只能进行全表扫描。
   • 行为：由于行锁是基于索引的，因此 InnoDB 只能将表中的每一行都当作“经过”的行来加锁。
   • 结果：相当于对整个表的所有行都加了锁，从而导致锁的范围扩大，并发性能大幅下降，实际上产生了类似于表锁的效果。

可以通过以下 SQL，查看意向锁及行锁的加锁情况：

select object_schema, object_name, index_name, lock_type, lock_mode, lock_data from performance_schema.data_locks;

以下面的 stu 表为例进行行锁的演示。

CREATE TABLE `stu` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`age` int NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB CHARACTER SET=utf8mb4;
  
INSERT INTO `stu`
VALUES
(1, 'tom', 1),
(3, 'cat', 3),
(11, 'jetty', 11),
(19, 'lily', 19),
(25, 'luci', 25);

普通 `SELECT` 不加锁

在事务中执行普通的 SELECT 语句不会触发任何锁机制。

select * from stu where id = 1;

mysql> select object_schema, object_name, index_name, lock_type, lock_mode, lock_data from performance_schema.data_locks;
Empty set (0.00 sec)

共享锁兼容性

开启事务 A，执行 SELECT ... LOCK IN SHARE MODE（加共享锁）。那么事务可以 B 执行相同操作（共享锁可共存）。

事务 A

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from stu where id = 1 lock in share mode;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | tom  |   1 |
+----+------+-----+
1 row in set (0.00 sec)

mysql> select object_schema, object_name, index_name, lock_type, lock_mode, lock_data from performance_schema.data_locks;
+---------------+-------------+------------+-----------+---------------+-----------+
| object_schema | object_name | index_name | lock_type | lock_mode     | lock_data |
+---------------+-------------+------------+-----------+---------------+-----------+
| hmmysql       | stu         | NULL       | TABLE     | IS            | NULL      |
| hmmysql       | stu         | PRIMARY    | RECORD    | S,REC_NOT_GAP | 1         |
+---------------+-------------+------------+-----------+---------------+-----------+
2 rows in set (0.00 sec)

执行以上 SQL 语句后，performance_schema.data_locks 表中会显示一个针对 stu 表的 PRIMARY 索引的共享锁，锁定的数据为 1。

事务 B

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from stu where id = 1 lock in share mode;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | tom  |   1 |
+----+------+-----+
1 row in set (0.00 sec)

mysql> select object_schema, object_name, index_name, lock_type, lock_mode, lock_data from performance_schema.data_locks;
+---------------+-------------+------------+-----------+---------------+-----------+
| object_schema | object_name | index_name | lock_type | lock_mode     | lock_data |
+---------------+-------------+------------+-----------+---------------+-----------+
| hmmysql       | stu         | NULL       | TABLE     | IS            | NULL      |
| hmmysql       | stu         | NULL       | TABLE     | IS            | NULL      |
| hmmysql       | stu         | PRIMARY    | RECORD    | S,REC_NOT_GAP | 1         |
| hmmysql       | stu         | PRIMARY    | RECORD    | S,REC_NOT_GAP | 1         |
+---------------+-------------+------------+-----------+---------------+-----------+
4 rows in set (0.00 sec)

执行以上 SQL 语句后，performance_schema.data_locks 表中会显示两个针对 stu 表的 PRIMARY 索引的共享锁，锁定的数据均为 1。

:::

共享锁与排他锁互斥

事务 A 执行 SELECT ... LOCK IN SHARE MODE 语句，为 id = 1 的行添加共享锁。此时，如果事务 B 尝试执行 UPDATE stu SET name = 'jack' WHERE id = 1 语句，则会被阻塞，因为共享锁与排他锁是互斥的。

事务 A

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from stu where id = 1 lock in share mode;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | tom  |   1 |
+----+------+-----+
1 row in set (0.00 sec)

mysql> select object_schema, object_name, index_name, lock_type, lock_mode, lock_data from performance_schema.data_locks;
+---------------+-------------+------------+-----------+---------------+-----------+
| object_schema | object_name | index_name | lock_type | lock_mode     | lock_data |
+---------------+-------------+------------+-----------+---------------+-----------+
| hmmysql       | stu         | NULL       | TABLE     | IS            | NULL      |
| hmmysql       | stu         | PRIMARY    | RECORD    | S,REC_NOT_GAP | 1         |
+---------------+-------------+------------+-----------+---------------+-----------+
2 rows in set (0.00 sec)

事务 B

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update stu set name = 'jack' where id = 1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

程序会被阻塞，因为事务 B 添加了一把排他锁。

:::

排他锁互斥

事务 A 执行 UPDATE stu SET name = 'jack' WHERE id = 1 语句，为 id = 1 的行添加排他锁。此时，如果事务 B 尝试执行 UPDATE stu SET name = 'tom' WHERE id = 1 语句，则会被阻塞，因为排他锁之间是互斥的。

事务 A

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update stu set name = 'jack' where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

事务 B

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update stu set name = 'tom' where id = 1;

事务 B 会被阻塞，直到事务 A 提交。

:::

无索引行锁升级为表锁

事务 A 执行 UPDATE stu SET name = 'lei' WHERE name = 'lily' 语句，由于 name 字段没有索引，因此会进行全表扫描，导致行锁升级为表锁。此时，如果事务 B 尝试执行 UPDATE stu SET name = 'dog' WHERE id = 3 语句，则会被阻塞。

事务 A

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update stu set name = 'lei' where name = 'lily';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

事务 B

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update stu set name = 'dog' where id = 3;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

事务 B 无法执行，处于阻塞状态。

:::

5.4.3 间隙锁 & 临键锁

默认情况下，InnoDB 在 REPEATABLE READ 事务隔离级别运行，并使用临键锁进行搜索和索引扫描，以防止幻读。下面三条规则说明了在不同检索条件下，InnoDB 如何在临键锁、间隙锁之间优化或退化锁的粒度：

1. 唯一索引等值查询（目标记录不存在）优化为间隙锁

   • 场景：当使用唯一索引进行等值查询，且查询的值在表中不存在时（WHERE unique_col = X，但值 X 不存在）。
   • 行为：InnoDB 会将锁简化为仅对该值位置的间隙锁。
   • 结果：阻止其他事务在该位置插入值为 X 的新行，防止幻读，缩小锁范围，提高并发性。

2. 非唯一普通索引等值查询在向右遍历、遇到不满足的最后一个值时临键锁退化为间隙锁

   • 场景：使用非唯一索引进行等值查询（WHERE non_unique_col = Y），且该值在索引中不存在。InnoDB 向右遍历索引寻找插入点，直到找到一个大于 Y 的值。
   • 行为：临键锁在边界处退化为间隙锁。
   • 结果：防止其他事务在该间隙插入值为 Y 的行，维持幻读防护，避免对不存在的记录加锁，控制锁粒度。

3. 唯一索引的范围查询只扫描并锁定到第一个不满足条件的值为止

   • 场景：通过唯一索引进行范围查询（WHERE unique_col > A AND unique_col < B）。
   • 行为：InnoDB 按索引顺序向右扫描，给满足范围的记录加上相应的临键锁，并在遇到第一个不在范围内的值时停止访问和加锁。
   • 结果：锁定范围精确覆盖查询范围，防止范围内幻读，避免锁定无关条目，保持锁的精确性和并发性。

注

间隙锁的作用是防止其他事务插入间隙。多个事务可以同时在同一间隙上持有间隙锁，间隙锁可以共存。

下面我们来分别分析这几种情况：

索引上的等值查询（唯一索引）

当目标记录不存在时，唯一索引的等值查询会优化为间隙锁。

• 场景：数据库中 id=5 的记录不存在，事务 A 执行 UPDATE ... WHERE id=5。
• 结果：事务 A 会在 id=5 的位置加上间隙锁，事务 B 尝试插入 id=7 的记录时会被阻塞，因为 7 位于该间隙内。

事务 A

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update stu set age = 10 where id = 5;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> select object_schema, object_name, index_name,lock_type, lock_mode, lock_data from performance_schema.data_locks;
+---------------+-------------+------------+-----------+-----------+-----------+
| object_schema | object_name | index_name | lock_type | lock_mode | lock_data |
+---------------+-------------+------------+-----------+-----------+-----------+
| hmmysql       | stu         | NULL       | TABLE     | IX        | NULL      |
| hmmysql       | stu         | PRIMARY    | RECORD    | X,GAP     | 11        |
+---------------+-------------+------------+-----------+-----------+-----------+
2 rows in set (0.00 sec)

通过查询 performance_schema.data_locks 表，可以看到存在一个排他锁 (X) 和一个间隙锁 (GAP)，锁定的是 PRIMARY 索引上 11 之前的间隙。

事务 B

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into stu values (10, 'Ruby', 18);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

由于 id = 11 之前的间隙被事务 A 锁住，事务 B 尝试插入 id = 10 的记录时会被阻塞，并最终导致锁等待超时。

:::

索引上的等值查询（非唯一普通索引）

当末值不满足条件时，非唯一索引等值查询的临键锁会退化为间隙锁。

• 场景：在非唯一索引 age 上执行 SELECT ... WHERE age=3 LOCK IN SHARE MODE。
• 结果：会锁住邻近的间隙，直到遇到不满足条件的值。

假设存在一个名为 idx_stu_age 的索引在 stu 表的 age 列上 (CREATE INDEX idx_stu_age ON stu(age))。执行以下查询：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from stu where age = 3 lock in share mode;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  3 | cat  |   3 |
+----+------+-----+
1 row in set (0.00 sec)

mysql> select object_schema, object_name, index_name,lock_type, lock_mode, lock_data from performance_schema.data_locks;
+---------------+-------------+-------------+-----------+---------------+-----------+
| object_schema | object_name | index_name  | lock_type | lock_mode     | lock_data |
+---------------+-------------+-------------+-----------+---------------+-----------+
| hmmysql       | stu         | NULL        | TABLE     | IS            | NULL      |
| hmmysql       | stu         | idx_stu_age | RECORD    | S,GAP         | 11, 11    |
| hmmysql       | stu         | idx_stu_age | RECORD    | S             | 3, 3      |
| hmmysql       | stu         | PRIMARY     | RECORD    | S,REC_NOT_GAP | 3         |
+---------------+-------------+-------------+-----------+---------------+-----------+
4 rows in set (0.00 sec)

从 performance_schema.data_locks 表的结果可以看出，间隙锁 (GAP) 锁住了 idx_stu_age 索引上 11 之前的间隙。同时，age = 3 的记录也被加上了共享锁 (S)。锁会向后遍历至不满足条件的值，本例中，11 之前的间隙被锁住，意味着任何尝试在 age 为 (3, 11) 范围内的插入操作都会被阻塞。

索引上的范围查询（唯一索引）

在唯一索引上进行范围查询时，锁会覆盖范围至首个不满足条件的值。

• 场景：执行 SELECT ... WHERE id>=19 LOCK IN SHARE MODE。
• 结果：
  • id=19：行锁（等值点）。
  • (19,25]：临键锁（包含间隙）。
  • (25,+∞)：临键锁（扩展至正无穷）。

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from stu where id >= 19 lock in share mode;
+----+------+-----+
| id | name | age |
+----+------+-----+
| 19 | lei  |  19 |
| 25 | luci |  25 |
+----+------+-----+
2 rows in set (0.00 sec)

mysql> select object_schema, object_name, index_name,lock_type, lock_mode, lock_data from performance_schema.data_locks;
+---------------+-------------+------------+-----------+---------------+------------------------+
| object_schema | object_name | index_name | lock_type | lock_mode     | lock_data              |
+---------------+-------------+------------+-----------+---------------+------------------------+
| hmmysql       | stu         | NULL       | TABLE     | IS            | NULL                   |
| hmmysql       | stu         | PRIMARY    | RECORD    | S             | supremum pseudo-record |
| hmmysql       | stu         | PRIMARY    | RECORD    | S             | 25                     |
| hmmysql       | stu         | PRIMARY    | RECORD    | S,REC_NOT_GAP | 19                     |
+---------------+-------------+------------+-----------+---------------+------------------------+
4 rows in set (0.00 sec)

通过查看 performance_schema.data_locks 表，可以发现：

• S,REC_NOT_GAP 锁在 id = 19 的记录上，表示这是一个共享锁，并且不是间隙锁。
• S 锁在 id = 25 的记录上，表示这是一个共享锁。
• S 锁在 supremum pseudo-record 上，表示锁扩展到了正无穷。

这意味着查询锁定了 id = 19 和 id = 25 的记录，以及 id = 25 之后的所有间隙，直到正无穷。任何尝试在这些范围内插入或修改记录的事务都可能被阻塞，从而保证了范围查询的一致性和避免了幻读。