锁机制与死锁排查

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）是表级别的轻量锁，用于协调表锁与行锁之间的关系。

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

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

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

1.4 AUTO-INC 锁与 innodb_autoinc_lock_mode

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

生产建议: 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 可以观察到精确的锁信息：

SELECT ENGINE_TRANSACTION_ID, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_DATA
FROM performance_schema.data_locks;

+------------------------+------------+-----------+-----------+-----------+
| ENGINE_TRANSACTION_ID  | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_DATA |
+------------------------+------------+-----------+-----------+-----------+
| 281479386710048        | NULL       | TABLE     | IX        | NULL      |
| 281479386710048        | PRIMARY    | RECORD    | X,REC_NOT | 5         |
|                        |            |           | _GAP      |           |
+------------------------+------------+-----------+-----------+-----------+

源码参考: 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

-- performance_schema.data_locks output:
+------------------------+------------+-----------+-----------+-----------+
| ENGINE_TRANSACTION_ID  | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_DATA |
+------------------------+------------+-----------+-----------+-----------+
| 281479386710049        | NULL       | TABLE     | IX        | NULL      |
| 281479386710049        | PRIMARY    | RECORD    | X,GAP     | 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 行锁类型总结

3. 行锁兼容矩阵

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

3.1 完整兼容矩阵

核心规则: - 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 支持两种读取方式，它们对锁的影响完全不同：

关键区别: 在 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.

相关配置参数：

高并发场景: 在高并发环境下（数百个活跃事务），死锁检测本身可能成为性能瓶颈，因为每次等待都触发 DFS 遍历。MySQL 8.0.18+ 改进了检测算法，将其委托给后台线程。对于写热点极端的场景，可考虑关闭 innodb_deadlock_detect 并将 innodb_lock_wait_timeout 设为较小值（如 5 秒）。

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

解读要点：

1. hex 80000005 → 0x80000005 - 0x80000000 = 5，即 id=5。InnoDB 存储有符号整数时加了 0x80000000 偏移。
2. lock_mode X locks rec but not gap = Record Lock（X, REC_NOT_GAP）
3. lock_mode X（不带 but not gap）= Next-Key Lock
4. lock_mode X locks gap before rec = Gap Lock
5. 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 语句，观察每一步获取的锁和最终的死锁发生过程。

    选择场景:

      AB-BA 顺序死锁
      Gap Lock + INSERT 死锁
      唯一键冲突死锁
      二级索引 + 主键死锁

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< **生产监控建议:** 1. 开启 `innodb_print_all_deadlocks`，配合日志收集系统（ELK/Loki）解析死锁日志 2. 监控 `Innodb_row_lock_waits` 和 `Innodb_row_lock_time_avg` 状态变量 3. 设置告警：当 `Innodb_row_lock_time_avg > 1000ms` 或 `Innodb_deadlocks` 增量异常时触发 4. 定期检查 `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（连接泄露），或在事务中等待外部资源超时。排查方法： 1. `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` 的事务 2. `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);` — 追踪线程来源 3. 如果确认是泄露连接，用 `KILL ` 杀掉该连接 4. 根本修复：确保应用使用连接池，且连接池有空闲超时和泄露检测机制










    [← 上一章：InnoDB 内核](/books/high-performance-mysql/innodb-internals)
    [返回目录](/books/high-performance-mysql)
    [下一章：索引优化 →](/books/high-performance-mysql/index-guide)