解决MySQL锁等待超时：Lock wait timeout exceeded; try restarting transaction

引言

在使用MySQL 数据库 时，有时会遇到 Lock wait timeout exceeded; try restarting transaction 的错误。这个错误意味着一个事务在等待获取锁的过程中超过了设置的锁等待超时时间。下面我们将分析这个错误的原因，并给出一些实用的解决方案。

错误原因分析

1. 长时间运行的查询 ：一个长时间运行的查询可能会持有锁很长时间，导致其他事务无法获取锁。
2. 死锁 ：两个或多个事务互相等待对方释放锁，导致锁等待超时。
3. 锁等待超时时间过短 ：MySQL默认的锁等待超时时间可能不足以处理某些复杂的查询或并发情况。

解决方案

1. 优化查询

• 使用索引 ：确保查询中涉及的字段都有合适的索引，以减少查询时间和锁持有时间。
• 减少锁定范围 ：尽量缩小锁定范围，避免长时间持有不必要的锁。
• 避免使用SELECT FOR UPDATE ：如果可能，尽量避免使用 SELECT FOR UPDATE ，因为它会持有锁直到事务结束。

2. 调整锁等待超时时间

• 增加 innodb_lock_wait_timeout 值 ：可以通过增加 innodb_lock_wait_timeout 的值来延长锁等待时间。例如，将其设置为120秒：

SET GLOBAL innodb_lock_wait_timeout = 120;

[mysqld]
innodb_lock_wait_timeout = 120

SET TRANSACTION ISOLATION LEVEL READ COMMITTED, LOCKS=IMMEDIATE FOR UPDATE;
SET TRANSACTION innodb_lock_wait_timeout = 120;

3. 避免死锁

• 分析死锁 日志 ：启用MySQL的死锁日志功能，分析死锁日志以找出导致死锁的原因。
• 使用 SHOW ENGINE INNODB STATUS ：运行 SHOW ENGINE INNODB STATUS 命令可以获取InnoDB引擎的状态信息，包括死锁信息。
• 避免多个事务互相等待 ：设计数据库和事务逻辑时，尽量避免多个事务互相等待对方释放锁的情况。