如何在MySQL中通过Performance Schema排查行锁争用-mysql教程-PHP中文网

能直接用performance_schema.data_lock_waits查到谁在等谁，前提是mysql≥5.7且已启用wait/lock/innodb相关instrument；该表需关联data_locks才能获取表名、索引、lock_data等关键信息，用于精准定位间隙锁或行锁阻塞。

如何在mysql中通过performance schema排查行锁争用

能直接用 performance_schema.data_lock_waits 查到谁在等谁，前提是 MySQL ≥ 5.7 且锁采集已开启——否则表里没数据不是 SQL 写错了，是开关根本没打开。

很多排查卡在这一步：表存在、SQL 能执行、结果却为空。根本原因不是权限或语法问题，而是相关 instrument 默认关闭。

运行 SELECT * FROM performance_schema.setup_instruments WHERE NAME LIKE 'wait/lock/innoDB%';，检查 ENABLED 和 Timed 列是否都为 YES
若为 NO，需在 my.cnf 中添加：performance_schema_instrument = 'wait/lock/innoDB%=ON'，然后重启 mysqld
MySQL 8.0+ 支持部分动态开启，但 wait/lock/innoDB/row_lock 必须静态配置才稳定生效
别忘了同时开 consumers：UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE '%locks%';

data_lock_waits 只存 ID 关系，不带表名、索引名、锁值——单查它等于白查。

MySQL

编写正确的MySQL查询，避免字符集、索引和锁方面的常见陷阱。

下载

核心关联写法：SELECT w.OBJECT_SCHEMA, w.OBJECT_NAME, w.INDEX_NAME, w.LOCK_TYPE, w.LOCK_MODE, w.LOCK_STATUS, w.BLOCKING_ENGINE_TRANSACTION_ID, w.REQUESTING_ENGINE_TRANSACTION_ID, d.LOCK_DATA FROM performance_schema.data_lock_waits w JOIN performance_schema.data_locks d ON w.REQUESTING_ENGINE_LOCK_ID = d.ENGINE_LOCK_ID;
LOCK_MODE 值要会读：S 是共享锁，X 是排他锁，NEXT_KEY 表示临键锁（记录 + 间隙），GAP 表示纯间隙锁
LOCK_DATA 是关键：显示具体主键值（如 12345）或范围（如 10, 20），直接暴露热点行或争用间隙
注意：BLOCKING_ENGINE_TRANSACTION_ID ≠ INFORMATION_SCHEMA.INNODB_TRX.trx_id，需通过 performance_schema.threads 关联 PROCESSLIST_ID 才能找到阻塞会话

知道谁在等还不够，得知道阻塞方到底卡在哪条语句上——它可能是个未提交事务，也可能正执行一条慢 UPDATE。

先拿到阻塞者的 PROCESSLIST_ID：SELECT blocking_pid FROM sys.innodb_lock_waits;（sys 视图更易读）
再查其最近执行的 SQL：SELECT sql_text FROM performance_schema.events_statements_history WHERE thread_id = (SELECT thread_id FROM performance_schema.threads WHERE processlist_id = ?) ORDER BY event_id DESC LIMIT 1;
如果 events_statements_history 没数据，说明该线程没开启语句采集，需提前设 setup_consumers.events_statements_history_long = YES
特别注意：某些 ORM 自动生成的 FOR UPDATE 查询，本意是只读却加了写锁，会引发连锁阻塞

看到大量锁等待，不能默认就是行锁问题——MDL（元数据锁）或隐式表锁常被误判为行锁争用。

查 metadata_locks 表：SELECT * FROM performance_schema.metadata_locks WHERE LOCK_STATUS = 'PENDING';，若发现 LOCK_TYPE = 'EXCLUSIVE' 且 OBJECT_TYPE = 'TABLE'，大概率是 DDL 阻塞了 DML
看 data_locks 的 LOCK_TYPE 字段：'TABLE' 是表锁，'RECORD' 才是行锁；大量 RECORD + 同一 INDEX_NAME 才算典型行锁争用
配合 SHOW ENGINE INNODB STATUS\G 中的 TRANSACTIONS 部分交叉验证：若多个事务反复卡在同一个二级索引（如 idx_user_id），且 lock struct(s) 数量激增，基本可锁定热点行
别忽略隔离级别影响：间隙锁（GAP）只在 REPEATABLE-READ 下生效，READ-COMMITTED 下无间隙锁，行为差异极大