传统的锁排查如同翻阅一本已经写完的侦探小说，而基于 performance_schema 的排查则像在案发现场安装了一个实时监控摄像头。

一、锁排查的范式转移：从“事后尸检”到“实时监控”

在 MySQL 5.7 之前，数据库管理员们主要依赖 SHOW ENGINE INNODB STATUS 和 information_schema 来排查棘手的锁问题。这些工具如同 “尸检报告” ，能详细告诉你系统 “曾经发生了什么”，比如最后一次死锁的细节，但对于 “正在发生什么” 却常常无能为力——当业务系统突然卡顿，页面加载超时，你却无法实时看到是哪个具体的事务阻塞了关键更新。

传统工具的局限性：

• SHOW ENGINE INNODB STATUS：仅显示最近一次死锁信息，且输出为半结构化文本，难以程序化分析。
• information_schema.INNODB_LOCKS/INNODB_LOCK_WAITS：在 MySQL 8.0 中已被标记为废弃，提供的是 静态快照视图，无法反映瞬时状态。
• 无法完整追踪 “谁在等谁 → 等什么锁 → 持锁者在做什么” 的完整证据链。

performance_schema 的革命性突破：
作为 MySQL 内置的性能监控引擎，performance_schema 提供了 动态、低开销 的插桩框架。对于锁等待场景，它的核心价值在于：实时记录锁的“获取-等待-释放”全过程，而非最终状态。

这种能力让 performance_schema 成为线上实时故障排查的 “动态心电图” ，尤其适合以下场景：

• 突发性业务卡顿，但未形成死锁
• 间歇性慢查询，怀疑是锁竞争导致
• 需要定位“锁源头” 而非仅仅“锁现象”

二、核心原理：performance_schema 如何捕捉锁等待

2.1 监控框架的三层架构

Instrumentation → Consumer → SQL Interface
    (采集点)     → (消费者表) → (用户查询)

1. 采集点（Instrument）：在 MySQL 服务器代码的关键路径插入的钩子函数，如 wait/lock/table/sql/handler。
2. 消费者表（Consumer）：如 data_locks、data_lock_waits、metadata_locks 等，用于存储采集到的原始数据。
3. SQL接口：通过标准的 SELECT 查询将数据暴露给用户和监控系统。

2.2 锁等待的完整证据链

data_lock_waits               data_locks                  threads
┌─────────────────────┐      ┌─────────────────┐      ┌─────────────┐
│ waiting_lock_id   ──┼─────►│ lock_id         │      │ thread_id   │
│ blocking_lock_id  ──┼─────►│ lock_id         │◄─────┤ processlist │
│                     │      │ thread_id       │      │  id         │
└─────────────────────┘      └─────────────────┘      └─────────────┘
         │                                                    ▲
         │                                                    │
         ▼                                                    │
 events_statements_current                                    │
┌──────────────────────┐                                     │
│ thread_id          ──┼─────────────────────────────────────┘
│ sql_text            │
└──────────────────────┘

关键设计：data_lock_waits 表通过外键关系明确记录了等待的 因果关系 ，这是传统工具无法提供的。这张表直接回答了“谁阻塞了谁”这个核心问题。

三、实战前准备：环境检查与配置优化

3.1 确认 performance_schema 启用状态

-- 基础检查
SHOW VARIABLES LIKE 'performance_schema';

-- 更详细的配置检查（MySQL 8.0/9.0 同样适用）
SELECT 
    VARIABLE_NAME,
    VARIABLE_VALUE,
    CASE WHEN VARIABLE_VALUE = 'ON' THEN '✅' ELSE '❌' END AS STATUS
FROM performance_schema.global_variables
WHERE VARIABLE_NAME IN (
    'performance_schema',
    'performance_schema_consumer_events_statements_current',
    'performance_schema_consumer_global_instrumentation'
);

3.2 必要时调整采集粒度

默认情况下，MySQL 8.0 和 9.0 已经为锁监控启用了必要的采集点。但在某些深度调试场景，你可能需要确认。

-- 查看当前锁监控设置
SELECT NAME, ENABLED, TIMED 
FROM performance_schema.setup_instruments 
WHERE NAME LIKE '%lock%' AND NAME LIKE '%innodb%';

-- 在测试环境中，如果需要更全面的监控，可以启用所有锁相关的instrument
-- 生产环境请谨慎评估性能影响
UPDATE performance_schema.setup_instruments 
SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME LIKE 'wait/synch/mutex/innodb/%'
   OR NAME LIKE 'wait/synch/rwlock/innodb/%';

四、核心视图深度解析

4.1 data_locks：锁的“档案库”

data_locks 表（MySQL 8.0 引入，替代了 information_schema.INNODB_LOCKS）记录了 InnoDB 引擎当前持有或正在请求的所有锁。

-- 关键字段解读
SELECT 
    ENGINE,                    -- 存储引擎（始终为 INNODB）
    ENGINE_LOCK_ID,           -- 锁的唯一标识
    ENGINE_TRANSACTION_ID,    -- 事务 ID
    THREAD_ID,                -- 持有锁的线程 ID
    OBJECT_SCHEMA,            -- 数据库名
    OBJECT_NAME,              -- 表名
    INDEX_NAME,               -- 索引名（NULL 表示表锁）
    LOCK_TYPE,                -- 锁类型：TABLE（表锁）/ RECORD（行锁）
    LOCK_MODE,                -- 锁模式：S, X, IS, IX, GAP, NEXT_KEY 等
    LOCK_STATUS,              -- 状态：GRANTED（已持有）/ WAITING（等待中）
    LOCK_DATA                 -- 锁定的数据（主键值或其他索引值）
FROM performance_schema.data_locks;

LOCK_MODE 深度解读：

• S / X：共享锁/排他锁（Record Lock）
• S,GAP / X,GAP：间隙锁（Gap Lock）
• S,REC_NOT_GAP / X,REC_NOT_GAP：仅记录锁，不含间隙
• S,INSERT_INTENTION / X,INSERT_INTENTION：插入意向锁
• X,NEXT_KEY：Next-Key Lock（InnoDB 默认行锁算法）

4.2 data_lock_waits：等待关系的“因果图”

这是 最关键 的视图，直接映射了阻塞关系。它回答了“谁在等谁”。

SELECT 
    REQUESTING_ENGINE_LOCK_ID,      -- 正在等待的锁 ID
    REQUESTING_ENGINE_TRANSACTION_ID, -- 正在等待的事务 ID
    BLOCKING_ENGINE_LOCK_ID,        -- 造成阻塞的锁 ID  
    BLOCKING_ENGINE_TRANSACTION_ID, -- 造成阻塞的事务 ID
    REQUESTING_THREAD_ID,           -- 等待的线程 ID
    BLOCKING_THREAD_ID              -- 阻塞的线程 ID
FROM performance_schema.data_lock_waits;

关键洞察：一个 BLOCKING_ENGINE_TRANSACTION_ID 可能对应多个 REQUESTING_ENGINE_TRANSACTION_ID，这表明形成了 阻塞链（A 阻塞 B，B 阻塞 C）。

五、完整排查实战：UPDATE 卡死的深度追踪

场景还原

业务反馈：UPDATE orders SET status = 2 WHERE user_id = 100 AND order_time > '2023-01-01'; 执行超过 30 秒未返回。

5.1 第一步：快速确认锁等待存在

-- 快速检查是否存在锁等待
SELECT COUNT(*) AS lock_wait_count 
FROM performance_schema.data_lock_waits;

-- 如果有等待，查看等待概况
SELECT 
    COUNT(*) AS total_waits,
    COUNT(DISTINCT BLOCKING_ENGINE_TRANSACTION_ID) AS blocking_trx_count
FROM performance_schema.data_lock_waits;

结果解读：如果 lock_wait_count > 0，确认系统存在锁等待；blocking_trx_count 表示有几个独立的事务在阻塞他人。

5.2 第二步：定位阻塞关系链

-- 完整阻塞关系查询（核心 SQL）
WITH LockWaitChain AS (
    SELECT 
        w.REQUESTING_ENGINE_TRANSACTION_ID AS waiting_trx_id,
        w.BLOCKING_ENGINE_TRANSACTION_ID AS blocking_trx_id,
        r.trx_started AS waiting_started,
        b.trx_started AS blocking_started,
        TIMESTAMPDIFF(SECOND, b.trx_started, NOW()) AS blocking_age_seconds,
        r.trx_mysql_thread_id AS waiting_thread,
        b.trx_mysql_thread_id AS blocking_thread
    FROM performance_schema.data_lock_waits w
    JOIN information_schema.innodb_trx r 
        ON w.REQUESTING_ENGINE_TRANSACTION_ID = r.trx_id
    JOIN information_schema.innodb_trx b 
        ON w.BLOCKING_ENGINE_TRANSACTION_ID = b.trx_id
)
SELECT 
    waiting_trx_id,
    blocking_trx_id,
    waiting_thread,
    blocking_thread,
    blocking_age_seconds,
    waiting_started,
    blocking_started,
    CASE 
        WHEN blocking_age_seconds > 300 THEN '⛔ 长事务风险'
        WHEN blocking_age_seconds > 60 THEN '⚠️  事务较长'
        ELSE '✅ 正常范围'
    END AS risk_assessment
FROM LockWaitChain
ORDER BY blocking_age_seconds DESC;

5.3 第三步：获取阻塞双方 SQL 上下文

-- 获取所有涉及锁等待的线程当前执行的 SQL
SELECT 
    t.PROCESSLIST_ID AS conn_id,
    t.PROCESSLIST_USER AS user,
    t.PROCESSLIST_HOST AS host,
    t.PROCESSLIST_DB AS db,
    t.PROCESSLIST_COMMAND AS cmd,
    t.PROCESSLIST_INFO AS current_sql,
    es.SQL_TEXT AS p_sql_text,
    es.ROWS_EXAMINED,
    es.ROWS_AFFECTED,
    TIMESTAMPDIFF(SECOND, es.EVENT_TIME, NOW()) AS sql_duration_sec
FROM performance_schema.threads t
LEFT JOIN performance_schema.events_statements_current es 
    ON t.THREAD_ID = es.THREAD_ID
WHERE t.PROCESSLIST_ID IN (
    -- 获取所有阻塞者和等待者的连接 ID
    SELECT DISTINCT r.trx_mysql_thread_id
    FROM performance_schema.data_lock_waits w
    JOIN information_schema.innodb_trx r 
        ON w.REQUESTING_ENGINE_TRANSACTION_ID = r.trx_id
        OR w.BLOCKING_ENGINE_TRANSACTION_ID = r.trx_id
)
AND t.PROCESSLIST_ID IS NOT NULL;

5.4 第四步：分析锁的粒度与范围

-- 分析锁的具体类型和范围
SELECT 
    dl.OBJECT_SCHEMA,
    dl.OBJECT_NAME,
    dl.INDEX_NAME,
    dl.LOCK_TYPE,
    dl.LOCK_MODE,
    dl.LOCK_STATUS,
    dl.LOCK_DATA,
    COUNT(*) AS lock_count,
    GROUP_CONCAT(DISTINCT dl.ENGINE_TRANSACTION_ID) AS involved_trx_ids
FROM performance_schema.data_locks dl
WHERE dl.ENGINE_TRANSACTION_ID IN (
    SELECT DISTINCT REQUESTING_ENGINE_TRANSACTION_ID 
    FROM performance_schema.data_lock_waits
    UNION 
    SELECT DISTINCT BLOCKING_ENGINE_TRANSACTION_ID 
    FROM performance_schema.data_lock_waits
)
GROUP BY 
    dl.OBJECT_SCHEMA,
    dl.OBJECT_NAME,
    dl.INDEX_NAME,
    dl.LOCK_TYPE,
    dl.LOCK_MODE,
    dl.LOCK_STATUS,
    dl.LOCK_DATA
ORDER BY lock_count DESC;

5.5 典型分析结果与根因判断

通过以上四步分析，可能发现以下典型场景：

场景 A：Next-Key Lock 范围过大

OBJECT_NAME: orders
INDEX_NAME: idx_user_id
LOCK_MODE: X,NEXT_KEY
LOCK_DATA: (100, '2023-01-01')  -- 实际锁定了 user_id=100 的整个时间范围

根因：WHERE 条件 user_id = 100 AND order_time > '2023-01-01' 在 idx_user_id(user_id) 单列索引上使用 Next-Key Lock，锁定了 user_id=100 的所有后续记录，即使 order_time 条件不满足。
解决方案：

1. 创建复合索引 (user_id, order_time)，让查询能精准定位。
2. 在业务允许的情况下，考虑使用 READ COMMITTED 隔离级别以减少间隙锁。

场景 B：热点行更新竞争

OBJECT_NAME: account_balance
INDEX_NAME: PRIMARY  
LOCK_MODE: X,REC_NOT_GAP
LOCK_DATA: 12345  -- 账户 ID
LOCK_COUNT: 8     -- 多个事务在等待同一行

根因：多个事务同时更新同一账户余额，形成热点行竞争。
解决方案：

1. 业务层引入排队或合并更新机制。
2. 考虑拆分热点账户，将余额分散到多行。
3. 使用乐观锁（版本号或时间戳）减少锁持有时间。

六、MySQL 8.0 与 9.0 的特异性与注意事项

6.1 版本核心变化

特性/方面	MySQL 8.0 版本	MySQL 9.0 版本
核心锁表	performance_schema.data_locks, performance_schema.data_lock_waits（完全替换 information_schema 中的旧表）。	延续 8.0 的表结构，未进行破坏性改动。
监控增强	引入了更细粒度的等待事件。	新增了如 variables_metadata 等系统表，提供更详细的变量元信息，对优化排查有帮助。
排查方法	上文所有 SQL 和思路完全适用，且是官方推荐的标准做法。	同样完全适用。
性能与风险	在 8.0.37 之前，高并发下查询 data_locks 可能触发 Bug 导致系统挂起。8.0.37 已修复。	相关问题已修复，但查询海量锁数据本身仍是重量级操作。

6.2 关键注意事项与最佳实践

1. 警惕监控本身的性能风险：当系统中存在海量行锁（如一个未提交的事务锁定了上百万行）时，查询 performance_schema.data_locks 可能会消耗大量内存和 CPU，在早期 MySQL 8.0 版本中甚至可能导致实例挂起。

   • 最佳实践：先用 SELECT COUNT(*) FROM performance_schema.data_locks; 快速评估锁数量。若数量巨大，优先通过 information_schema.innodb_trx 找出并处理长事务源头。

2. 优化关联查询效率：data_lock_waits 表上的事务 ID 索引在复杂关联查询时可能效率不高。如果发现查询慢，可以尝试简化查询条件或使用 IGNORE INDEX 提示。

3. 拓展监控范围至元数据锁：除了 InnoDB 行锁，元数据锁（MDL）等待是另一个常见阻塞原因。在 MySQL 8.0/9.0 中，你可以通过以下方式监控：

   -- 首先确保启用 MDL 监控（通常默认开启）
   SELECT * FROM performance_schema.setup_instruments 
   WHERE NAME = 'wait/lock/metadata/sql/mdl';
   
   -- 查询当前的 MDL 等待
   SELECT * FROM performance_schema.metadata_locks 
   WHERE OWNER_THREAD_ID != 0 
   AND LOCK_STATUS = 'PENDING';
   

4. 拥抱新的诊断工具：

   • EXPLAIN ANALYZE (MySQL 8.0.18+)：在排查因低效查询导致锁范围过大时，这个工具可以实际执行查询并输出详细的执行计划和实际耗时，比传统 EXPLAIN 更精准。
   • SELECT * FROM sys.schema_table_lock_waits;：如果你启用了 sys 库，这个视图能快速给出表级锁等待的友好摘要。

七、根治方案：从紧急止血到架构优化

7.1 紧急处理：终止阻塞源

-- 1. 识别最老的阻塞事务（源头）
SELECT 
    b.trx_mysql_thread_id AS blocking_thread_id,
    TIMESTAMPDIFF(SECOND, b.trx_started, NOW()) AS trx_age,
    b.trx_query AS blocking_sql,
    COUNT(w.REQUESTING_ENGINE_TRANSACTION_ID) AS blocked_count
FROM information_schema.innodb_trx b
LEFT JOIN performance_schema.data_lock_waits w 
    ON b.trx_id = w.BLOCKING_ENGINE_TRANSACTION_ID
WHERE b.trx_state = 'RUNNING'
GROUP BY b.trx_id
HAVING blocked_count > 0 OR trx_age > 60  -- 长事务即使没阻塞别人也建议关注
ORDER BY trx_age DESC
LIMIT 3;

-- 2. 与业务方确认后，谨慎执行 KILL
-- KILL [上面查询出的 blocking_thread_id];

7.2 配置与架构优化

1. 数据库配置优化 (my.cnf)：

   [mysqld]
   # 减少单次锁等待时间
   innodb_lock_wait_timeout = 30
   # 确保死锁检测开启（8.0默认ON）
   innodb_deadlock_detect = ON
   # 控制事务大小，避免大事务产生海量锁
   innodb_undo_log_truncate = ON
   innodb_max_undo_log_size = 1G
   

2. 应用层设计优化：

   • 事务最小化：确保事务内只包含必要的 SQL，避免在事务内进行远程 RPC 调用或长时间计算。
   • 使用锁超时与跳过 (MySQL 8.0+)：

     -- NOWAIT: 获取不到锁立即报错
     SELECT * FROM table WHERE id = 1 FOR UPDATE NOWAIT;
     -- SKIP LOCKED: 跳过已被锁定的行，处理剩余行
     SELECT * FROM table WHERE status = 'pending' FOR UPDATE SKIP LOCKED;
     

   • 索引设计黄金法则：为 WHERE、JOIN、ORDER BY、GROUP BY 子句创建复合索引，避免索引失效导致锁表。

7.3 建立常态化监控

创建一个定期运行的监控脚本或将其集成到现有的数据库监控平台中。

-- 示例：创建一个存储历史锁等待信息的表
CREATE TABLE IF NOT EXISTS lock_wait_history (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    sample_time DATETIME NOT NULL,
    waiting_trx_id BIGINT UNSIGNED,
    blocking_trx_id BIGINT UNSIGNED,
    blocking_age_sec INT,
    locked_table VARCHAR(64),
    waiting_query TEXT,
    blocking_query TEXT,
    KEY idx_sample_time (sample_time),
    KEY idx_blocking_trx (blocking_trx_id)
);

-- 定期（如每分钟）采集严重的锁等待信息
INSERT INTO lock_wait_history 
    (sample_time, waiting_trx_id, blocking_trx_id, blocking_age_sec, locked_table, waiting_query, blocking_query)
SELECT 
    NOW(),
    w.REQUESTING_ENGINE_TRANSACTION_ID,
    w.BLOCKING_ENGINE_TRANSACTION_ID,
    TIMESTAMPDIFF(SECOND, b.trx_started, NOW()),
    CONCAT(dl.OBJECT_SCHEMA, '.', dl.OBJECT_NAME),
    LEFT(r.trx_query, 1000),
    LEFT(b.trx_query, 1000)
FROM performance_schema.data_lock_waits w
JOIN information_schema.innodb_trx r ON w.REQUESTING_ENGINE_TRANSACTION_ID = r.trx_id
JOIN information_schema.innodb_trx b ON w.BLOCKING_ENGINE_TRANSACTION_ID = b.trx_id
JOIN performance_schema.data_locks dl ON w.BLOCKING_ENGINE_LOCK_ID = dl.ENGINE_LOCK_ID
WHERE TIMESTAMPDIFF(SECOND, b.trx_started, NOW()) > 5  -- 只记录阻塞超过5秒的
LIMIT 100; -- 防止一次插入过多

总结

从 SHOW ENGINE INNODB STATUS 的静态快照到 performance_schema 的动态追踪，MySQL 锁排查完成了一次从 “法医验尸” 到 “实时监察” 的范式转移。

核心思维转变：

• 从 “发生了什么” → “正在发生什么”
• 从 “现象描述” → “因果关系链”
• 从 “被动响应” → “主动预警与根治”

在 MySQL 8.0 和 9.0 的时代，这套方法论不仅依然有效，而且随着系统表的完善和新工具的加入变得更加强大。成功的关键在于：理解原理，善用工具，警惕风险，优化根本。通过将本文的实战方法融入日常运维，你能将令人头疼的数据库锁问题，转化为可观测、可分析、可根治的技术挑战，最终构建出更稳健、高性能的数据服务。