MySQL 运维中排查表锁、事务锁、死锁

这三条命令是 MySQL 锁问题排查的「三板斧」，掌握它们能快速定位绝大多数锁相关的性能问题和故障：

show open tables where in_use>0;
 
SELECT * FROM information_schema.INNODB_TRX\G;

show engine innodb status\G;

一、show open tables where in_use>0; —— 查看被占用的表（表级锁

这条命令用于查看当前正在被使用（持有表锁） 的表，in_use>0 表示表被至少一个会话占用（读锁 / 写锁），常用于排查「表被锁住导致 SQL 执行卡住」的问题。

典型输出：

字段说明：

• Database：数据库名；
• Table：表名；
• In_use：占用该表的会话数（1 表示 1 个会话占用，2 表示 2 个会话同时占用）；
• Name_locked：表名是否被锁定（0= 未锁定，1= 锁定，通常为 0，仅在重命名 / 删除表时会触发）。

实战场景

• 当执行 ALTER TABLE/DROP TABLE 卡住时，用这条命令排查是否有会话占用该表；
• 结合 show processlist; 可进一步找到占用表的会话 ID，进而 kill 掉阻塞的会话。

二、SELECT * FROM information_schema.INNODB_TRX\G; —— 查看活跃事务（行级锁）

查询 InnoDB 引擎下所有未提交的活跃事务，包括事务 ID、执行的 SQL、持有的锁、阻塞状态等，是排查「行级锁阻塞」「长事务」的核心命令（\G 用于格式化输出，比表格更易读）。

典型输出：

*************************** 1. row ***************************
                    trx_id: 123456  -- 事务ID（唯一标识）
                 trx_state: RUNNING  -- 事务状态（RUNNING/LOCK WAIT等）
               trx_started: 2026-01-23 10:00:00  -- 事务启动时间（排查长事务）
     trx_requested_lock_id: NULL  -- 若为LOCK WAIT，此处显示等待的锁ID
          trx_wait_started: NULL  -- 等待锁的开始时间（LOCK WAIT时非空）
                trx_weight: 2  -- 事务权重（影响死锁时的回滚选择）
       trx_mysql_thread_id: 123  -- 对应的MySQL会话ID（可结合show processlist）
                 trx_query: UPDATE user SET name='test' WHERE id=1  -- 事务执行的SQL
       trx_operation_state: NULL  -- 事务当前操作状态
         trx_tables_in_use: 1  -- 事务中使用的表数量
         trx_tables_locked: 1  -- 事务锁定的表数量
          trx_lock_structs: 1  -- 事务持有的锁结构数量

核心字段重点：

核心字段重点

• trx_state：
  • RUNNING：事务正常运行（未提交）；
  • LOCK WAIT：事务正在等待锁（被其他事务阻塞）；
• trx_started：若时间很久（如几小时），说明是长事务，易导致锁堆积；
• trx_mysql_thread_id：找到阻塞的会话 ID 后，执行 kill 123; 可终止事务；
• trx_query：直接看到导致锁的 SQL 语句，快速定位问题。

三、show engine innodb status\G; —— 查看 InnoDB 状态（含死锁日志）

输出 InnoDB 引擎的完整状态信息，包括最近一次死锁的详细记录、事务统计、锁等待、缓冲池状态等，是排查「死锁」的核心命令（MySQL 默认只保留最近一次死锁记录）。

关键输出：死锁记录解读

执行命令后，找到 LATEST DETECTED DEADLOCK 段（最近死锁），示例：

------------------------
LATEST DETECTED DEADLOCK
------------------------
2026-01-23 10:10:00 0x7f1234567890
*** (1) TRANSACTION:
TRANSACTION 123456, ACTIVE 10 sec updating
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 123, OS thread handle 140702345678900, query id 789 localhost root updating
UPDATE user SET balance = balance - 100 WHERE id = 1;  -- 事务1执行的SQL

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:  -- 事务1等待的锁
RECORD LOCKS space id 123 page no 45 n bits 72 index PRIMARY of table `test`.`user` trx id 123456 lock_mode X waiting  -- 等待行级排他锁（X锁）

*** (2) TRANSACTION:
TRANSACTION 123457, ACTIVE 15 sec updating
mysql tables in use 1, locked 1
2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 124, OS thread handle 140702345679000, query id 790 localhost root updating
UPDATE user SET balance = balance + 100 WHERE id = 1;  -- 事务2执行的SQL

*** (2) HOLDS THE LOCK(S):  -- 事务2持有的锁
RECORD LOCKS space id 123 page no 45 n bits 72 index PRIMARY of table `test`.`user` trx id 123457 lock_mode X  -- 持有行级排他锁（X锁）

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:  -- 事务2也在等待锁（死锁核心）
RECORD LOCKS space id 123 page no 45 n bits 72 index PRIMARY of table `test`.`user` trx id 123457 lock_mode X waiting

*** WE ROLL BACK TRANSACTION (1);  -- MySQL选择回滚事务1（权重小的）

死锁解读关键点

• TRANSACTION 1/2：两个互相阻塞的事务；
• WAITING FOR THIS LOCK：事务等待的锁类型（X 锁 = 排他锁，S 锁 = 共享锁）；
• HOLDS THE LOCK(S)：事务持有的锁；
• WE ROLL BACK TRANSACTION (1)：MySQL 自动回滚其中一个事务以解除死锁；
• 核心原因：两个事务互相持有对方需要的锁，且都不释放。

四、实战排查流程（锁 / 死锁问题）

当数据库出现 SQL 卡住、执行缓慢或报死锁错误时，按以下步骤排查：

1. 第一步：查被占用的表show open tables where in_use>0; → 确认是否有表被长期占用；
2. 第二步：查活跃事务SELECT * FROM information_schema.INNODB_TRX\G; → 找到长事务、阻塞的事务 ID 和 SQL；
3. 第三步：查死锁记录show engine innodb status\G; → 若有死锁，定位死锁的事务和 SQL；
4. 第四步：处理问题
   • 长事务 / 阻塞事务：kill 会话ID;（如 kill 123;）；
   • 死锁：优化 SQL（如调整更新顺序、缩小锁范围、加索引），避免互相锁。

死锁记录保留：MySQL 仅保留最近一次死锁记录，若需监控所有死锁，需开启死锁日志：

# my.cnf 配置
innodb_print_all_deadlocks = 1  # 开启所有死锁日志，记录到error log