mysql如何排查表锁冲突_技术教程

首先查看锁等待情况，通过information_schema.INNODB_TRX和performance_schema.data_lock_waits确定阻塞会话；接着查询长时间运行的事务，定位未提交事务的线程ID；再结合SHOW FULL PROCESSLIST分析会话状态、执行时长及SQL内容；最后检查MDL锁信息，识别DDL操作导致的阻塞。关键步骤依次为：查锁等待、找长事务、析进程列表、检MDL锁，最终定位并处理阻塞源头。

排查 MySQL 表锁冲突需要从当前锁状态、会话行为和 SQL 执行情况入手，快速定位阻塞源头。重点是查看哪些会话持有锁，哪些在等待，以及具体执行了什么语句。

查看当前锁等待情况

通过 information_schema.INNODB_TRX 和 performance_schema.data_locks（MySQL 8.0+）可以查看正在运行的事务和锁信息。

MySQL 8.0 及以上版本：

查询当前被阻塞的事务：

SELECT 
    r.trx_id waiting_trx_id,
    r.trx_mysql_thread_id waiting_thread,
    r.trx_query waiting_query,
    b.trx_id blocking_trx_id,
    b.trx_mysql_thread_id blocking_thread,
    b.trx_query blocking_query
FROM 
    performance_schema.data_lock_waits w
JOIN 
    information_schema.INNODB_TRX b ON b.trx_id = w.BLOCKING_ENGINE_TRANSACTION_ID
JOIN 
    information_schema.INNODB_TRX r ON r.trx_id = w.REQUESTING_ENGINE_TRANSACTION_ID;

这个结果会显示“谁在等”和“被谁阻塞”，便于快速定位问题会话。

检查长时间运行的事务

长时间未提交的事务最容易导致表锁堆积。查看所有活跃事务：

SELECT 
    trx_id, 
    trx_state, 
    trx_started, 
    trx_mysql_thread_id, 
    trx_query 
FROM 
    information_schema.INNODB_TRX 
ORDER BY 
    trx_started;

重点关注 trx_state 为 RUNNING 且 trx_started 时间很早的事务。这类事务可能忘了提交或执行了大事务，持续持有表锁。

记下对应的 trx_mysql_thread_id，用于后续分析或终止操作
结合 SHOW PROCESSLIST 查看该线程当前状态

分析会话与进程列表

使用 SHOW FULL PROCESSLIST 查看当前所有连接的执行状态：

SHOW FULL PROCESSLIST;

关注以下几列：

Command：如果是 Sleep 但事务未提交，可能应用没正确关闭事务
Time：运行时间过长的语句可能是全表扫描或缺少索引
State：如出现 Waiting for table metadata lock，说明被元数据锁阻塞
Info：正在执行的 SQL 语句，判断是否涉及 DDL 或大范围 DML

识别 DDL 操作引发的锁冲突

ALTER、DROP、RENAME 等 DDL 语句会申请表级元数据锁（MDL），容易被前面的事务阻塞。

如果发现某个 DDL 一直卡住，执行：

SELECT * FROM performance_schema.metadata_locks WHERE OWNER_THREAD_ID IN (
    SELECT THREAD_ID FROM performance_schema.threads WHERE PROCESSLIST_ID = 
);

也可以全局查看 MDL 状态：

SELECT 
    OBJECT_SCHEMA, 
    OBJECT_NAME, 
    LOCK_TYPE, 
    LOCK_DURATION, 
    LOCK_STATUS, 
    OWNER_THREAD_ID 
FROM 
    performance_schema.metadata_locks 
WHERE 
    OBJECT_NAME = 'your_table_name';

若 LOCK_STATUS 为 PENDING，表示在等待锁释放。

基本上就这些常用方法。关键是结合 INNODB_TRX、data_locks 和 PROCESSLIST 快速定位持有锁的会话，并判断是否需要 kill 掉异常连接。日常建议避免长事务，DDL 操作尽量在低峰期执行。