大家好，我是枫夜求索阁。话不多说，先来一个全局思维导图：

一、背景

在使用 MySQL 数据库时，Lock wait timeout exceeded; try restarting transaction 是一个常见且令人头疼的错误。这个错误通常出现在多个事务同时访问同一资源时，一个事务在等待获取锁的过程中超过了设定的超时时间。例如，当一个事务正在更新某一行数据时，另一个事务也尝试更新同一行数据，就会进入等待状态。如果等待时间超过了系统变量 innodb_lock_wait_timeout 的设定值，MySQL 就会抛出这个错误，并回滚等待的事务。该错误不仅影响程序的执行效率，还可能导致系统性能下降甚至卡死。

二、版本

MySQL 5.5 版本以上，可通过 information_schema.innodb_trx 查询当前运行的全部事务；

而在 5.5 版本以下，该表不存在。

另外，在 MySQL 8.0.13 版本中，innodb_locks 表由 performance_schema.data_locks 表所代替，innodb_lock_waits 表则由 performance_schema.data_lock_waits 表代替。

不同版本的 MySQL 在处理锁等待超时问题时，可使用的查询表有所不同，需要根据具体版本进行相应的操作。

三、问题原因

接下来为大家分析一下锁超时的几种情况：

3.1 事务锁等待

当一个事务持有锁，而另一个事务请求相同资源的锁时，请求者就会进入等待状态。如果等待时间超过了 innodb_lock_wait_timeout 的设定值，就会报错。例如，事务 A 对记录 C 进行更新操作且未提交时，事务 B 也对记录 C 进行更新操作，此时 B 会等待 A 提交事务释放行锁，若等待时间过长就会出现锁等待超时错误。

3.2 死锁

两个或多个事务互相等待对方释放锁，导致锁等待超时。比如事务 A 先获取锁 1 再获取锁 2，而事务 B 先获取锁 2 再获取锁 1，那么它们可能陷入死锁状态，最终导致锁等待超时。

3.3 长时间运行的查询

长时间运行的查询可能会持有锁很长时间，导致其他事务无法获取锁。例如，在事务中包含性能较差的查询 SQL，会使同一个事务中的其他 DML 无法及时释放占用的行锁，引起行锁等待。

3.4 事务设计不合理

事务执行时间过长、锁定资源范围过大或事务逻辑复杂。例如，在同一事务内先后对同一条数据进行插入和更新操作，或者单个事务中包含大量 SQL，通常是由于在事务代码中加入 for 循环导致，虽然单个 SQL 运行很快，但是 SQL 数量一大，事务就会很慢。

3.5 锁等待超时时间过短

MySQL 默认的锁等待超时时间可能不足以处理某些复杂的查询或并发情况。MySQL 数据库采用 InnoDB 模式时，默认参数 innodb_lock_wait_timeout 设置锁等待的时间是 50s，一旦数据库锁超过这个时间就会报错。

3.6 磁盘问题

极少出现的情形，比如存储突然离线，SQL 执行会卡在内核调用磁盘的步骤上，一直等待，事务无法提交。

3.7 数据库内存不足

导致无法执行写操作，例如在疲劳测试（压测 12 小时）过程中，使用 10 并发进行压测，每秒差不多 15 笔申请调用，可能会使数据库内存达到 100%，从而出现锁等待超时问题。

四、解决方案

遇到锁超时的问题，我们有几种解决方案：

4.1 重启事务

错误信息中的 try restarting transaction 提示我们，遇到超时错误时，最简单的做法就是重启事务，再次尝试执行操作。以下是 Java 代码示例：

try {
    // 执行数据库操作 
} catch (SQLException e) {
    if ("Lock wait timeout exceeded".equals(e.getMessage())) {
        // 重启事务，再次尝试 
        retryTransaction();
    }
}

4.2 优化事务管理

  • 减少事务的持续时间：在处理数据时，尽量减少事务的持续时间，尽快释放锁，避免其他事务等待。例如，将非数据库交互操作（如接口调用、文件操作等）放在事务之外。
  • 使用独立事务：每个事务尽量独立处理自己的业务，避免事务之间的冲突和等待。
  • 使用低级别的隔离级别：降低事务的隔离级别，可以减少锁的粒度，提高并发性能。但需要注意的是，不同的隔离级别对数据一致性的保证程度不同，需要根据具体业务需求进行选择。

4.3 调整锁等待超时设置

可以通过修改 innodb_lock_wait_timeout 参数来延长 MySQL 等待锁的时间。例如，设置全局参数：

SET GLOBAL innodb_lock_wait_timeout = 120;

对于当前会话，可以使用：

SET SESSION innodb_lock_wait_timeout = 120;

不过，将超时时间设置过长可能会使等待事务增多导致堆积问题，需要谨慎调整。

4.4 分析并优化锁竞争

  • 优化查询语句：使用合适的索引，在经常被查询的列上创建索引，可以大大提高查询性能，减少锁等待的时间。尽量避免全表扫描，如果查询涉及的数据量非常大，可以考虑分批次查询，或者使用更快的查询方式。避免使用不必要的锁，在事务中，只锁定需要修改的数据行，不要锁定整个表。
  • 合理设计事务顺序：确保所有事务以相同的顺序获取锁，从而减少死锁的可能性。这可以通过编码约定来实现。
  • 降低事务并发度：当有大量事务并发访问相同资源时，死锁的发生概率会增加。可以通过调整事务并发度或优化数据库设计来缓解，例如合理配置数据库连接池。

4.5 查找并终止持有锁的操作

可以通过 information_schema 中的相关表来查找被锁的语句和持有锁的事务。常用的表有：

  • innodb_trx：当前运行的所有事务。
  • innodb_locks：当前出现的锁。
  • innodb_lock_waits：锁等待的对应关系。
    例如，使用以下 SQL 语句查看当前运行的所有事务：
SELECT * FROM information_schema.innodb_trx;

如果发现有长时间未提交的事务，可以使用 kill 命令终止该事务的线程，如 kill 线程 id 号。但在执行 kill 命令前，务必确认这些进程是可以安全中断的，错误地结束进程可能会导致数据不一致或其他问题。

4.6 优化 SQL 语句

检查并优化导致长时间锁定的 SQL 语句。通过分析慢查询日志，找出那些执行时间长、占用资源多的查询，并对其进行优化。这可能涉及重写查询、添加或调整索引等。例如，对于级联更新 SQL（如 update A set ... where ... in (select B)），这类 SQL 不仅会占用 A 表上的行锁，也会占用 B 表上的行锁，当 SQL 执行较久时，很容易引起 B 表上的行锁等待，需要对其进行优化。

4.7 减少事务大小

如果事务处理的数据量过大，可以考虑将其拆分为多个较小的事务。这样可以减少锁定时间和范围，从而降低出现“Lock wait timeout exceeded”错误的可能性。

4.8 使用适当的锁机制

根据业务需求选择合适的锁类型（如行锁或表锁）。尽量避免不必要的表级锁定，尤其是在高并发环境下。例如，在可以使用行锁的情况下，不要使用表锁。

4.9 定期维护数据库

包括定期进行数据清理、重建索引、分析表结构等操作，以保持数据库的最佳性能状态。例如，删除无用数据可以释放数据库空间，提高数据库的读写性能。

4.10 监控和预警

建立完善的数据库监控体系，及时发现和处理潜在的问题。可以设置告警规则，在锁等待时间接近阈值时发出通知。例如，使用数据库监控工具实时监控 innodb_trx、innodb_locks、innodb_lock_waits 等表的状态，当发现异常时及时采取措施。

五、结论

“Lock wait timeout exceeded” 是 MySQL 数据库中一个常见且棘手的问题，它可能由多种原因引起，如事务锁等待、死锁、长时间运行的查询、事务设计不合理等。在解决该问题时，我们需要根据具体情况采取相应的措施，如重启事务、优化事务管理、调整锁等待超时设置、分析并优化锁竞争等。同时，为了预防该问题的发生，我们需要定期维护数据库，建立完善的监控和预警体系。在实际应用中，也需要不断地进行测试和优化，以确保数据库的稳定运行和高效性能。