Chapter 3: MySQL 锁与死锁完全指南

难度：高级阅读时间 ~75 min 基于 MySQL 8.0 / 8.4

锁是并发控制的基石，也是性能问题和故障的高发区。本章从 InnoDB 锁类型全景图开始，逐一拆解 Record Lock、Gap Lock、Next-Key Lock、Insert Intention Lock 的内部机制，给出完整的锁兼容矩阵。通过 15+ 个 SQL 加锁场景分析，展示不同语句在不同隔离级别下的精确加锁行为。深入死锁检测算法（wait-for graph）、SHOW ENGINE INNODB STATUS 解读方法，并提供交互式死锁模拟器让你在浏览器中复现典型死锁。最后覆盖 MDL 元数据锁、热点行优化方案和真实生产事故案例。

1. InnoDB 锁类型全景图

1.1 锁类型层次结构

InnoDB 的锁体系可以从粒度和模式两个维度来理解。下面的 ASCII 图展示了所有锁类型及其层次关系：

┌─────────────────────────────────────────────────────────────────────────────┐ │ InnoDB Lock Taxonomy │ ├─────────────────────────────────────────────────────────────────────────────┤ │ │ │ ┌─── By Granularity ──────────────────────────────────────────────────┐ │ │ │ │ │ │ │ Table-Level Row-Level │ │ │ │ ┌──────────────────┐ ┌──────────────────────────┐ │ │ │ │ │ Table Lock (LOCK │ │ Record Lock │ │ │ │ │ │ TABLES ... ) │ │ - locks single index rec │ │ │ │ │ ├──────────────────┤ ├──────────────────────────┤ │ │ │ │ │ Intention Lock │ │ Gap Lock │ │ │ │ │ │ - IS (Intent │ │ - locks gap before rec │ │ │ │ │ │ Shared) │ ├──────────────────────────┤ │ │ │ │ │ - IX (Intent │ │ Next-Key Lock │ │ │ │ │ │ Exclusive) │ │ = Record + Gap │ │ │ │ │ ├──────────────────┤ │ (default in RR) │ │ │ │ │ │ Auto-Inc Lock │ ├──────────────────────────┤ │ │ │ │ │ (AUTO_INCREMENT) │ │ Insert Intention Lock │ │ │ │ │ └──────────────────┘ │ - special gap lock for │ │ │ │ │ │ concurrent INSERTs │ │ │ │ │ └──────────────────────────┘ │ │ │ └──────────────────────────────────────────────────────────────────────┘ │ │ │ │ ┌─── By Mode ─────────────────────────────────────────────────────────┐ │ │ │ │ │ │ │ Shared (S) Exclusive (X) │ │ │ │ ┌──────────────────┐ ┌──────────────────────────┐ │ │ │ │ │ SELECT ... FOR │ │ SELECT ... FOR UPDATE │ │ │ │ │ │ SHARE / LOCK IN │ │ UPDATE ... │ │ │ │ │ │ SHARE MODE │ │ DELETE ... │ │ │ │ │ │ Multiple holders │ │ Single holder only │ │ │ │ │ │ allowed │ │ │ │ │ │ │ └──────────────────┘ └──────────────────────────┘ │ │ │ └──────────────────────────────────────────────────────────────────────┘ │ └─────────────────────────────────────────────────────────────────────────────┘

源码参考: storage/innobase/include/lock0types.h, storage/innobase/lock/lock0lock.cc

1.2 表锁 vs 行锁 vs 意向锁

表锁的粒度最大，整张表被锁定。行锁的粒度最小，只锁定索引中的特定记录。意向锁（Intention Lock）是表级别的轻量锁，用于协调表锁与行锁之间的关系。

锁类型	粒度	获取方式	用途
`LOCK TABLES ... READ`	表级 S	显式 LOCK TABLES	阻止写操作，允许读
`LOCK TABLES ... WRITE`	表级 X	显式 LOCK TABLES	阻止一切读写
IS (Intention Shared)	表级	获取行级 S 锁之前自动获取	声明事务意图对某些行加共享锁
IX (Intention Exclusive)	表级	获取行级 X 锁之前自动获取	声明事务意图对某些行加排他锁
AUTO-INC	表级	INSERT	保证 AUTO_INCREMENT 值的唯一递增

1.3 S/X 锁兼容矩阵（表级）

意向锁之间互不冲突（因为它们只是"声明意图"），但意向锁与表级 S/X 锁之间存在冲突关系：

已持有 ↓ \ 请求 →	X	IX	S	IS
X	N	N	N	N
IX	N	Y	N	Y
S	N	N	Y	Y
IS	N	Y	Y	Y

关键洞察: IX 与 IX 兼容，这就是为什么多个事务可以同时对同一张表的不同行加排他行锁。如果没有意向锁，MySQL 在执行 LOCK TABLES ... WRITE 时需要逐行检查是否存在行锁，意向锁让这个检查变成了 O(1)。

1.4 AUTO-INC 锁与 innodb_autoinc_lock_mode

AUTO-INC 锁是一种特殊的表级锁，用于保证 AUTO_INCREMENT 列值的唯一递增。innodb_autoinc_lock_mode 控制其行为：

模式	值	行为	适用场景
Traditional	0	每条 INSERT 持有表级 AUTO-INC 锁直到语句结束	SBR 复制需要连续 ID
Consecutive	1 (8.0 前默认)	Simple INSERT 用轻量互斥锁；Bulk INSERT 用 AUTO-INC 锁	SBR 与并发 INSERT 折中
Interleaved	2 (8.0+ 默认)	所有 INSERT 都用轻量互斥锁，ID 可能不连续	RBR 复制，最高并发

生产建议: MySQL 8.0 将默认值从 1 改为 2 并同时将默认 binlog 格式改为 ROW。如果你仍使用 STATEMENT 格式的 binlog 复制，必须将 innodb_autoinc_lock_mode 设为 0 或 1，否则从库的 AUTO_INCREMENT 值可能与主库不一致。

2. 行锁深入解析

InnoDB 行锁是加在索引记录上的，而不是加在数据行本身。如果一个表没有任何索引，InnoDB 会使用隐式的聚簇索引（ROW_ID），这会导致全表锁的效果。理解这一点对分析加锁行为至关重要。

2.1 Record Lock（记录锁）

Record Lock 锁定索引中的单条记录。当你使用唯一索引的等值查询精确匹配到一条记录时，InnoDB 只加 Record Lock 而不加 Gap Lock。

-- Setup CREATE TABLE t ( id INT PRIMARY KEY, name VARCHAR(50), age INT, INDEX idx_age (age) ) ENGINE=InnoDB; INSERT INTO t VALUES (1,'Alice',25),(5,'Bob',30),(10,'Carol',35),(15,'Dave',40); -- Transaction A: exact match on PK → Record Lock only BEGIN; SELECT * FROM t WHERE id = 5 FOR UPDATE; -- Locks: X,REC_NOT_GAP on index `PRIMARY`, record (id=5) -- Transaction B: can still insert id=3 or id=7 (no gap lock) BEGIN; INSERT INTO t VALUES (3,'Eve',28); -- ✅ succeeds INSERT INTO t VALUES (7,'Frank',32); -- ✅ succeeds

-- Index structure diagram: Record Lock on id=5 Primary Index B+ Tree Leaf Page ┌────────┬────────┬────────┬────────┬──────────┐ │ id=1 │ id=5 │ id=10 │ id=15 │ supremum │ │ │ 🔒 X │ │ │ │ │ │REC_NOT │ │ │ │ │ │ _GAP │ │ │ │ └────────┴────────┴────────┴────────┴──────────┘

通过 performance_schema.data_locks 可以观察到精确的锁信息：

源码参考: storage/innobase/lock/lock0lock.cc — lock_rec_lock()

2.2 Gap Lock（间隙锁）

Gap Lock 锁定索引记录之间的间隙（gap），防止其他事务向间隙中插入新记录。Gap Lock 只在 REPEATABLE READ（RR）隔离级别下使用，在 READ COMMITTED（RC）下不使用。

核心特性: Gap Lock 之间不冲突！两个事务可以同时持有同一个间隙上的 Gap Lock（无论 S 还是 X 模式）。Gap Lock 唯一的作用是阻止 INSERT。这也是为什么 Gap Lock 容易导致死锁的原因之一。

-- Transaction A: query on non-existent key → Gap Lock BEGIN; SELECT * FROM t WHERE id = 7 FOR UPDATE; -- id=7 doesn't exist. InnoDB locks the gap (5, 10) -- Locks: X,GAP on index `PRIMARY`, record (id=10) — means gap before 10 -- Transaction B: trying to insert into the locked gap BEGIN; INSERT INTO t VALUES (6,'Eve',28); -- ❌ BLOCKED (waiting for gap lock) INSERT INTO t VALUES (8,'Frank',32); -- ❌ BLOCKED INSERT INTO t VALUES (9,'Grace',29); -- ❌ BLOCKED INSERT INTO t VALUES (3,'Hank',27); -- ✅ succeeds (outside gap) INSERT INTO t VALUES (11,'Ivy',31); -- ✅ succeeds (outside gap)

-- Gap Lock diagram Primary Index B+ Tree Leaf Page ┌────────┬───────────────────┬────────┬────────┬──────────┐ │ id=1 │ GAP (5,10) │ id=10 │ id=15 │ supremum │ │ │ 🔒 X,GAP │ │ │ │ │ │ blocks INSERT │ │ │ │ │ │ of id=6,7,8,9 │ │ │ │ └────────┴───────────────────┴────────┴────────┴──────────┘ ↑ ↑ gap starts gap lock is attached after id=5 to record id=10

源码参考: storage/innobase/lock/lock0lock.cc — lock_rec_lock(), LOCK_GAP flag

2.3 Next-Key Lock（临键锁）

Next-Key Lock = Record Lock + Gap Lock，即锁定一条记录以及该记录之前的间隙。Next-Key Lock 是 InnoDB 在 RR 隔离级别下的默认行锁类型，用于防止幻读（Phantom Reads）。

-- Transaction A: range query on secondary index → Next-Key Locks BEGIN; SELECT * FROM t WHERE age >= 30 AND age < 40 FOR UPDATE; -- InnoDB scans idx_age and locks: -- 1. Next-Key Lock on idx_age record (age=30, id=5): locks (25,30] -- 2. Next-Key Lock on idx_age record (age=35, id=10): locks (30,35] -- 3. Gap Lock on idx_age record (age=40, id=15): locks (35,40) — gap only -- 4. Record Lock (X,REC_NOT_GAP) on PRIMARY id=5 and id=10 -- Net effect: age range [30, 40) is fully locked, no phantom possible

-- Next-Key Lock diagram on secondary index idx_age idx_age B+ Tree Leaf Page ┌──────────────┬──────────────┬──────────────┬──────────────┬──────────┐ │ age=25,id=1 │ age=30,id=5 │ age=35,id=10 │ age=40,id=15 │ supremum │ │ │ 🔒 X,NK │ 🔒 X,NK │ 🔒 X,GAP │ │ │ │ (25,30] │ (30,35] │ (35,40) │ │ └──────────────┴──────────────┴──────────────┴──────────────┴──────────┘ PRIMARY B+ Tree (record locks for matched rows) ┌────────┬────────┬────────┬────────┬──────────┐ │ id=1 │ id=5 │ id=10 │ id=15 │ supremum │ │ │ 🔒 X │ 🔒 X │ │ │ │ │REC_NOT │REC_NOT │ │ │ │ │ _GAP │ _GAP │ │ │ └────────┴────────┴────────┴────────┴──────────┘

源码参考: storage/innobase/lock/lock0lock.cc — LOCK_ORDINARY (next-key lock is the default, no special flag)

2.4 Insert Intention Lock（插入意向锁）

Insert Intention Lock 是一种特殊的 Gap Lock，在 INSERT 操作时获取。它表示事务打算在间隙中的某个特定位置插入一条记录。多个事务如果要插入同一间隙中的不同位置，彼此的 Insert Intention Lock 不会冲突，从而允许并发插入。

-- Existing records: id = 1, 5, 10, 15 -- Gap between id=5 and id=10: (5, 10) -- Transaction A: BEGIN; INSERT INTO t VALUES (6,'Eve',28); -- Acquires: Insert Intention Lock on gap (5,10), then Record Lock on id=6 -- Transaction B (concurrent): BEGIN; INSERT INTO t VALUES (8,'Frank',32); -- Acquires: Insert Intention Lock on gap (5,10), then Record Lock on id=8 -- ✅ Both succeed concurrently — Insert Intention Locks don't conflict!

但是: 如果另一个事务已经在该间隙上持有 Gap Lock（非 Insert Intention 类型），则 Insert Intention Lock 会被阻塞。这是 Gap Lock + INSERT 导致死锁的根本原因。

-- Deadlock scenario: Gap Lock vs Insert Intention Lock -- TX A: BEGIN; SELECT * FROM t WHERE id = 7 FOR UPDATE; -- Gap Lock on (5,10) -- TX B: BEGIN; SELECT * FROM t WHERE id = 8 FOR UPDATE; -- Gap Lock on (5,10) — compatible! -- TX A: INSERT INTO t VALUES (7,'Eve',28); -- Needs Insert Intention Lock on (5,10), blocked by TX B's Gap Lock → WAIT -- TX B: INSERT INTO t VALUES (8,'Frank',32); -- Needs Insert Intention Lock on (5,10), blocked by TX A's Gap Lock → DEADLOCK!

源码参考: storage/innobase/lock/lock0lock.cc — LOCK_INSERT_INTENTION flag

2.5 行锁类型总结

锁类型	LOCK_MODE 显示	锁定范围	主要用途
Record Lock	`X,REC_NOT_GAP` / `S,REC_NOT_GAP`	单条索引记录	精确匹配唯一索引
Gap Lock	`X,GAP` / `S,GAP`	两条记录之间的间隙	防止幻读（阻止 INSERT）
Next-Key Lock	`X` / `S`	记录 + 前面的间隙 (gap, record]	RR 下默认的行锁类型
Insert Intention Lock	`X,INSERT_INTENTION`	间隙中的一个点	允许并发 INSERT 到同一间隙

3. 行锁兼容矩阵

行锁的兼容性不仅取决于模式（S/X），还取决于锁的类型（Record/Gap/Next-Key/Insert Intention）。这是 InnoDB 锁系统中最容易混淆的部分：

3.1 完整兼容矩阵

已持有 ↓ \ 请求 →	Record S	Record X	Gap S	Gap X	Next-Key S	Next-Key X	Insert Intention
Record S	Y	N	Y	Y	Y	N	Y
Record X	N	N	Y	Y	N	N	Y
Gap S	Y	Y	Y	Y	Y	Y	N
Gap X	Y	Y	Y	Y	Y	Y	N
Next-Key S	Y	N	Y	Y	Y	N	N
Next-Key X	N	N	Y	Y	N	N	N
Insert Intention	Y	Y	Y	Y	Y	Y	Y

核心规则:

Gap Lock 之间永远兼容（Gap S/X vs Gap S/X 都是 Y）
Gap Lock（以及 Next-Key Lock 的 Gap 部分）阻塞 Insert Intention Lock
Insert Intention Lock 不阻塞任何锁（包括其他 Insert Intention Lock）
Record Lock 的 S/X 冲突规则与普通读写锁一致

3.2 锁兼容性判断源码逻辑

InnoDB 在 lock_rec_has_to_wait() 函数中判断两把锁是否冲突。简化后的逻辑如下：

// Simplified from storage/innobase/lock/lock0lock.cc static bool lock_rec_has_to_wait( const lock_t *lock1, // requesting lock const lock_t *lock2, // existing lock ulint heap_no) { // Rule 1: Same transaction never waits for itself if (lock1->trx == lock2->trx) return false; // Rule 2: If modes are compatible (S+S), no wait if (lock_mode_compatible(lock1->mode, lock2->mode)) return false; // Rule 3: Gap lock never blocks anything except Insert Intention if (lock_rec_get_gap(lock2)) { // Existing lock is a gap-only lock if (!lock_rec_get_insert_intention(lock1)) return false; // Insert Intention vs Gap → must wait } // Rule 4: Requesting a gap lock? Never wait if (lock_rec_get_gap(lock1)) return false; // Rule 5: Insert Intention never has to wait for Insert Intention if (lock_rec_get_insert_intention(lock1) && lock_rec_get_insert_intention(lock2)) return false; // Rule 6: Record part of requesting lock vs record part of existing lock if (lock_rec_get_rec_not_gap(lock1)) { // Only the record component matters if (lock_rec_get_gap(lock2)) return false; } return true; // must wait }

源码参考: storage/innobase/lock/lock0lock.cc — lock_rec_has_to_wait()

4. 加锁场景分析（15+ 场景）

以下所有示例均基于这张表（REPEATABLE READ 隔离级别，除非特别说明）：

CREATE TABLE orders ( id INT PRIMARY KEY, user_id INT, amount DECIMAL(10,2), status VARCHAR(20), INDEX idx_user (user_id), INDEX idx_status (status) ) ENGINE=InnoDB; INSERT INTO orders VALUES (1, 100, 50.00, 'paid'), (5, 100, 80.00, 'paid'), (10, 200, 120.00, 'pending'), (15, 200, 200.00, 'paid'), (20, 300, 90.00, 'shipped'), (25, 300, 150.00, 'paid');

场景 1: SELECT ... FOR UPDATE — 主键等值

SELECT * FROM orders WHERE id = 10 FOR UPDATE; -- Index used: PRIMARY -- Locks acquired: -- TABLE: IX -- PRIMARY id=10: X,REC_NOT_GAP (record lock only, no gap) -- Why no gap lock? PK is unique, exact match → record lock suffices

场景 2: SELECT ... FOR UPDATE — 主键不存在

SELECT * FROM orders WHERE id = 12 FOR UPDATE; -- Index used: PRIMARY -- Locks acquired: -- TABLE: IX -- PRIMARY id=15: X,GAP (gap lock on (10, 15)) -- Why? id=12 doesn't exist, InnoDB locks the gap to prevent phantom

场景 3: SELECT ... FOR UPDATE — 主键范围

SELECT * FROM orders WHERE id BETWEEN 5 AND 15 FOR UPDATE; -- Index used: PRIMARY (range scan) -- Locks acquired: -- TABLE: IX -- PRIMARY id=5: X (next-key lock, locks (1,5]) -- PRIMARY id=10: X (next-key lock, locks (5,10]) -- PRIMARY id=15: X (next-key lock, locks (10,15]) -- PRIMARY id=20: X,GAP (gap lock, locks (15,20) — scan stop point) -- Total locked range: (1, 20) — wider than you might expect!

场景 4: SELECT ... FOR SHARE（LOCK IN SHARE MODE）

SELECT * FROM orders WHERE id = 10 FOR SHARE; -- MySQL 8.0+ syntax; equivalent to: LOCK IN SHARE MODE -- Locks acquired: -- TABLE: IS -- PRIMARY id=10: S,REC_NOT_GAP (shared record lock) -- Another tx can also FOR SHARE on id=10 → both succeed -- But FOR UPDATE on id=10 → blocked until S lock released

场景 5: SELECT ... FOR UPDATE — 非唯一索引等值

SELECT * FROM orders WHERE user_id = 200 FOR UPDATE; -- Index used: idx_user -- Locks acquired: -- TABLE: IX -- idx_user (user_id=200, id=10): X (next-key, locks gap before + record) -- idx_user (user_id=200, id=15): X (next-key) -- idx_user (user_id=300, id=20): X,GAP (gap lock — scan stop) -- PRIMARY id=10: X,REC_NOT_GAP -- PRIMARY id=15: X,REC_NOT_GAP -- Why next-key on non-unique? To prevent phantom: INSERT user_id=200

场景 6: UPDATE — 主键等值

UPDATE orders SET amount = 100.00 WHERE id = 10; -- Index used: PRIMARY -- Locks acquired: -- TABLE: IX -- PRIMARY id=10: X,REC_NOT_GAP -- Same as FOR UPDATE with PK equality — record lock only

场景 7: UPDATE — 非唯一索引范围

UPDATE orders SET amount = amount * 1.1 WHERE user_id >= 200; -- Index used: idx_user (range scan from user_id=200 to supremum) -- Locks acquired: -- TABLE: IX -- idx_user (user_id=200, id=10): X (next-key) -- idx_user (user_id=200, id=15): X (next-key) -- idx_user (user_id=300, id=20): X (next-key) -- idx_user (user_id=300, id=25): X (next-key) -- idx_user supremum: X (next-key — locks to +infinity) -- PRIMARY id=10,15,20,25: X,REC_NOT_GAP (each) -- WARNING: supremum lock blocks ALL inserts with user_id ≥ 200

场景 8: UPDATE — 修改索引列

UPDATE orders SET user_id = 400 WHERE id = 10; -- Locks acquired: -- TABLE: IX -- PRIMARY id=10: X,REC_NOT_GAP -- Note: When updating a secondary index column, InnoDB also needs to -- delete the old secondary index entry and insert a new one. -- This involves additional implicit locking on idx_user.

场景 9: DELETE — 主键等值

DELETE FROM orders WHERE id = 10; -- Locks acquired: -- TABLE: IX -- PRIMARY id=10: X,REC_NOT_GAP -- Same as UPDATE on PK equality -- The record is delete-marked, purge thread removes it later

场景 10: DELETE — 非唯一索引

DELETE FROM orders WHERE user_id = 200; -- Index used: idx_user -- Locks (same pattern as SELECT ... FOR UPDATE on non-unique index): -- idx_user (user_id=200, id=10): X (next-key) -- idx_user (user_id=200, id=15): X (next-key) -- idx_user (user_id=300, id=20): X,GAP -- PRIMARY id=10: X,REC_NOT_GAP -- PRIMARY id=15: X,REC_NOT_GAP

场景 11: DELETE — 无索引（全表扫描）

DELETE FROM orders WHERE amount > 100.00; -- No index on amount → full table scan on PRIMARY -- Locks acquired (TERRIFYING): -- TABLE: IX -- PRIMARY: next-key lock on EVERY record + supremum -- Effectively locks the entire table! -- -- Why? Without an index, InnoDB must scan all rows and lock each one. -- It cannot release locks on non-matching rows mid-transaction.

严重警告: 没有合适索引的 UPDATE/DELETE 会锁住整张表！这是生产环境中最常见的"锁全表"事故原因。始终确保 WHERE 条件能命中索引。

场景 12: INSERT — 基本加锁

INSERT INTO orders VALUES (12, 200, 75.00, 'pending'); -- Locks acquired (briefly): -- TABLE: IX -- Insert Intention Lock on gap (10, 15) of PRIMARY -- PRIMARY id=12: X,REC_NOT_GAP (held until commit) -- -- The insert intention lock is released as soon as the record lock -- is placed on the newly inserted row.

场景 13: INSERT — 唯一键冲突

-- TX A: BEGIN; INSERT INTO orders VALUES (10, 500, 99.00, 'new'); -- ERROR 1062: Duplicate entry '10' for key 'PRIMARY' -- BUT: TX A now holds S,REC_NOT_GAP on PRIMARY id=10 (shared lock!) -- Why S lock on failure? To prevent the conflicting row from being -- deleted before the failing transaction decides what to do.

场景 14: INSERT ... ON DUPLICATE KEY UPDATE

INSERT INTO orders VALUES (10, 200, 130.00, 'paid') ON DUPLICATE KEY UPDATE amount = VALUES(amount); -- Locks acquired: -- TABLE: IX -- PRIMARY id=10: X,REC_NOT_GAP (exclusive — because it's an UPDATE) -- -- Difference from plain INSERT: -- Plain INSERT failure → S lock (shared) -- ON DUPLICATE KEY UPDATE → X lock (exclusive) -- This distinction matters for deadlock analysis!

场景 15: REPLACE INTO

REPLACE INTO orders VALUES (10, 200, 130.00, 'paid'); -- If id=10 exists: -- Equivalent to DELETE + INSERT -- PRIMARY id=10: X (next-key lock — includes gap!) -- The next-key lock (vs record lock) is because REPLACE may need -- to prevent phantom on secondary unique indexes. -- If id=10 doesn't exist: -- Same as plain INSERT (insert intention + record lock)

场景 16: SELECT ... FOR UPDATE with LIMIT

SELECT * FROM orders WHERE user_id = 100 FOR UPDATE LIMIT 1; -- Index used: idx_user -- Locks acquired: -- idx_user (user_id=100, id=1): X (next-key) -- PRIMARY id=1: X,REC_NOT_GAP -- -- Stops after first match! Fewer locks than without LIMIT. -- LIMIT can significantly reduce lock contention.

场景 17: 同一事务内多条语句的锁累积

BEGIN; SELECT * FROM orders WHERE id = 5 FOR UPDATE; -- Lock 1: X on PK=5 SELECT * FROM orders WHERE id = 15 FOR UPDATE; -- Lock 2: X on PK=15 UPDATE orders SET amount = 0 WHERE user_id = 300; -- Lock 3: X on user_id=300 range -- ALL locks held simultaneously until COMMIT/ROLLBACK! -- InnoDB does not release locks within a transaction (2PL: two-phase locking) -- This is the fundamental reason why long transactions cause lock contention.

场景 18: 子查询中的加锁

-- Subquery with FOR UPDATE — be careful! SELECT * FROM orders WHERE user_id IN ( SELECT user_id FROM vip_users WHERE level = 'gold' ) FOR UPDATE; -- The FOR UPDATE applies to the outer query (orders), not the subquery -- vip_users: only snapshot read (no locks) -- orders: X locks on all matching rows -- -- WARNING: If MySQL transforms this into a JOIN internally, -- locking behavior may differ from what you expect. -- Check EXPLAIN to verify the actual execution plan.

场景 19: 全表 COUNT 的加锁行为

-- Plain COUNT — no locks (snapshot read) SELECT COUNT(*) FROM orders; -- Uses MVCC snapshot, no row locks at all -- COUNT with FOR UPDATE — locks every row! SELECT COUNT(*) FROM orders FOR UPDATE; -- Scans entire PRIMARY index -- Next-Key Lock on every record + supremum -- Effectively table-wide lock. Almost never what you want!

场景补充: RC 与 RR 对比

REPEATABLE READ (RR)

使用 Next-Key Lock（Record + Gap）
范围查询锁定间隙
防止幻读
锁范围更大，并发度更低

READ COMMITTED (RC)

只使用 Record Lock（无 Gap Lock）
不锁定间隙
允许幻读
锁范围最小，并发度最高
扫描到不匹配的行时立即释放锁（Semi-consistent read）

5. MVCC 与锁的交互

5.1 一致性读（快照读）vs 锁定读

InnoDB 支持两种读取方式，它们对锁的影响完全不同：

读取方式	SQL	加锁	数据来源
一致性读（快照读）	`SELECT ...` (普通 SELECT)	不加任何锁	通过 Undo Log 构建的 ReadView 快照
锁定读（当前读）	`SELECT ... FOR UPDATE/SHARE`	加行锁	最新已提交版本（非快照）
隐式锁定读	`UPDATE / DELETE / INSERT`	加行锁	最新已提交版本

关键区别: 在 RR 下，普通 SELECT 看到的是事务开始时的快照，而 SELECT ... FOR UPDATE 看到的是最新数据。这两者在同一事务中可以返回不同结果！这不是 Bug，而是 MVCC 的设计。

-- Demonstrating snapshot read vs locking read difference -- TX A: TX B: BEGIN; BEGIN; SELECT * FROM orders WHERE id=10; -- sees: amount=120.00 (snapshot) UPDATE orders SET amount=999 WHERE id=10; COMMIT; SELECT * FROM orders WHERE id=10; -- still sees: amount=120.00 (same snapshot) SELECT * FROM orders WHERE id=10 FOR UPDATE; -- sees: amount=999.00 (current read, latest committed)

5.2 为什么 RR 使用 Gap Lock 而 RC 不使用

RR 要保证可重复读和防止幻读，因此需要 Gap Lock 阻止其他事务在已扫描的范围内插入新行。而 RC 只关心"读已提交"的数据，每次 SELECT 都获取新快照，天然允许幻读，所以不需要 Gap Lock。

-- Phantom row problem (without gap locks, i.e. under RC) -- TX A: TX B: BEGIN; SELECT * FROM orders WHERE user_id = 200 FOR UPDATE; -- Returns: id=10, id=15 BEGIN; INSERT INTO orders VALUES (12, 200, 55.00, 'new'); COMMIT; SELECT * FROM orders WHERE user_id = 200 FOR UPDATE; -- Under RC: Returns id=10, id=12, id=15 — PHANTOM ROW (id=12)! -- Under RR: TX B's INSERT would be BLOCKED by gap lock → no phantom

5.3 Semi-Consistent Read（RC 下的特殊优化）

在 RC 隔离级别下，UPDATE 语句会进行"半一致性读"：如果扫描到的行已经被其他事务锁定且不匹配 WHERE 条件，InnoDB 会读取该行的最新已提交版本来评估条件。如果仍不匹配，立即释放该行上的锁。这大大减少了 RC 下的锁等待。

-- Semi-consistent read example (RC isolation level) -- Table has: id=1 (status='paid'), id=5 (status='paid'), id=10 (status='pending') -- TX A: BEGIN; SELECT * FROM orders WHERE id = 5 FOR UPDATE; -- X lock on id=5 -- TX B (RC): BEGIN; UPDATE orders SET amount = 0 WHERE status = 'pending'; -- Full scan: encounters id=5 locked by TX A -- Semi-consistent: reads latest committed version of id=5 -- status='paid' ≠ 'pending' → skips, does NOT wait for TX A's lock -- Continues to id=10 (status='pending') → locks and updates -- Without semi-consistent read, TX B would block on id=5 waiting for TX A

6. 死锁深入解析

6.1 什么导致死锁

死锁是两个或多个事务互相等待对方持有的锁，形成循环等待，没有任何事务能继续执行。InnoDB 会自动检测并回滚代价最小的事务来打破循环。

-- Classic AB-BA deadlock TX A TX B ──────────────────── ──────────────────── BEGIN; BEGIN; UPDATE orders SET amount=0 UPDATE orders SET amount=0 WHERE id = 5; WHERE id = 10; -- holds X lock on PK=5 -- holds X lock on PK=10 UPDATE orders SET amount=0 WHERE id = 10; -- WAIT: needs X lock on PK=10 -- (held by TX B) UPDATE orders SET amount=0 WHERE id = 5; -- WAIT: needs X lock on PK=5 -- (held by TX A) ┌────────┐ ┌────────┐ │ TX A │ waits → │ TX B │ │ │ ← waits │ │ └────────┘ └────────┘ DEADLOCK! Circular wait detected. InnoDB rolls back TX B (fewer undo log records) TX A proceeds, TX B gets: ERROR 1213 (40001): Deadlock found when trying to get lock

6.2 死锁检测算法（Wait-for Graph）

InnoDB 使用等待图（Wait-for Graph）算法检测死锁。每当一个事务需要等待锁时，InnoDB 就在等待图中添加一条边，然后检查是否产生了环。如果有环，就发生了死锁。

-- Wait-for Graph visualization -- -- Each node = a transaction -- Each edge = "waits for lock held by" -- -- No deadlock (linear chain): Deadlock (cycle): -- -- TX A → TX B → TX C TX A → TX B -- (A waits B waits C) ↑ ↓ -- No cycle → just waiting TX D ← TX C -- Cycle detected → deadlock! -- -- Algorithm: depth-first search (DFS) on the wait-for graph -- Complexity: O(V + E) where V = transactions, E = wait edges -- -- Victim selection: InnoDB picks the transaction with the fewest -- undo log records (least work to roll back) as the victim.

参数	默认值	说明
`innodb_deadlock_detect`	ON	是否启用主动死锁检测。关闭后依赖超时来解决死锁。
`innodb_lock_wait_timeout`	50 (秒)	等待行锁的最大时间，超时后报错 `ERROR 1205`。

6.3 解读 SHOW ENGINE INNODB STATUS 死锁信息

SHOW ENGINE INNODB STATUS\G ------------------------ LATEST DETECTED DEADLOCK ------------------------ 2024-01-15 14:23:07 140234567890432 *** (1) TRANSACTION: ← TX A info TRANSACTION 421937285, ACTIVE 3 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s) MySQL thread id 12, OS thread handle 140234567890432, query id 5678 UPDATE orders SET amount=0 WHERE id = 10 ← TX A's blocked query *** (1) HOLDS THE LOCK(S): ← What TX A already holds RECORD LOCKS space id 58 page no 4 n bits 72 index PRIMARY of table `test`.`orders` trx id 421937285 lock_mode X locks rec but not gap Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; ... 0: len 4; hex 80000005; asc ;; ← id=5 (X record lock) *** (1) WAITING FOR THIS LOCK TO BE GRANTED: ← What TX A is waiting for RECORD LOCKS space id 58 page no 4 n bits 72 index PRIMARY of table `test`.`orders` trx id 421937285 lock_mode X locks rec but not gap waiting Record lock, heap no 4 PHYSICAL RECORD: n_fields 6; ... 0: len 4; hex 8000000a; asc ;; ← id=10 (waiting for X) *** (2) TRANSACTION: ← TX B info TRANSACTION 421937286, ACTIVE 2 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s) MySQL thread id 15, OS thread handle 140234567890433, query id 5679 UPDATE orders SET amount=0 WHERE id = 5 ← TX B's blocked query *** (2) HOLDS THE LOCK(S): ← What TX B already holds RECORD LOCKS space id 58 page no 4 n bits 72 index PRIMARY of table `test`.`orders` trx id 421937286 lock_mode X locks rec but not gap Record lock, heap no 4 PHYSICAL RECORD: n_fields 6; ... 0: len 4; hex 8000000a; asc ;; ← id=10 (X record lock) *** (2) WAITING FOR THIS LOCK TO BE GRANTED: ← What TX B is waiting for RECORD LOCKS space id 58 page no 4 n bits 72 index PRIMARY of table `test`.`orders` trx id 421937286 lock_mode X locks rec but not gap waiting Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; ... 0: len 4; hex 80000005; asc ;; ← id=5 (waiting for X) *** WE ROLL BACK TRANSACTION (2) ← TX B is the victim

解读要点：

hex 80000005 → 0x80000005 - 0x80000000 = 5，即 id=5。InnoDB 存储有符号整数时加了 0x80000000 偏移。
lock_mode X locks rec but not gap = Record Lock（X, REC_NOT_GAP）
lock_mode X（不带 but not gap）= Next-Key Lock
lock_mode X locks gap before rec = Gap Lock
lock_mode X insert intention = Insert Intention Lock

6.4 常见死锁模式

模式 A: AB-BA 顺序颠倒

两个事务以相反顺序访问同一组资源。这是最经典的死锁模式，解决方案是确保所有事务以相同顺序访问资源。

-- TX A: UPDATE WHERE id=5, then UPDATE WHERE id=10 -- TX B: UPDATE WHERE id=10, then UPDATE WHERE id=5 -- Fix: both should access id=5 first, then id=10 (ascending order)

模式 B: Gap Lock + INSERT

这是最隐蔽的死锁模式。两个事务都获取了同一个间隙上的 Gap Lock（Gap Lock 之间兼容），然后都尝试 INSERT 到该间隙中，互相被对方的 Gap Lock 阻塞。

-- Records: id = 1, 5, 10, 15 -- TX A: BEGIN; SELECT * FROM t WHERE id = 7 FOR UPDATE; -- Gap Lock on (5,10) -- TX B: BEGIN; SELECT * FROM t WHERE id = 8 FOR UPDATE; -- Gap Lock on (5,10) — COMPATIBLE! -- TX A: INSERT INTO t VALUES (7, 'x', 0); -- Insert Intention on (5,10) → BLOCKED by TX B's Gap -- TX B: INSERT INTO t VALUES (8, 'y', 0); -- Insert Intention on (5,10) → BLOCKED by TX A's Gap -- 💀 DEADLOCK

模式 C: 二级索引 + 聚簇索引

一个事务通过二级索引加锁（先锁二级索引，再锁聚簇索引），另一个事务直接通过聚簇索引加锁。加锁顺序不同导致死锁。

-- TX A: uses secondary index idx_user BEGIN; UPDATE orders SET amount = 0 WHERE user_id = 200; -- Lock order: idx_user(200,id=10) → PRIMARY(id=10) → idx_user(200,id=15) → PRIMARY(id=15) -- TX B: uses primary index directly BEGIN; UPDATE orders SET user_id = 999 WHERE id = 15; -- Lock order: PRIMARY(id=15) → idx_user(200,id=15) (old entry) → idx_user(999,id=15) (new) -- If TX A has locked idx_user(200,id=10)+PRIMARY(10) and is waiting for PRIMARY(15), -- while TX B has locked PRIMARY(15) and is waiting for idx_user(200,id=15): -- 💀 DEADLOCK

模式 D: 批量 INSERT 死锁

多个事务同时批量插入有唯一约束的记录，唯一键冲突产生的 S 锁与后续 INSERT 的 X 锁形成交叉等待。

-- Table with unique index on (email) -- TX A: INSERT (email='[email protected]') → holds X on '[email protected]' -- TX B: INSERT (email='[email protected]') → duplicate → S lock wait on '[email protected]' -- TX C: INSERT (email='[email protected]') → duplicate → S lock wait on '[email protected]' -- TX A: ROLLBACK -- TX B and TX C both get S lock on '[email protected]', both try to INSERT → -- Both need X lock, both blocked by the other's S lock → DEADLOCK

源码参考: storage/innobase/lock/lock0lock.cc — DeadlockChecker::check_and_resolve()

7. 死锁模拟器（交互式）

选择一个预设的死锁场景，逐步执行两个事务的 SQL 语句，观察每一步获取的锁和最终的死锁发生过程。

选择场景:

Transaction A

持有的锁:

Transaction B

持有的锁:

点击"下一步"开始模拟

-- 执行日志将在此显示

8. 死锁预防策略

8.1 一致的加锁顺序

确保所有事务按相同的顺序访问资源（例如按主键升序），是预防 AB-BA 死锁最有效的方法。

-- BAD: inconsistent order -- TX A: lock id=5, then id=10 -- TX B: lock id=10, then id=5 -- GOOD: always ascending order -- TX A: lock id=5, then id=10 -- TX B: lock id=5, then id=10 (same order) -- Application code pattern: def transfer(from_id, to_id, amount): ids = sorted([from_id, to_id]) # always lock in ascending order with transaction(): lock_account(ids[0]) lock_account(ids[1]) do_transfer(from_id, to_id, amount)

8.2 保持事务简短

事务持有锁的时间越长，死锁的概率越高。原则：

将耗时操作（如外部 API 调用、文件 I/O）移到事务外
先做只读查询（快照读，不加锁），最后再做写操作
避免在事务中等待用户输入
考虑将大事务拆分为多个小事务

-- BAD: long transaction with external call BEGIN; SELECT * FROM orders WHERE id = 10 FOR UPDATE; -- ... call payment gateway (3 seconds) ... UPDATE orders SET status = 'paid' WHERE id = 10; COMMIT; -- GOOD: minimize lock hold time -- 1. Read data (no lock) SELECT * FROM orders WHERE id = 10; -- 2. Call payment gateway (no transaction) -- ... call payment gateway (3 seconds) ... -- 3. Quick write (minimal lock time) BEGIN; UPDATE orders SET status = 'paid' WHERE id = 10 AND status = 'pending'; COMMIT;

8.3 使用 RC 代替 RR

READ COMMITTED 不使用 Gap Lock，大幅减少死锁概率。如果你的业务不需要可重复读和防幻读（大部分 OLTP 场景），切换到 RC 是最简单的降锁方案。

-- Global setting SET GLOBAL transaction_isolation = 'READ-COMMITTED'; -- Or per-session SET SESSION transaction_isolation = 'READ-COMMITTED'; -- Or in my.cnf [mysqld] transaction_isolation = READ-COMMITTED

8.4 重试逻辑与指数退避

死锁是正常现象，应用层必须处理 ERROR 1213 并重试。建议使用指数退避加随机抖动：

import time, random def execute_with_retry(func, max_retries=3): for attempt in range(max_retries): try: return func() except MySQLDeadlockError: # ERROR 1213 if attempt == max_retries - 1: raise wait = (2 ** attempt) * 0.1 # 0.1s, 0.2s, 0.4s jitter = random.uniform(0, wait * 0.5) time.sleep(wait + jitter) log.warning(f"Deadlock retry {attempt+1}/{max_retries}")

// Go version func executeWithRetry(fn func() error, maxRetries int) error { for i := 0; i < maxRetries; i++ { err := fn() if err == nil { return nil } if !isDeadlockError(err) { return err } if i == maxRetries-1 { return fmt.Errorf("deadlock after %d retries: %w", maxRetries, err) } backoff := time.Duration(1<<uint(i)) * 100 * time.Millisecond jitter := time.Duration(rand.Int63n(int64(backoff / 2))) time.Sleep(backoff + jitter) } return nil } func isDeadlockError(err error) bool { var mysqlErr *mysql.MySQLError return errors.As(err, &mysqlErr) && mysqlErr.Number == 1213 }

8.5 Advisory Lock（应用级协调锁）

当业务逻辑需要更粗粒度的互斥时，可以使用 MySQL 的 Advisory Lock（咨询锁）：

-- Acquire named lock (timeout 10 seconds) SELECT GET_LOCK('order_process_123', 10); -- returns 1 if acquired -- Do complex multi-table operations safely BEGIN; UPDATE orders SET status = 'processing' WHERE id = 123; INSERT INTO order_log VALUES (123, 'status_change', NOW()); COMMIT; -- Release named lock SELECT RELEASE_LOCK('order_process_123'); -- Check if lock is free SELECT IS_FREE_LOCK('order_process_123'); -- 1=free, 0=in use

8.6 队列 + 合并写入模式

对于高并发更新同一行的场景，将更新请求放入队列，由单个消费者合并后一次性写入，彻底消除行级锁争用：

-- Instead of N concurrent transactions doing: UPDATE product SET stock = stock - 1 WHERE id = 100; -- N times, each in own TX -- Use a queue table: INSERT INTO stock_changes (product_id, delta, created_at) VALUES (100, -1, NOW()); -- Fast, no contention -- Background worker merges periodically: BEGIN; SELECT product_id, SUM(delta) AS total_delta FROM stock_changes WHERE processed = 0 GROUP BY product_id FOR UPDATE; UPDATE product p JOIN (SELECT product_id, SUM(delta) d FROM stock_changes WHERE processed=0 GROUP BY product_id) c ON p.id = c.product_id SET p.stock = p.stock + c.d; UPDATE stock_changes SET processed = 1 WHERE processed = 0; COMMIT;

9. 锁监控

9.1 performance_schema.data_locks（MySQL 8.0+）

这是 MySQL 8.0 引入的最重要的锁监控视图，替代了 5.7 的 information_schema.INNODB_LOCKS（只显示被等待的锁）。data_locks 显示所有已持有的锁。

9.2 performance_schema.data_lock_waits

显示当前正在等待的锁和阻塞它的锁：

SELECT r.ENGINE_TRANSACTION_ID AS waiting_trx, r.LOCK_MODE AS waiting_mode, r.LOCK_DATA AS waiting_data, b.ENGINE_TRANSACTION_ID AS blocking_trx, b.LOCK_MODE AS blocking_mode, b.LOCK_DATA AS blocking_data FROM performance_schema.data_lock_waits w JOIN performance_schema.data_locks r ON w.REQUESTING_ENGINE_LOCK_ID = r.ENGINE_LOCK_ID JOIN performance_schema.data_locks b ON w.BLOCKING_ENGINE_LOCK_ID = b.ENGINE_LOCK_ID;

9.3 sys.innodb_lock_waits

sys schema 提供了一个更友好的等待锁视图，直接显示阻塞和等待的 SQL 语句：

SELECT waiting_trx_id, waiting_pid, waiting_query, blocking_trx_id, blocking_pid, blocking_query, wait_age, locked_table, locked_index, locked_type FROM sys.innodb_lock_waits; -- Example output: +-----------------+-------------+----------------------------------+------------------+--------------+----------------------------------+----------+------------------+--------------+-------------+ | waiting_trx_id | waiting_pid | waiting_query | blocking_trx_id | blocking_pid | blocking_query | wait_age | locked_table | locked_index | locked_type | +-----------------+-------------+----------------------------------+------------------+--------------+----------------------------------+----------+------------------+--------------+-------------+ | 421937286 | 15 | UPDATE orders SET ... WHERE id=5 | 421937285 | 12 | NULL | 00:00:03 | `test`.`orders` | PRIMARY | RECORD | +-----------------+-------------+----------------------------------+------------------+--------------+----------------------------------+----------+------------------+--------------+-------------+ -- blocking_query=NULL means that transaction is idle (already executed its statement)

9.4 SHOW ENGINE INNODB STATUS — TRANSACTIONS 段

SHOW ENGINE INNODB STATUS\G ------------ TRANSACTIONS ------------ Trx id counter 421937290 Purge done for trx's n:o < 421937288 undo n:o < 0 state: running History list length 15 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 421937289, ACTIVE 8 sec 2 lock struct(s), heap size 1128, 1 row lock(s), undo log entries 1 MySQL thread id 12, OS thread handle 140234567890432, query id 5688 localhost root ↑ thread info ---TRANSACTION 421937290, ACTIVE 3 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 1128, 1 row lock(s) MySQL thread id 15, OS thread handle 140234567890433, query id 5690 localhost root updating UPDATE orders SET amount=0 WHERE id = 10 ← currently executing query ------- TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 58 page no 4 n bits 72 index PRIMARY of table `test`.`orders` trx id 421937290 lock_mode X locks rec but not gap waiting Record lock, heap no 4 PHYSICAL RECORD: ...

关键指标解读：

lock struct(s): 锁结构数量（每个表/索引的锁组一个结构）
row lock(s): 行锁数量（锁住的行记录数）
undo log entries: Undo 日志条目数（修改了多少行）
LOCK WAIT: 表示该事务正在等待锁
History list length: 未被 Purge 的 Undo 版本数（过大表示有长事务）

9.5 设置死锁日志

-- Print all deadlocks to the error log (not just the latest) SET GLOBAL innodb_print_all_deadlocks = ON; -- In my.cnf: [mysqld] innodb_print_all_deadlocks = 1 -- This logs every deadlock to the MySQL error log with the same -- detail as SHOW ENGINE INNODB STATUS, which only keeps the LATEST one. -- Essential for production: you need history, not just the last deadlock.

生产监控建议:

开启 innodb_print_all_deadlocks，配合日志收集系统（ELK/Loki）解析死锁日志
监控 Innodb_row_lock_waits 和 Innodb_row_lock_time_avg 状态变量
设置告警：当 Innodb_row_lock_time_avg > 1000ms 或 Innodb_deadlocks 增量异常时触发
定期检查 information_schema.INNODB_TRX 中 trx_started 超过 60 秒的事务

9.6 锁等待监控脚本

以下是一个实用的锁等待监控查询，可以作为定期执行的监控脚本：

-- Comprehensive lock wait report SELECT r.trx_id AS waiting_trx_id, r.trx_mysql_thread_id AS waiting_thread, r.trx_query AS waiting_query, TIMESTAMPDIFF(SECOND, r.trx_wait_started, NOW()) AS wait_seconds, b.trx_id AS blocking_trx_id, b.trx_mysql_thread_id AS blocking_thread, b.trx_query AS blocking_query, TIMESTAMPDIFF(SECOND, b.trx_started, NOW()) AS blocking_age_seconds, b.trx_rows_locked AS blocking_rows_locked, b.trx_rows_modified AS blocking_rows_modified, CONCAT(dl.OBJECT_SCHEMA, '.', dl.OBJECT_NAME) AS locked_table, dl.INDEX_NAME AS locked_index, dl.LOCK_MODE AS waiting_lock_mode, dl.LOCK_DATA AS lock_data FROM information_schema.INNODB_TRX r JOIN performance_schema.data_lock_waits w ON r.trx_id = w.REQUESTING_ENGINE_TRANSACTION_ID JOIN information_schema.INNODB_TRX b ON b.trx_id = w.BLOCKING_ENGINE_TRANSACTION_ID JOIN performance_schema.data_locks dl ON dl.ENGINE_LOCK_ID = w.REQUESTING_ENGINE_LOCK_ID WHERE r.trx_state = 'LOCK WAIT' ORDER BY wait_seconds DESC;

9.7 关键状态变量

-- Lock-related status variables SHOW GLOBAL STATUS LIKE 'Innodb_row_lock%'; +-------------------------------+-------+ | Variable_name | Value | +-------------------------------+-------+ | Innodb_row_lock_current_waits | 0 | ← currently waiting transactions | Innodb_row_lock_time | 15234 | ← total wait time (ms) since startup | Innodb_row_lock_time_avg | 127 | ← average wait time (ms) | Innodb_row_lock_time_max | 5023 | ← max single wait time (ms) | Innodb_row_lock_waits | 120 | ← total wait count since startup +-------------------------------+-------+ -- Deadlock count SHOW GLOBAL STATUS LIKE 'Innodb_deadlocks'; -- Monitor the rate of change, not absolute value -- Long-running transactions (potential lock holders) SELECT trx_id, trx_state, trx_started, TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS age_sec, trx_rows_locked, trx_rows_modified, trx_query, trx_mysql_thread_id FROM information_schema.INNODB_TRX WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 30 ORDER BY trx_started;

9.8 Grafana 监控面板建议

建议在 Grafana 中设置以下锁相关面板：

面板名称	指标源	告警阈值
行锁等待率	`rate(Innodb_row_lock_waits)`	> 50/s
平均锁等待时间	`Innodb_row_lock_time_avg`	> 500ms
死锁率	`rate(Innodb_deadlocks)`	> 1/min
当前锁等待数	`Innodb_row_lock_current_waits`	> 10
长事务数	`COUNT(INNODB_TRX WHERE age > 60s)`	> 0
History List 长度	`Innodb_history_list_length`	> 10000

10. 元数据锁（MDL）

10.1 什么是 MDL

Metadata Lock（MDL，元数据锁）是 MySQL 在 5.5.3 引入的锁机制，用于保护表结构不在查询执行期间被修改。当你执行任何 SQL 语句时，MySQL 会自动获取 MDL：

SELECT / DML → MDL Shared Read / Shared Write
DDL (ALTER TABLE, DROP TABLE 等) → MDL Exclusive

10.2 MDL 阻塞 ALTER TABLE 的典型场景

-- Timeline showing how MDL can cascade into a total block Time Session 1 Session 2 Session 3 ───── ────────────────── ────────────────── ────────────────── T1 BEGIN; SELECT * FROM t; -- holds MDL_SHARED_READ T2 ALTER TABLE t ADD COLUMN c INT; -- needs MDL_EXCLUSIVE -- BLOCKED by Session 1's MDL_SHARED_READ T3 SELECT * FROM t; -- needs MDL_SHARED_READ -- BLOCKED! Why? -- Session 2's pending -- MDL_EXCLUSIVE is ahead -- in the wait queue Result: Session 1's long transaction causes Session 2 (DDL) to wait, which in turn blocks ALL subsequent queries (Session 3, 4, 5, ...)! This can take down an entire service.

生产事故高频原因: 一个未提交的事务 + 一个 ALTER TABLE = 所有查询阻塞。这是 MySQL 线上故障最常见的原因之一。执行 DDL 前务必检查是否有长事务：SELECT * FROM information_schema.INNODB_TRX WHERE TIME_TO_SEC(TIMEDIFF(NOW(), trx_started)) > 10;

10.3 监控 MDL

-- Enable MDL instrumentation (MySQL 8.0+) UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES' WHERE NAME = 'wait/lock/metadata/sql/mdl'; -- View current MDL locks SELECT OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, LOCK_TYPE, LOCK_DURATION, LOCK_STATUS, OWNER_THREAD_ID, OWNER_EVENT_ID FROM performance_schema.metadata_locks WHERE OBJECT_TYPE = 'TABLE'; -- Find the blocking thread SELECT ml.OBJECT_SCHEMA, ml.OBJECT_NAME, ml.LOCK_TYPE, ml.LOCK_STATUS, t.PROCESSLIST_ID, t.PROCESSLIST_INFO AS query FROM performance_schema.metadata_locks ml JOIN performance_schema.threads t ON ml.OWNER_THREAD_ID = t.THREAD_ID WHERE ml.OBJECT_SCHEMA = 'your_db' AND ml.OBJECT_NAME = 'your_table';

10.4 Online DDL 与 MDL

InnoDB Online DDL 可以减少 DDL 对 DML 的影响，但仍然需要在开始和结束时短暂获取 MDL Exclusive：

-- Online DDL lifecycle: -- -- Phase 1: Preparation (brief MDL Exclusive) -- - Acquire MDL_EXCLUSIVE -- - Determine DDL algorithm (INPLACE, INSTANT, COPY) -- - Downgrade to MDL_SHARED_UPGRADABLE -- - Very short, usually milliseconds -- -- Phase 2: Execution (MDL_SHARED_UPGRADABLE — allows DML) -- - Rebuild table / add index in background -- - DML continues, changes logged to row log -- - Can be minutes/hours for large tables -- -- Phase 3: Commit (brief MDL Exclusive again) -- - Upgrade to MDL_EXCLUSIVE -- - Apply row log (accumulated DML changes) -- - Swap old/new table -- - Release MDL_EXCLUSIVE -- - This is where the blocking happens if there are long transactions! -- Recommended: set a lock wait timeout for DDL SET lock_wait_timeout = 5; -- give up after 5 seconds ALTER TABLE orders ADD COLUMN note TEXT, ALGORITHM=INPLACE, LOCK=NONE; -- If MDL Exclusive can't be acquired in 5s, the ALTER fails gracefully -- instead of blocking all traffic

DDL 操作	ALGORITHM	是否需要重建表	允许并发 DML
ADD COLUMN (末尾)	INSTANT (8.0.12+)	否	是
ADD INDEX	INPLACE	否	是
DROP COLUMN	INPLACE	是	是
CHANGE COLUMN TYPE	COPY	是	否
ADD FOREIGN KEY	INPLACE	否	是

11. 热点行 / 锁争用解决方案

当多个事务频繁更新同一行时（如库存扣减、计数器累加、账户余额变更），行锁争用成为严重的性能瓶颈。TPS 从数万降到数百甚至几十。以下是 5 种经过生产验证的解决方案。

方案 1: 分片计数器（Sharded Counters）

将一个热点行拆分成 N 个子行，更新时随机选择一个子行，读取时 SUM 所有子行。

-- Original: single hot row CREATE TABLE product_stock ( product_id INT PRIMARY KEY, stock INT ); -- All threads fight for the same row → bottleneck -- Sharded: 16 sub-rows per product CREATE TABLE product_stock_shard ( product_id INT, shard_id TINYINT, -- 0..15 stock INT, PRIMARY KEY (product_id, shard_id) ); -- Initialize: distribute stock across shards -- product_id=100, total stock=1000, 16 shards → 62 or 63 each -- Deduct stock: randomly pick a shard UPDATE product_stock_shard SET stock = stock - 1 WHERE product_id = 100 AND shard_id = FLOOR(RAND() * 16) AND stock > 0; -- Read total stock: SELECT SUM(stock) FROM product_stock_shard WHERE product_id = 100; -- Throughput improvement: ~16x (linear with shard count) -- Tradeoff: reads become slightly more expensive (SUM over N shards)

方案 2: 队列 + 批量合并

将变更请求写入队列表（高吞吐、无争用），后台 Worker 定期合并到主表。详见 8.6 节。

方案 3: 乐观锁（Version Column）

不使用 FOR UPDATE 显式加锁，而是用版本号实现 CAS（Compare-And-Swap）语义：

CREATE TABLE product ( id INT PRIMARY KEY, stock INT, version INT DEFAULT 0 ); -- Step 1: Read (no lock, snapshot read) SELECT id, stock, version FROM product WHERE id = 100; -- Returns: stock=50, version=7 -- Step 2: Update with version check (CAS) UPDATE product SET stock = stock - 1, version = version + 1 WHERE id = 100 AND version = 7; -- If affected_rows = 1 → success -- If affected_rows = 0 → another transaction already modified it → retry -- Advantage: no lock wait, losers retry immediately -- Disadvantage: high retry rate under extreme contention (bad for 100+ concurrent)

方案 4: SELECT ... SKIP LOCKED（MySQL 8.0+）

跳过已被锁定的行，适用于任务队列、工单分配等场景：

-- Job queue table CREATE TABLE jobs ( id INT PRIMARY KEY, status ENUM('pending','running','done'), payload JSON ); -- Worker picks next available job, skipping locked ones BEGIN; SELECT * FROM jobs WHERE status = 'pending' ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED; -- If another worker already locked the first pending job, skip to the next one -- No blocking, no contention! UPDATE jobs SET status = 'running' WHERE id = ?; COMMIT;

方案 5: NOWAIT（MySQL 8.0+）

如果无法立即获取锁，立即报错而不是等待。适用于"获取不到就快速失败"的场景：

BEGIN; SELECT * FROM orders WHERE id = 10 FOR UPDATE NOWAIT; -- If id=10 is already locked: -- ERROR 3572 (HY000): Statement aborted because lock(s) could not be acquired -- immediately and NOWAIT is set. -- Application catches error and handles gracefully (show "item busy" to user)

方案对比

方案	吞吐提升	复杂度	适用场景
分片计数器	~Nx (N=分片数)	中	计数器、库存
队列 + 合并	极高	高	可容忍短延迟的累加场景
乐观锁	低争用下高	低	争用不激烈的更新
SKIP LOCKED	高（无等待）	低	任务队列、工单
NOWAIT	高（快速失败）	低	需要快速反馈的场景

11.6 综合策略决策树

热点行处理决策树：是否需要实时准确余额/库存? │ ├── 是 │ │ │ ├── 并发量 < 100 TPS? │ │ ├── 是 → 乐观锁（版本号） │ │ └── 否 → 分片计数器 │ │ │ └── 是否可以排队处理? │ ├── 是 → 队列 + 批量合并 │ └── 否 → 分片计数器 + NOWAIT 快速失败 │ └── 否（可容忍短延迟） │ ├── 是否为任务分配场景? │ ├── 是 → SKIP LOCKED │ └── 否 → 队列 + 批量合并 │ └── 考虑 Redis 缓存 + MySQL 异步回写

11.7 分片计数器实现细节与注意事项

分片计数器看似简单，但生产中有几个容易忽略的问题：

-- Problem 1: shard exhaustion -- If one shard reaches 0 while others still have stock, -- the random selection may fail repeatedly. -- Fix: retry with different shard, or use a smarter selection: UPDATE product_stock_shard SET stock = stock - 1 WHERE product_id = 100 AND stock > 0 ORDER BY RAND() LIMIT 1; -- Problem 2: rebalancing -- When restocking, distribute evenly across shards: UPDATE product_stock_shard SET stock = stock + FLOOR(1000 / 16) WHERE product_id = 100; -- Remainder goes to shard 0: UPDATE product_stock_shard SET stock = stock + (1000 MOD 16) WHERE product_id = 100 AND shard_id = 0; -- Problem 3: negative stock race -- The AND stock > 0 check + UPDATE is atomic within a single row -- but total stock across all shards might briefly show wrong totals -- during concurrent operations. This is acceptable for most use cases.

11.8 Redis + MySQL 混合方案

对于极端高并发的场景（万级 TPS），可以将库存/计数器缓存在 Redis 中，异步回写到 MySQL：

-- Architecture: -- -- Client → Redis DECR → check >= 0? -- ├── Yes → Queue message → Worker → MySQL UPDATE -- └── No → Redis INCR (restore) → return "out of stock" -- -- Pros: Redis handles 100K+ TPS, MySQL only sees merged writes -- Cons: Redis crash loses in-flight data (mitigate with AOF + queue) -- Temporary inconsistency between Redis and MySQL -- Redis commands (pseudocode): -- DECR stock:product:100 -- if result < 0: -- INCR stock:product:100 # restore -- return ERROR_OUT_OF_STOCK -- else: -- publish deduction event to message queue -- return SUCCESS

12. 真实案例

案例 1: 电商库存扣减死锁

问题描述

秒杀活动中，大量请求同时扣减库存。应用层先查询库存（FOR UPDATE），判断够不够，再 UPDATE。高并发时频繁发生死锁。

-- Problem code (simplified) BEGIN; -- Step 1: lock the SKU row SELECT stock FROM sku WHERE sku_id = 1001 FOR UPDATE; -- Step 2: check stock -- if stock < quantity: rollback -- Step 3: deduct UPDATE sku SET stock = stock - 1 WHERE sku_id = 1001; -- Step 4: create order (on different table) INSERT INTO orders (sku_id, user_id, qty) VALUES (1001, ?, 1); COMMIT; -- Deadlock scenario: -- An order might contain multiple SKUs. If TX A locks sku_id=1001 then 1002, -- while TX B locks sku_id=1002 then 1001 → classic AB-BA deadlock

解决方案

-- Fix 1: Sort SKU IDs before locking (consistent ordering) sku_ids = sorted(order_items.keys()) # [1001, 1002] for sku_id in sku_ids: SELECT stock FROM sku WHERE sku_id = ? FOR UPDATE -- Fix 2: Single UPDATE with stock check (atomic, no FOR UPDATE needed) UPDATE sku SET stock = stock - 1 WHERE sku_id = 1001 AND stock >= 1; -- Check affected_rows: 1=success, 0=insufficient stock -- No separate SELECT FOR UPDATE → simpler, less lock contention -- Fix 3: For extreme concurrency, use sharded counters (see Section 11)

案例 2: 转账（复式记账）死锁

问题描述

用户 A 给用户 B 转账，同时用户 B 也给用户 A 转账。两个事务以相反的顺序锁定两个账户。

-- Transfer: A → B (TX 1) BEGIN; UPDATE accounts SET balance = balance - 100 WHERE user_id = 'A'; -- lock A UPDATE accounts SET balance = balance + 100 WHERE user_id = 'B'; -- lock B → wait COMMIT; -- Transfer: B → A (TX 2) BEGIN; UPDATE accounts SET balance = balance - 50 WHERE user_id = 'B'; -- lock B UPDATE accounts SET balance = balance + 50 WHERE user_id = 'A'; -- lock A → DEADLOCK COMMIT;

解决方案

-- Fix: Always lock accounts in ascending user_id order def transfer(from_user, to_user, amount): # Ensure consistent lock ordering first, second = sorted([from_user, to_user]) BEGIN; SELECT * FROM accounts WHERE user_id = first FOR UPDATE; SELECT * FROM accounts WHERE user_id = second FOR UPDATE; UPDATE accounts SET balance = balance - amount WHERE user_id = from_user; UPDATE accounts SET balance = balance + amount WHERE user_id = to_user; COMMIT; -- Now both TX 1 and TX 2 will lock 'A' first, then 'B' (if A < B) -- No deadlock possible

案例 3: 批量导入引发的 Gap Lock 风暴

问题描述

批量导入用户数据到有唯一索引（email）的表中。多个线程并发 INSERT ... ON DUPLICATE KEY UPDATE，频繁死锁。

-- Multiple threads doing: INSERT INTO users (email, name) VALUES ('[email protected]', 'Alice') ON DUPLICATE KEY UPDATE name = VALUES(name); -- Problem: INSERT on unique index acquires next-key lock, not just record lock -- When the key doesn't exist yet, InnoDB acquires gap locks on idx_email -- Multiple threads with gap locks on adjacent gaps → INSERT intention deadlock

解决方案

-- Fix 1: Switch to READ COMMITTED (no gap locks) SET SESSION transaction_isolation = 'READ-COMMITTED'; -- Fix 2: Batch INSERTs in a single statement (fewer transactions) INSERT INTO users (email, name) VALUES ('[email protected]', 'Alice'), ('[email protected]', 'Bob'), ... ('[email protected]', 'Zach') ON DUPLICATE KEY UPDATE name = VALUES(name); -- Fix 3: Sort import data by the unique key to minimize gap lock range -- Process emails alphabetically: a@..., b@..., c@... → sequential gaps -- Fix 4: Use LOAD DATA INFILE for very large imports (single transaction)

13. FAQ

Q1: InnoDB 行锁是加在行上还是索引上？

加在索引记录上。InnoDB 的每种行锁（Record Lock、Gap Lock、Next-Key Lock）都是附着在某个索引的 B+Tree 叶子节点上的。如果表没有定义任何索引，InnoDB 会创建一个隐式聚簇索引（基于 6 字节的 ROW_ID），行锁加在这个隐式索引上。这就是为什么没有索引的表执行 UPDATE/DELETE 会锁住所有行——因为必须扫描隐式索引的每一条记录。

Q2: 为什么 Gap Lock 之间不冲突？

因为 Gap Lock 的唯一目的是阻止 INSERT 到间隙中，而不是保护间隙中的"数据"（间隙中本来就没有数据）。两个事务同时声明"不允许在这个间隙里插入"，不产生任何矛盾。这也是 InnoDB 源码中 lock_rec_has_to_wait() 的特殊判断：如果请求的锁不是 Insert Intention 类型，则永远不等待 Gap Lock。但注意：这个设计也是 Gap Lock + INSERT 死锁的根源——两个事务都轻松拿到 Gap Lock，然后都因为对方的 Gap Lock 而无法 INSERT。

Q3: 在 RC 隔离级别下还会有死锁吗？

可以，但概率大幅降低。RC 没有 Gap Lock，消除了所有 Gap Lock 相关的死锁模式（模式 B 和 D）。但 AB-BA 顺序死锁（模式 A）和二级索引+主键死锁（模式 C）仍然可能发生，因为它们涉及的是 Record Lock，与隔离级别无关。实际生产中，从 RR 切到 RC 通常能减少 80%+ 的死锁。

Q4: SHOW ENGINE INNODB STATUS 只保留最后一次死锁信息，怎么看历史？

设置 innodb_print_all_deadlocks = ON，每次发生死锁都会写入 MySQL 错误日志。结合 ELK、Loki 或其他日志收集系统，可以解析并聚合死锁模式、识别高频死锁的表和索引。另外，MySQL 8.0 的 performance_schema.events_errors_summary_by_account_by_error 可以统计 ERROR 1213 的发生次数。

Q5: 什么时候应该关闭 innodb_deadlock_detect？

几乎永远不应该关闭。只有在非常特殊的场景下考虑：(1) 同一行上有极端并发写入（例如 1000+ TPS 更新同一行），且死锁检测的 CPU 开销成为瓶颈。(2) 已经用分片计数器/队列等方式消除了真正的死锁可能性。(3) 将 innodb_lock_wait_timeout 设为较小值（如 3-5 秒）作为后备。关闭死锁检测意味着死锁需要等到超时才能被发现，用户体验会变差。

Q6: FOR UPDATE 和 FOR SHARE 在使用上有什么最佳实践？

原则：(1) 如果你读取数据后要修改它，用 FOR UPDATE。(2) 如果你只需要确保数据在事务期间不被修改，用 FOR SHARE。(3) 避免对不需要锁的查询使用锁定读——大部分只读查询用普通 SELECT（快照读）即可。(4) FOR SHARE 容易导致死锁：两个事务同时 FOR SHARE 同一行（兼容），然后都 UPDATE 该行（需要 X 锁）→ 死锁。如果读后要写，直接用 FOR UPDATE。

Q7: LOCK TABLES 和 InnoDB 行锁是什么关系？

LOCK TABLES 是 MySQL Server 层的表级锁，与 InnoDB 引擎层的行锁是独立的两套机制。在 InnoDB 中，不推荐使用 LOCK TABLES，因为它会禁用 InnoDB 的行级锁优化。正确的做法是使用 SELECT ... FOR UPDATE 和事务来实现并发控制。唯一需要 LOCK TABLES 的场景是与 MyISAM 表交互（MyISAM 不支持行锁和事务）。

Q8: 如何判断我的应用是否受到锁争用影响？

监控以下指标：(1) SHOW GLOBAL STATUS LIKE 'Innodb_row_lock%': Innodb_row_lock_waits（累计等待次数）和 Innodb_row_lock_time_avg（平均等待时间 ms）。(2) performance_schema.data_lock_waits 中的行数（当前等待数）。(3) information_schema.INNODB_TRX 中 trx_state='LOCK WAIT' 的事务。(4) 慢查询日志中 Lock_time 较高的查询。如果 Innodb_row_lock_time_avg > 500ms 或 Innodb_row_lock_waits 增速超过 100/s，就需要关注了。

Q9: Next-Key Lock 到底锁的是哪个范围？

Next-Key Lock 锁定的是一个左开右闭区间 (prev_record, current_record]。例如索引中有记录 5, 10, 15，对 10 加 Next-Key Lock 锁定的范围是 (5, 10]，即阻止 INSERT id=6,7,8,9,10 到间隙中，并锁定记录 10 本身。需要注意 InnoDB 有一些优化规则会自动将 Next-Key Lock 退化为 Record Lock 或 Gap Lock：(1) 唯一索引等值命中 → 退化为 Record Lock；(2) 非唯一索引等值扫描到第一条不满足的记录 → 该记录上退化为 Gap Lock。

Q10: 如何找出持有锁但不释放的"幽灵事务"？

常见原因：应用代码中 BEGIN 后没有 COMMIT/ROLLBACK（连接泄露），或在事务中等待外部资源超时。排查方法：

SELECT trx_id, trx_started, trx_state, trx_query, trx_mysql_thread_id FROM information_schema.INNODB_TRX ORDER BY trx_started; — 找到 trx_started 很久以前且 trx_query=NULL 的事务
SELECT * FROM performance_schema.threads WHERE THREAD_ID = (SELECT THREAD_ID FROM performance_schema.events_statements_current WHERE SQL_TEXT IS NOT NULL LIMIT 1); — 追踪线程来源
如果确认是泄露连接，用 KILL <processlist_id> 杀掉该连接
根本修复：确保应用使用连接池，且连接池有空闲超时和泄露检测机制

← 上一章：InnoDB 内核返回目录下一章：索引优化 →