MySQL的performance_schema分析锁等待与死锁

很多DBA和开发人员遇到MySQL锁等待问题时，第一反应是去查INFORMATION_SCHEMA下的INNODB_TRX、INNODB_LOCKS和INNODB_LOCK_WAITS这三张表。但如果你使用的是MySQL 5.7或8.0版本，你会发现官方已经明确标注INFORMATION_SCHEMA下的这几张表将被废弃，取而代之的是performance_schema中的一套全新表结构。直接用新方案，不仅能拿到更精确的锁等待链，还能捕捉到死锁发生瞬间的完整现场。

INFORMATION_SCHEMA的锁表本质上是查询时临时构建的快照，在高并发环境下，你查到的数据可能已经过时，而且它无法提供事务执行的具体SQL文本、锁的层级关系等关键信息。performance_schema则不同，它在内存中维护锁的运行时信息，开销极低，数据实时性极高。更重要的是，它把锁等待关系组织成了清晰的等待链，你一眼就能看出谁是阻塞者，谁是被阻塞者。

要使用这套功能，首先要确保performance_schema本身是开启的，然后激活相关的instruments。在MySQL 8.0中，默认已经启用了大部分等待事件，但为了保险，可以手动确认或开启以下配置：

UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME LIKE 'wait/lock/metadata/sql/mdl%';

UPDATE performance_schema.setup_consumers
SET ENABLED = 'YES'
WHERE NAME LIKE 'events_transactions%';

UPDATE performance_schema.setup_consumers
SET ENABLED = 'YES'
WHERE NAME LIKE 'events_statements%';

如果你需要分析行级锁等待，重点关注的是wait/lock/innodb/row_lock这个instrument。MDL锁的问题则看wait/lock/metadata/sql/mdl相关的项。这些配置调整后，新产生的锁等待事件就会被记录下来。

分析锁等待主要依赖三张表：data_locks、data_lock_waits和threads。data_locks记录了当前所有活跃的InnoDB锁，每一行代表一个锁资源。关键字段包括ENGINE_TRANSACTION_ID（持有锁的事务ID）、OBJECT_SCHEMA和OBJECT_NAME（锁所在的库和表）、LOCK_TYPE（行锁还是表锁）、LOCK_MODE（锁的模式，如IX、X、GAP等）、LOCK_DATA（被锁定的具体索引值）。

data_lock_waits则专门描述等待关系，它的REQUESTING_ENGINE_TRANSACTION_ID字段代表正在等待锁的事务，BLOCKING_ENGINE_TRANSACTION_ID代表持有锁并造成阻塞的事务。把这两张表关联起来，就能画出完整的锁等待拓扑图。

假设线上出现大量连接堆积，业务侧反馈操作卡死。你可以直接跑下面这条SQL，它会把等待关系一目了然地展示出来：

SELECT
    r.trx_id AS waiting_trx_id,
    r.trx_mysql_thread_id AS waiting_thread,
    r.trx_query AS waiting_query,
    b.trx_id AS blocking_trx_id,
    b.trx_mysql_thread_id AS blocking_thread,
    b.trx_query AS blocking_query,
    l.lock_type,
    l.lock_mode,
    l.lock_data,
    l.object_schema,
    l.object_name
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 l
    ON w.BLOCKING_ENGINE_LOCK_ID = l.ENGINE_LOCK_ID;

这条SQL的输出非常直观。waiting_trx_id和waiting_query告诉你哪个事务在等，它正在执行什么SQL。blocking_trx_id和blocking_query告诉你谁在阻塞，它当前在执行什么语句。lock_type、lock_mode和lock_data精确描述了阻塞锁的类型和锁定范围。比如你看到lock_type是RECORD，lock_mode是X，lock_data是一个主键值，那就说明阻塞者持有某行记录的排他锁，等待者想要获取同一行的锁。

很多时候，阻塞者本身可能并不在干活，它的blocking_query字段显示为NULL，这说明该事务已经执行完语句但尚未提交，就这么挂着占着锁不释放。这种情况在应用代码中常见于开启事务后进行了耗时操作，比如调用外部接口、处理文件，或者干脆是代码逻辑中漏掉了commit。

现实中的锁等待往往不是一对一的简单关系。事务A阻塞事务B，事务B又阻塞事务C，形成一条链。要找到最终的根因，你需要顺着blocking_trx_id一层层往上追溯。可以写一个递归查询或者直接在应用层做循环查找。在MySQL 8.0中，可以利用CTE递归特性来一次性揪出整条链的源头：

WITH RECURSIVE lock_chain AS (
    SELECT
        r.trx_id,
        r.trx_mysql_thread_id,
        r.trx_query,
        b.trx_id AS blocking_trx_id,
        b.trx_mysql_thread_id AS blocking_thread,
        b.trx_query AS blocking_query,
        1 AS level
    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
    UNION ALL
    SELECT
        lc.trx_id,
        lc.trx_mysql_thread_id,
        lc.trx_query,
        b.trx_id,
        b.trx_mysql_thread_id,
        b.trx_query,
        lc.level + 1
    FROM lock_chain lc
    JOIN performance_schema.data_lock_waits w ON lc.blocking_trx_id = w.REQUESTING_ENGINE_TRANSACTION_ID
    JOIN information_schema.innodb_trx b ON w.BLOCKING_ENGINE_TRANSACTION_ID = b.trx_id
)
SELECT * FROM lock_chain ORDER BY level;

这条递归SQL跑完，level最大的那行记录对应的blocking_trx_id就是整条链的根节点，也就是罪魁祸首。找到它之后，你可以根据blocking_thread直接kill掉对应的连接，或者紧急情况下先kill掉，再慢慢排查业务逻辑。

死锁和锁等待不同，它是两个或多个事务互相持有对方需要的锁，形成闭环，InnoDB会自动检测并回滚其中一个事务来打破僵局。但死锁发生后，你往往只能从应用日志里看到一个模糊的报错信息。要拿到完整的死锁现场，以前大家习惯看SHOW ENGINE INNODB STATUS的输出，但那是一大段文本，解析起来很痛苦。performance_schema提供了结构化的死锁记录，存储在data_lock_waits和相关的历史表中。

关键是要开启events_statements_history和events_transactions_history这两个consumer，它们会把每个线程最近执行的SQL和事务信息保留下来。当死锁发生时，被回滚的事务和获胜的事务，它们各自的SQL执行历史都能在这些历史表中找到。

你可以用下面这个思路来查询最近发生的死锁详情：

SELECT
    e.thread_id,
    e.event_id,
    e.event_name,
    e.source,
    e.timer_wait,
    e.lock_mode,
    e.lock_type,
    e.lock_data,
    e.object_schema,
    e.object_name,
    s.sql_text
FROM performance_schema.events_statements_history s
JOIN performance_schema.threads t ON s.thread_id = t.thread_id
JOIN performance_schema.data_locks l ON t.processlist_id = l.thread_id
LEFT JOIN performance_schema.events_statements_current e ON t.thread_id = e.thread_id
WHERE l.lock_status = 'WAITING'
ORDER BY s.timer_wait DESC;

但更直接的办法是查询performance_schema下的metadata_locks和相关的等待事件，结合sys库的innodb_lock_waits视图。sys库其实是performance_schema的易用封装，它已经帮你把复杂的关联逻辑写好了。直接查sys.innodb_lock_waits，你能看到等待事务和阻塞事务的详细信息，包括它们各自正在执行或最后执行的SQL语句。

除了InnoDB行锁，元数据锁也是线上故障的常客。一个DDL操作如果长时间拿不到MDL锁，会阻塞后续所有对同表的读写请求，瞬间把连接池打满。用performance_schema分析MDL锁等待，核心表是metadata_locks。这张表记录了当前所有活跃的MDL锁及其等待状态。

下面这条查询能快速定位MDL等待链：

SELECT
    waiting.OBJECT_SCHEMA,
    waiting.OBJECT_NAME,
    waiting.LOCK_TYPE,
    waiting.LOCK_STATUS,
    waiting.OWNER_THREAD_ID AS waiting_thread_id,
    waiting_p.PROCESSLIST_ID AS waiting_pid,
    waiting_p.PROCESSLIST_INFO AS waiting_query,
    blocking.OWNER_THREAD_ID AS blocking_thread_id,
    blocking_p.PROCESSLIST_ID AS blocking_pid,
    blocking_p.PROCESSLIST_INFO AS blocking_query
FROM performance_schema.metadata_locks waiting
LEFT JOIN performance_schema.metadata_locks blocking
    ON waiting.OBJECT_SCHEMA = blocking.OBJECT_SCHEMA
    AND waiting.OBJECT_NAME = blocking.OBJECT_NAME
    AND waiting.LOCK_STATUS = 'PENDING'
    AND blocking.LOCK_STATUS = 'GRANTED'
LEFT JOIN performance_schema.threads waiting_t
    ON waiting.OWNER_THREAD_ID = waiting_t.THREAD_ID
LEFT JOIN performance_schema.threads blocking_t
    ON blocking.OWNER_THREAD_ID = blocking_t.THREAD_ID
LEFT JOIN information_schema.processlist waiting_p
    ON waiting_t.PROCESSLIST_ID = waiting_p.ID
LEFT JOIN information_schema.processlist blocking_p
    ON blocking_t.PROCESSLIST_ID = blocking_p.ID
WHERE waiting.LOCK_STATUS = 'PENDING';

输出结果中，blocking_query如果是一个ALTER TABLE或者是一个长时间未提交的事务，那就是你要处理的根因。MDL锁的传播速度极快，一旦发现PENDING状态的MDL锁在堆积，必须立刻找到阻塞源头并kill掉，否则故障面会迅速扩大。

等到故障发生再去查performance_schema，虽然能解决问题，但已经造成了业务影响。更好的做法是把锁等待指标纳入常态化监控。你可以定时采集data_lock_waits和metadata_locks中的等待数量，一旦超过阈值就触发告警。同时，把等待时间超过一定秒数的事务详情记录下来，存入一张日志表，方便事后回溯。

例如，每隔10秒执行一次：

INSERT INTO dba_monitor.lock_wait_log
SELECT NOW(), waiting_trx_id, blocking_trx_id, waiting_query, blocking_query
FROM sys.innodb_lock_waits;

这样积累下来的数据，不仅能帮你发现偶发性的锁争用，还能分析出哪些表、哪些SQL模式最容易引发锁冲突，从而有针对性地优化索引设计或调整事务隔离级别。

锁等待的根因绝大多数情况下是SQL执行时没有走合适的索引，导致扫描范围过大，锁定了本不需要锁定的行。比如一个UPDATE语句的WHERE条件没有索引，它就会扫描全表并对所有行加锁，即使最终只更新一行。通过performance_schema的events_statements_history，你可以拿到引起锁等待的SQL的完整文本和执行计划，结合EXPLAIN分析，加上合适的索引后，锁范围缩小，锁等待自然消失。

还有一种常见情况是事务中SQL的执行顺序不一致。事务A先更新表X再更新表Y，事务B先更新表Y再更新表X，在高并发下极易产生死锁。通过data_lock_waits的等待链分析，你能还原出两个事务各自的加锁顺序，进而推动业务开发调整代码逻辑，统一加锁顺序。

performance_schema提供的锁分析能力远不止于此，它还能结合内存使用表、文件IO表等进行全链路性能诊断。掌握这套工具，你就不再是盲目地重启数据库或者无差别kill连接，而是精准打击，快速恢复业务，同时为后续的架构优化提供扎实的数据支撑。