Mysql数据库锁查询及处理

一、查看 InnoDB 事务及锁状

-- 查看所有活跃事务（含锁信息）
SELECT 
  trx_id,  -- 事务ID
  trx_state,  -- 事务状态（RUNNING/LOCK WAIT等）
  trx_started,  -- 事务启动时间
  trx_mysql_thread_id,  -- 线程ID
  trx_query,  -- 事务执行的SQL
  trx_wait_started,  -- 开始等待锁的时间
  trx_lock_structs  -- 事务持有的锁数量
FROM 
  information_schema.innodb_trx
WHERE 
  trx_state != 'COMMITTED';  -- 过滤已提交的事务

二、定位阻塞该事务的 “元凶”

执行以下 SQL，找到持有锁并阻塞当前事务的线程 ID 和 SQL：

-- 关联查询阻塞者信息
SELECT 
  b.trx_id AS blocking_trx_id,  -- 阻塞事务ID
  b.trx_mysql_thread_id AS blocking_thread_id,  -- 阻塞线程ID（关键）
  b.trx_query AS blocking_sql,  -- 阻塞者执行的SQL
  b.trx_started AS blocking_trx_started,  -- 阻塞事务启动时间
  TIMESTAMPDIFF(SECOND, b.trx_started, NOW()) AS blocking_trx_running_seconds  -- 阻塞事务运行时长
FROM 
  information_schema.innodb_lock_waits w
JOIN 
  information_schema.innodb_trx b ON w.blocking_trx_id = b.trx_id
JOIN 
  information_schema.innodb_trx r ON w.requesting_trx_id = r.trx_id
WHERE 
  r.trx_id = '16933311';  -- 替换为你的等待事务ID

三、kill 阻塞线程

 -- 替换为步骤二中查到的blocking_thread_id。假设阻塞线程ID是123
KILL 123;

四、检查是否有长事务持有锁, 如果有可以直接kill全部阻塞线程

SELECT 
  trx_id,
  trx_mysql_thread_id,
  trx_query,
  TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS running_seconds
FROM 
  information_schema.innodb_trx
WHERE 
  TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 60
ORDER BY running_seconds DESC;

五、开启SQL语句执行记录日志，并输出到文件

-- 开启
SET GLOBAL log_output = 'file';SET GLOBAL general_log = 'ON';
-- 查询日志文件位置
show variables like 'general_log_file';
-- 关闭
SET GLOBAL log_output = 'file';SET GLOBAL general_log = 'OFF';
truncate table mysql.general_log;