Lock wait timeout exceeded 问题处理步骤

当遇到 “Lock wait timeout exceeded; try restarting transaction” 错误时，通常是由于数据库事务长时间等待锁资源超时导致。以下是系统的解决方案：

---

1. 诊断阻塞源

首先确定哪个事务持有锁并阻塞了其他操作。

MySQL 示例：

-- 查看当前运行的事务和锁状态
SHOW ENGINE INNODB STATUS;  -- 在结果中查找 "TRANSACTIONS" 和 "LOCK WAIT" 部分

-- 查询阻塞的进程
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 information_schema.innodb_lock_waits w
JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

---

2. 终止阻塞事务

确认阻塞事务后，终止其进程（需谨慎操作）。

-- 根据查询结果获取 blocking_thread
KILL <blocking_thread_id>;  -- 替换为实际的线程ID

---

3. 临时调整超时参数

增大锁等待超时时间（临时缓解，需重启事务生效）。

-- 查看当前超时设置（默认50秒）
SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';

-- 动态调整超时时间（例如调整为120秒）
SET GLOBAL innodb_lock_wait_timeout = 120; 

---

4. 优化事务设计

根本解决需优化事务：

• 缩短事务时间：避免在事务中执行远程调用、文件IO等耗时操作。
• 按需加锁：使用 SELECT ... FOR UPDATE 时确保精确命中索引，缩小锁定范围。
• 拆分大事务：将大事务拆分为多个小事务，减少单次锁持有时间。
• 使用低隔离级别：如从 REPEATABLE READ 改为 READ COMMITTED（需评估一致性影响）。
• 乐观锁替代：通过版本号控制并发更新，减少锁竞争。

---

5. 检查索引与查询效率

• 确保更新/删除条件有索引：无索引会导致全表扫描，引发大量行锁。
• 优化慢查询：使用 EXPLAIN 分析执行计划，减少全表扫描。

---

6. 代码与连接管理

• 及时提交/回滚：确保代码中事务正确关闭，避免未提交事务残留。
• 连接池配置：检查连接泄漏，合理设置空闲超时。

---

7. 监控与预防

• 启用数据库监控：定期检查 SHOW ENGINE INNODB STATUS 和锁等待情况。
• 设置报警：对长事务和锁等待时间设置阈值报警。

---

总结

该问题的核心是锁竞争，需通过诊断工具定位阻塞源，及时终止异常事务，并结合事务优化、索引调整等措施长期规避。临时调整超时参数仅作为应急手段，优化数据库设计才是根本解决方案。