本篇文章带大家来详细拆解一下这个问题。MySQL锁等待超时是一个常见的并发问题，排查和解决需要从数据库和代码两个层面入手。

干货分享，感谢阅读

问题本质分析

这个报错 Lock wait timeout exceeded; try restarting transaction 的含义是：当前事务在尝试获取某个锁（通常是行锁）时，等待了超过 innodb_lock_wait_timeout（默认50秒）的时间，仍然无法获取，于是MySQL自动回滚了当前事务。

核心原因：另一个事务长时间持有了它需要的锁，并且没有及时提交或回滚。

排查与定位步骤 (从数据库到代码)

排查就像一个侦探游戏，目标是找到那个“凶手”事务（持有锁不释放的事务）和“受害者”事务（被阻塞的事务）。

第1步：数据库层面 - 立即诊断当前状态

当报错发生时，第一时间连接到数据库，查看当前的锁和事务状态。

1. 查看当前正在进行的锁信息
   执行以下SQL语句，这是排查锁问题最关键的命令：

   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,
       b.trx_started AS blocking_start_time,
       TIMESTAMPDIFF(SECOND, b.trx_started, NOW()) AS blocking_duration_sec,
       pl.lock_mode AS lock_mode,
       pl.lock_type AS lock_type,
       pl.lock_table AS locked_table,
       pl.lock_index AS locked_index
   FROM information_schema.innodb_lock_waits w
   INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
   INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id
   INNER JOIN information_schema.innodb_locks pl ON pl.lock_id = w.blocking_lock_id;
   

   关键字段解读：

   • blocking_thread: “凶手”事务的数据库连接ID。这是最重要的信息。
   • blocking_query: “凶手”事务正在执行的SQL（有时可能是NULL，表示它处于空闲状态，但持有锁）。
   • waiting_query: “受害者”事务（也就是你报错的事务）正在尝试执行的SQL。
   • blocking_duration_sec: “凶手”事务已经运行了多久。时间过长通常意味着有问题。

2. 查看所有活跃事务
   如果上一条命令没有结果，可以查看所有活跃事务：

   SELECT 
       trx_id, 
       trx_state, 
       trx_started, 
       TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS duration_sec,
       trx_mysql_thread_id AS thread_id,
       trx_query AS query,
       trx_operation_state AS operation_state
   FROM information_schema.innodb_trx 
   ORDER BY trx_started ASC;
   

   • 寻找那些 duration_sec 非常大、trx_state 为 RUNNING 但 trx_query 为 NULL 的事务。这些通常是忘记提交/回滚的长事务，是锁等待的罪魁祸首。

3. 定位到具体的数据库连接
   拿到 blocking_thread（例如，ID为 12345）后，你可以查看这个连接的详细信息：

   SELECT * FROM information_schema.PROCESSLIST WHERE ID = 12345;
   

   这可以告诉你这个连接是从哪个主机发起的、用户是谁、已经执行了多久，帮助你进一步定位到应用和代码。

第2步：代码层面 - 根据数据库线索定位问题

拿到 blocking_thread 和可能的SQL语句后，回到你的应用程序日志中寻找线索。

1. 查找对应的业务操作：

   • 根据 blocking_query SQL语句中的表名和操作类型（INSERT/UPDATE/DELETE），定位到代码中的DAO层（Mapper）方法。
   • 根据数据库连接的主机、用户和时间点，去对应应用服务器的日志中查找同一时间点执行的业务请求。日志中必须记录业务的唯一ID和SQL执行的traceId，否则很难追踪。

2. 分析常见代码问题：

   • 未提交的事务：代码中开启了事务（@Transactional 或手动 beginTransaction()），但在执行完成后没有提交。例如：方法内部吞掉了异常，导致事务无法回滚；或者复杂的业务逻辑中，事务范围过大，忘记提交。
   • 大事务：一个事务中包含了过多的SQL操作（例如循环中多次update/insert）、耗时极长的业务计算（如文件处理、远程调用）、或者等待用户输入。这会导致锁持有时间过长。
   • 事务嵌套不当：多个方法的事务传播属性（Propagation）设置不合理，导致事务范围超出预期。
   • 并发处理不当：对同一条数据（如账户余额、商品库存）进行了高并发的更新，而没有做好并发控制。

解决方案

根据排查出的根本原因，选择相应的解决方案。

1. 应急处理：终止阻塞事务

如果问题正在发生，需要立即恢复服务，可以强制杀死“凶手”事务的连接。

-- 先用 PROCESSLIST 找到连接的ID和正在执行的操作
SHOW PROCESSLIST;
-- 或使用第一步查到的 blocking_thread ID
KILL 12345; -- 12345 就是 blocking_thread 的值

注意：KILL 命令会回滚该连接正在执行的事务，释放其持有的所有锁。请确保了解这一操作的业务影响。

2. 优化代码与设计（根本解决）

1. 缩小事务范围：

   • 将不必要的操作移出事务。例如，查询操作、远程调用（RPC）、文件IO等耗时操作，尽量不要放在数据库事务中。
   • 遵循“短事务”原则，尽快提交事务，释放锁。

   反例（事务范围过大）：

   @Transactional
   public void processOrder(Order order) {
       // 1. 业务校验（非DB操作）
       // 2. 远程调用库存服务（耗时）
       inventoryService.checkStock();
       // 3. 写本地数据库
       orderDao.insert(order);
       // ... 其他操作
       // 事务直到方法结束才提交，锁持有时间包含了远程调用的时间！
   }
   

   正例（拆分事务）：

   // 方法1：先做非事务操作和准备
   public void preProcessOrder(Order order) {
       inventoryService.checkStock();
       // ... 其他非事务操作
   }
   
   // 方法2：只负责核心的数据库操作，事务范围很小
   @Transactional
   public void createOrder(Order order) {
       orderDao.insert(order);
   }
   
   // 主流程调用
   public void mainProcess() {
       preProcessOrder(order);
       createOrder(order);
   }
   

2. 避免长事务：

   • 在业务逻辑中，避免在事务内进行复杂的循环更新。可以考虑批量操作，减少锁的持有时间。
   • 绝对避免在事务内进行用户交互操作（如等待用户确认）。

3. 优化SQL和索引：

   • 确保你的 UPDATE/DELETE 语句都使用了索引来定位数据。没有索引的查询会升级为表锁，极易导致严重的锁等待。
   • 使用 EXPLAIN 分析慢查询，优化SQL执行效率。

4. 调整锁超时时间（临时缓解）：

   • 如果确实存在需要较长时间持有锁的合理场景，可以适当调整超时时间（治标不治本）。

   SET GLOBAL innodb_lock_wait_timeout = 120; -- 单位是秒
   

   注意：盲目调高此值可能会导致大量连接挂起，耗尽数据库连接池，使系统失去响应。

5. 使用乐观锁或悲观锁控制业务并发：

   • 悲观锁：在代码中显式使用 SELECT ... FOR UPDATE 来提前锁定资源。适用于冲突频繁的场景。但要非常小心，因为它会主动引入锁竞争。
   • 乐观锁：在表中增加一个 version 字段，更新时带上版本号判断。

     UPDATE table_name SET column1 = new_value, version = version + 1 WHERE id = #{id} AND version = #{old_version};
     

     如果更新条数为0，说明期间数据被修改过，业务代码中进行重试或报错。这是更推荐的方式，避免了数据库层面的锁等待。

总结与流程图

排查流程总结：

1. 发现问题：应用日志出现 Lock wait timeout exceeded 错误。
2. 连接数据库：立刻使用 information_schema 库查询锁等待和活跃事务信息。
3. 定位元凶：找到 blocking_trx_id 和 blocking_thread。
4. 分析原因：查看阻塞事务的SQL和状态，判断是空闲事务、慢查询还是大事务。
5. 应急处理：必要时使用 KILL 命令终止阻塞连接。
6. 代码修复：根据根本原因优化代码，缩短事务范围、优化SQL、引入乐观锁等。

预防措施：

• 在开发阶段就建立良好的事务设计规范。
• 在应用日志中清晰记录事务的开始、提交/回滚以及耗时。
• 对数据库进行监控，设置告警，当有长事务（例如超过30秒）出现时及时通知。

转载自CSDN-专业IT技术社区

原文链接：https://blog.csdn.net/qq_39126115/article/details/151157367