MySQL 事务没有提交导致 锁等待 Lock wait timeout exceeded

本文介绍了一种解决MySQL中因长时间未提交的只读事务导致的Lockwaittimeoutexceeded问题的方法。通过查询information_schema.innodb_trx表找到未提交的事务,并使用KILL命令终止对应的线程。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

转自:http://blog.youkuaiyun.com/tanggao1314/article/details/53097561


Lock wait timeout exceeded

当一个SQL执行完了,但未COMMIT,后

select * from information_schema.innodb_trx 之后找到了一个一直没有提交的只读事务,

kill 到了对应的线程后ok 了。



MySQL> select * from information_schema.innodb_trx;

+--------+-----------+---------------------+-----------------------+------------------+------------+---------------------+-----------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+
| trx_id | trx_state | trx_started         | trx_requested_lock_id | trx_wait_started | trx_weight | trx_mysql_thread_id | trx_query | trx_operation_state | trx_tables_in_use | trx_tables_locked | trx_lock_structs | trx_lock_memory_bytes | trx_rows_locked | trx_rows_modified | trx_concurrency_tickets | trx_isolation_level | trx_unique_checks | trx_foreign_key_checks | trx_last_foreign_key_error | trx_adaptive_hash_latched | trx_adaptive_hash_timeout |
+--------+-----------+---------------------+-----------------------+------------------+------------+---------------------+-----------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+
| F663B  | RUNNING   | 2016-11-09 10:15:52 | NULL                  | NULL             |         10 |               14310 | NULL      | NULL                |                 0 |                 0 |                7 |                  1248 |               4 |                 3 |                       0 | REPEATABLE READ     |                 1 |                      1 | NULL                       |                         0 |                      9999 |
+--------+-----------+---------------------+-----------------------+------------------+------------+---------------------+-----------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+
1 row in set


mysql> kill 14310 ;
Query OK, 0 rows affected


mysql> 
### MySQL 更新时出现等待超时问题的解决方案 当遇到 `Lock wait timeout exceeded; try restarting transaction` 的错误时,这通常明当前事务因长时间能获取到所需的资源而被回滚。以下是针对该问题的具体分析和解决方案: #### 1. 背景描述 此错误通常是由于多个并发事务争夺同一资源而导致定冲突引起的[^3]。具体现为某个事务试图修改已被另一个提交事务占用的数据。 --- #### 2. 原因分析 - **长事务持有**:某些事务运行时间过长,在其完成之前阻止了其他事务对该数据的操作。 - **死情况**:两个或更多事务相互依赖对方持有的,形成循环等待- **高并发环境下的竞争**:在高并发场景下,频繁读写相同记录可能导致争用加剧。 - **配置参数不足**:默认情况下,InnoDB 存储引擎中的 `innodb_lock_wait_timeout` 参数设置为较低值(如 50 秒),可能不足以满足复杂业务需求[^3]。 --- #### 3. 解决方案 ##### 3.1 查询并终止阻塞事务 通过查看当前活动会话及其状态来定位哪个事务正在占用所需资源,并考虑手动中断它以释放: ```sql -- 查看所有进程列 SHOW PROCESSLIST; -- 杀掉指定 ID 的线程 KILL <thread_id>; ``` 如果发现某条语句执行时间异常延长,则可能是造成堵塞的原因之一[^3]。 ##### 3.2 扩展等待超时时限 调整 InnoDB 默认等待时限可以给事务更多的机会去获得必要的而不至于立即失败。可以通过修改全局变量实现这一点: ```sql SET GLOBAL innodb_lock_wait_timeout = 120; ``` 注意重启服务可能会恢复原始设定,因此建议将其加入 my.cnf 配置文件永久生效[^3]: ```ini [mysqld] innodb_lock_wait_timeout=120 ``` ##### 3.3 优化 SQL 逻辑减少范围及时长 重新审视涉及更新操作的相关代码片段,确保它们遵循最佳实践原则,比如只加载必要字段而非整张扫描;利用索引加速检索过程从而缩短加周期等等[^4]。例如对于批量处理任务来说,分批次提交而不是一次性全部做完往往能有效缓解压力。 ##### 3.4 使用乐观机制替代悲观策略 传统方式采用显式的行级排他控制访问权限容易引发此类问题,转而应用版本号验证方法可以在一定程度上规避这些问题的发生几率[^5]。即每次变更前先校验目标对象最新版本号是否匹配预期值,如果不符则提示用户刷新页面后再试。 --- ### 示例代码展示如何增加等待时间 下面是一个简单的例子说明怎样动态改变 session 层面的等待阈值以便临时解决问题: ```sql -- 设置当前连接内的等待时间为 300 秒 SET SESSION innodb_lock_wait_timeout = 300; -- 尝试再次执行原来失败的 DML 操作 DELETE FROM facebook_posts WHERE id = 7048962; ``` ---
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值