MySQL 系列 #07：MySQL 锁机制

系统梳理 MySQL 锁的分类（全局锁/表锁/行锁）及 InnoDB 加锁规则，重点解析 Next-Key Lock 的锁范围计算，帮助排查死锁和锁等待问题。

目录

锁的分类

全局锁

作用

对整个数据库实例加锁，让数据库处于只读状态。

-- 加全局读锁
FLUSH TABLES WITH READ LOCK;  -- FTWRL

-- 解锁
UNLOCK TABLES;

主要用途

全量备份时，保证备份数据的一致性（备份期间数据库不能写入）。

更好的替代方案

-- mysqldump 使用 --single-transaction 选项
-- 利用 MVCC 的快照读，备份过程中数据库可以正常写入
mysqldump --single-transaction -h host -u user -p dbname > backup.sql

只适用于支持事务的引擎（InnoDB）。若表中有 MyISAM 引擎，仍需 FTWRL。

表锁

表锁（LOCK TABLES）

LOCK TABLES t1 READ, t2 WRITE;
-- 执行操作
UNLOCK TABLES;

粒度粗，并发性差，InnoDB 引擎下很少直接使用。

元数据锁（MDL，Metadata Lock）

自动加锁，无需显式调用。

读锁之间不互斥，读写锁、写写锁互斥。

MDL 锁的隐患：给表加字段为什么这么难

并发场景：

Session A：SELECT（持有 MDL 读锁）
Session B：SELECT（持有 MDL 读锁）— 与 A 不互斥，成功
Session C：ALTER TABLE ADD COLUMN（等待 MDL 写锁）— 被 A 和 B 阻塞，积压
Session D：SELECT（等待 MDL 读锁）— 被 C 阻塞（写锁请求优先）
...

→ 后续所有读请求都被 C 阻塞，连接池耗尽，数据库雪崩

安全 DDL 步骤

1. 查询是否有长事务占用 MDL 锁：

SELECT * FROM information_schema.innodb_trx;

2. 确认无长事务后再执行 DDL；或在 ALTER 时加 WAIT N 超时参数（MariaDB/部分版本支持）：

ALTER TABLE t WAIT 5 ADD COLUMN f INT;  -- 等待 5 秒，超时则放弃

3. 对大表 DDL，优先使用 pt-online-schema-change 或 gh-ost 等在线 DDL 工具。

行锁

两阶段锁协议（2PL）

• 行锁在需要时才加
• 行锁在事务结束时（COMMIT 或 ROLLBACK）才释放，不是用完立即释放

优化原则：把最可能导致冲突的行锁操作放到事务最后

-- 场景：转账，需要更新账户余额（高竞争行），还需要插入账单记录（低竞争）

-- ❌ 不好：账户余额更新在最前，行锁持有时间长
BEGIN;
UPDATE account SET balance = balance - 100 WHERE id = A;  -- 高竞争
UPDATE account SET balance = balance + 100 WHERE id = B;  -- 高竞争
INSERT INTO bill_record ...;  -- 低竞争
COMMIT;

-- ✅ 更好：低竞争操作先做，高竞争操作最后
BEGIN;
INSERT INTO bill_record ...;  -- 低竞争，先做
UPDATE account SET balance = balance - 100 WHERE id = A;  -- 高竞争
UPDATE account SET balance = balance + 100 WHERE id = B;  -- 高竞争
COMMIT;  -- 立即释放行锁，其他事务等待时间更短

InnoDB 加锁规则

InnoDB 可重复读隔离级别下，默认加 Next-Key Lock（左开右闭区间）。

两条基本原则

1. InnoDB 加锁的基本单位是 Next-Key Lock
2. 查找过程中访问到的对象才加锁

四个优化

1. 索引上的等值查询，命中唯一索引时，Next-Key Lock 退化为行锁
2. 索引上的等值查询，向右遍历到最后一个不满足等值条件的值时，Next-Key Lock 退化为间隙锁
3. 唯一索引上的范围查询，会访问到不满足条件的第一个值为止
4. 索引上的范围查询会锁住整个范围

示例

-- 表：id INT PRIMARY KEY, c INT, d INT, INDEX c(c)
-- 数据：(0,0,0), (5,5,5), (10,10,10), (15,15,15), (20,20,20), (25,25,25)

-- 场景1：等值查询命中（主键唯一索引等值）
SELECT * FROM t WHERE id=5 FOR UPDATE;
-- 加锁：行锁(5)，不加间隙锁（唯一索引等值命中，退化为行锁）

-- 场景2：等值查询未命中
SELECT * FROM t WHERE id=7 FOR UPDATE;
-- 访问到 id=10（不满足等值，退化为间隙锁），加锁：间隙锁(5,10)

-- 场景3：普通索引等值查询
SELECT * FROM t WHERE c=5 FOR UPDATE;
-- Next-Key Lock: (0,5]，再向右扫描到 c=10（不满足等值），退化为间隙锁(5,10)
-- 总共加锁：(0,5] + (5,10) + 主键行锁(5)

-- 场景4：主键范围查询
SELECT * FROM t WHERE id>=10 AND id<11 FOR UPDATE;
-- id=10：等值命中唯一索引，行锁(10)
-- 继续扫描到 id=15（不满足 id<11），Next-Key Lock(10,15]
-- 总共：行锁(10) + Next-Key Lock(10,15]

插入意向锁

INSERT 在真正插入行之前，先在目标位置的间隙上加一个"插入意向"标记，表示"我打算在这里插入"。属于特殊的间隙锁。

核心特性：同一间隙内的多个插入意向锁互不阻塞

间隙 (5, 10)，两个事务同时插入：
  事务 A：INSERT id=6  → 加插入意向锁(5,10)
  事务 B：INSERT id=8  → 加插入意向锁(5,10)
  → A 和 B 不互相阻塞，可并发执行

如果插入意向锁也互斥，同一间隙的并发插入就会全部串行化，性能极差。

与间隙锁的冲突关系

插入意向锁会被已有的间隙锁阻塞：

事务 A：SELECT * FROM t WHERE id=7 FOR UPDATE;
  → 加间隙锁(5,10)

事务 B：INSERT INTO t VALUES(6,...);
  → 尝试加插入意向锁(5,10)
  → 被事务 A 的间隙锁阻塞，等待

这正是间隙锁防止幻读的机制——阻止其他事务在锁定范围内插入新行。

间隙锁之间互相不阻塞（都是防御性的），但间隙锁会阻塞插入意向锁。

实验环境

-- 建表
CREATE TABLE t (
  id INT PRIMARY KEY,
  c  INT,
  d  INT,
  INDEX c(c)
) ENGINE=InnoDB;

-- 插入数据
INSERT INTO t VALUES (0,0,0),(5,5,5),(10,10,10),(15,15,15),(20,20,20),(25,25,25);

查看加锁情况（MySQL 8.0+）

-- 查当前事务持有的所有锁
SELECT
  ENGINE_TRANSACTION_ID AS trx_id,
  OBJECT_NAME           AS tbl,
  INDEX_NAME            AS idx,
  LOCK_TYPE,
  LOCK_MODE,   -- X / X,GAP / X,REC_NOT_GAP / X,INSERT_INTENTION
  LOCK_STATUS,
  LOCK_DATA    -- 锁住的键值
FROM performance_schema.data_locks;

-- 查锁等待关系（谁在等谁）
SELECT
  REQUESTING_ENGINE_TRANSACTION_ID AS waiting_trx,
  BLOCKING_ENGINE_TRANSACTION_ID   AS blocking_trx
FROM performance_schema.data_lock_waits;

LOCK_MODE 对照：

场景1 实测：等值命中主键

BEGIN;
SELECT * FROM t WHERE id=5 FOR UPDATE;

+--------+------+---------+-----------+---------------+-------------+-----------+
| trx_id | tbl  | idx     | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA |
+--------+------+---------+-----------+---------------+-------------+-----------+
| 292349 | t    | NULL    | TABLE     | IX            | GRANTED     | NULL      |
| 292349 | t    | PRIMARY | RECORD    | X,REC_NOT_GAP | GRANTED     | 5         |
+--------+------+---------+-----------+---------------+-------------+-----------+

解读：

• IX（表级意向排他锁）：InnoDB 内部协议，加行锁前必须先在表上加 IX。其他事务要加全表锁时只需检查表上有无 IX，无需扫描所有行锁。
• X,REC_NOT_GAP（纯行锁）：唯一索引等值命中的退化结果。原本应加 Next-Key Lock (0,5]，因为唯一索引保证不存在重复值，间隙不需要保护，直接退化为行锁。

加锁范围：仅 id=5 这一行
(0,5) 和 (5,10) 间隙均未锁 → 其他事务可以 INSERT id=3 或 id=7，不会阻塞

⚠️ 加锁规则较复杂，遇到线上锁等待时优先用 EXPLAIN + performance_schema 分析实际加锁情况。

死锁

死锁示例

-- 事务 A
BEGIN;
UPDATE t SET d=2 WHERE id=1;  -- 加行锁(1)
UPDATE t SET d=2 WHERE id=2;  -- 等待事务 B 释放行锁(2)

-- 事务 B（并发）
BEGIN;
UPDATE t SET d=2 WHERE id=2;  -- 加行锁(2)
UPDATE t SET d=2 WHERE id=1;  -- 等待事务 A 释放行锁(1)
-- 死锁！

死锁检测与处理

-- 查看最近一次死锁信息
SHOW ENGINE INNODB STATUS;
-- 查看 "LATEST DETECTED DEADLOCK" 部分

减少死锁的策略

1. 按固定顺序访问表和行（如多个事务都按 id 升序更新）
2. 缩小事务，减少锁持有时间
3. 高并发写入场景，考虑降为 RC 隔离级别（去掉间隙锁，死锁概率大降）

参考资料

• 《MySQL 实战 45 讲》— 第 06 讲：全局锁和表锁
• 《MySQL 实战 45 讲》— 第 07 讲：行锁功过
• 《MySQL 实战 45 讲》— 第 21 讲：为什么我只改一行的语句，锁这么多？
• InnoDB Locking