6.10.5 事务与锁等待问题诊断

围绕事务与锁等待问题，运维需形成“现象识别 → 定位诊断 → 处置止损 → 复盘优化”的闭环。本节提供原理草图、诊断命令、排错步骤与可操作练习。

原理草图（锁等待与死锁链路）

关键现象与基线对比
- 症状：事务响应变慢、连接数升高、TPS下降、CPU/IO正常、应用报错 lock wait timeout 或 deadlock found
- 基线指标：TPS、活跃事务数、锁等待时间、死锁次数、事务平均时长

诊断与排错流程（含命令与解释）
1) 快速查看阻塞链与长事务

-- 1. 查看当前会话与状态
SHOW PROCESSLIST;

-- 2. 查看长事务（按活跃时间排序）
SELECT trx_id, trx_mysql_thread_id, trx_started, 
       TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS trx_age_s,
       trx_query
FROM information_schema.innodb_trx
ORDER BY trx_age_s DESC
LIMIT 10;

-- 3. 预期：识别 trx_age_s 过长且 state/查询异常的事务

2) 查看死锁详情与锁类型

-- 查看InnoDB引擎状态（死锁、锁等待、事务）
SHOW ENGINE INNODB STATUS\G;

-- 预期：在 "LATEST DETECTED DEADLOCK" 中看到两条事务及锁资源
-- 关注：waits for ... lock, record lock, gap lock, next-key lock

3) 串联锁等待关系（阻塞链）

-- 通过INNODB_LOCK_WAITS关联阻塞与被阻塞
SELECT
  r.trx_id AS waiting_trx_id,
  r.trx_mysql_thread_id AS waiting_thread,
  b.trx_id AS blocking_trx_id,
  b.trx_mysql_thread_id AS blocking_thread,
  l.lock_table, l.lock_index, l.lock_type, l.lock_mode
FROM information_schema.innodb_lock_waits w
JOIN information_schema.innodb_trx r ON w.requesting_trx_id = r.trx_id
JOIN information_schema.innodb_trx b ON w.blocking_trx_id = b.trx_id
JOIN information_schema.innodb_locks l ON w.requested_lock_id = l.lock_id;

4) 定位慢SQL与长事务根因

-- 开启慢查询（若未开启）
SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 1;

-- 查看慢查询位置
SHOW VARIABLES LIKE 'slow_query_log_file';

-- 预期：识别长事务SQL、全表扫描、无索引更新

典型根因与处置（含命令）
1) 长事务未提交
- 处置：通知应用提交/回滚，必要时终止阻塞线程

-- 根据阻塞线程ID强制终止
KILL 12345;
-- 预期：被阻塞的事务继续执行，锁等待下降

2) 锁范围过大/锁升级
- 处置：补齐索引、拆分批量更新、避免全表扫描

-- 查看执行计划，确认索引使用
EXPLAIN UPDATE orders SET status=2 WHERE user_id=1001;

-- 创建缺失索引（示例）
ALTER TABLE orders ADD INDEX idx_user_id(user_id);

3) 死锁
- 处置：统一访问顺序、缩短事务、避免隐式转换

-- 示例：统一锁顺序，按主键升序更新
UPDATE t1 SET c=c+1 WHERE id IN (1,2,3) ORDER BY id;
UPDATE t2 SET c=c+1 WHERE id IN (1,2,3) ORDER BY id;

4) 元数据锁（MDL）阻塞
- 处置：避免长事务与DDL并发，使用在线DDL

-- 观察MDL等待
SHOW PROCESSLIST;
-- 预期：State 为 "Waiting for table metadata lock"

-- 在线DDL示例（InnoDB）
ALTER TABLE t1 ADD COLUMN c2 INT, ALGORITHM=INPLACE, LOCK=NONE;

5) 主从复制阻塞
- 处置：优化热点写入、拆分事务、避免从库长事务

SHOW SLAVE STATUS\G;
-- 关注：Seconds_Behind_Master, Slave_SQL_Running_State

安装/启用观测组件（便于监控锁等待）

# 以RHEL/CentOS为例安装MySQL客户端工具
yum install -y mysql

# 连接示例
mysql -h 127.0.0.1 -P 3306 -u admin -p

# 预期：可执行SHOW ENGINE INNODB STATUS等命令

关键指标与监控命令

SHOW GLOBAL STATUS LIKE 'Innodb_row_lock%';
-- Innodb_row_lock_waits: 等待次数
-- Innodb_row_lock_time: 总等待时间
-- Innodb_row_lock_time_avg: 平均等待时间

SHOW GLOBAL STATUS LIKE 'Innodb_deadlocks';

应急止损策略（示例流程）
1. 限流/熔断应用写入
2. 找到阻塞链头部事务并评估回滚影响
3. 必要时 KILL 阻塞线程
4. 复盘与优化索引/事务边界

练习（可在测试库执行）
1) 构造锁等待

-- 会话A
BEGIN;
UPDATE t1 SET c=c+1 WHERE id=1;

-- 会话B
BEGIN;
UPDATE t1 SET c=c+1 WHERE id=1; -- 将进入等待

-- 练习：用SHOW PROCESSLIST和innodb_lock_waits定位阻塞链

2) 构造死锁

-- 会话A
BEGIN;
UPDATE t1 SET c=c+1 WHERE id=1;
UPDATE t2 SET c=c+1 WHERE id=1;

-- 会话B
BEGIN;
UPDATE t2 SET c=c+1 WHERE id=1;
UPDATE t1 SET c=c+1 WHERE id=1;

-- 练习：通过SHOW ENGINE INNODB STATUS\G查看死锁详情

3) 优化验证

-- 为热点条件建立索引后观察锁等待减少
ALTER TABLE t1 ADD INDEX idx_id(id);

结论要点
- 事务越短、锁持有越少，锁等待与死锁概率越低
- 统一访问顺序与合理索引是核心优化手段
- 监控锁等待指标并建立处置流程，是稳定性的关键保障