mysql 8.0查看正在执行的事务锁

SELECT * FROM performance_schema.data_lock_waits;
-- MySQL 8.0+
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
FROM performance_schema.data_lock_waits w
INNER JOIN information_schema.INNODB_TRX b
ON b.trx_id = w.BLOCKING_ENGINE_TRANSACTION_ID
INNER JOIN information_schema.INNODB_TRX r
ON r.trx_id = w.REQUESTING_ENGINE_TRANSACTION_ID;

一、查看等待锁情况

 

二、终止阻塞者
找到 blocking_thread（如 1000），执行

waiting_trx_id	waiting_thread	waiting_query	blocking_trx_id	blocking_thread	blocking_query
12345	1001	UPDATE ...	12344	1000	SELECT ... FOR UPDATE

 

三、终止等待者（不推荐，除非必要）
如果某个等待事务本身有问题（如应用卡死），也可以终止它：

KILL 1001; -- 终止等待中的线程

⚠️ 注意：这只会终止等待者，阻塞者仍然存在，其他事务仍可能被阻塞。

四、其他辅助命令

SHOW PROCESSLIST;
-- 或
SELECT * FROM information_schema.PROCESSLIST;

关注：

• State: 是否为 Locked, Waiting for ...
• Time: 执行时间过长的连接

杀死所有睡眠连接（可选）

-- 脚本方式生成 KILL 命令
SELECT CONCAT('KILL ', id, ';') 
FROM information_schema.PROCESSLIST 
WHERE Command = 'Sleep' AND Time > 60;

自动化脚本示例（紧急处理）

-- 一键终止所有阻塞者（谨慎使用！）
SELECT CONCAT('KILL ', blocking_thread, ';') 
FROM (
    SELECT b.trx_mysql_thread_id AS blocking_thread
    FROM performance_schema.data_lock_waits w
    INNER JOIN information_schema.INNODB_TRX b 
        ON b.trx_id = w.BLOCKING_ENGINE_TRANSACTION_ID
) t;

 

简单的步骤:

复现时可以看下show full processlist看下请求，执行这几个SQL看下锁情况

查询 正在执行的事务：

SELECT * FROM information_schema.INNODB_TRX

查看正在锁的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

查看等待锁的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

找到后评估是否可以kill +sql id