Lock wait timeout exceeded； try restarting transaction的错误

关注

已于 2024-08-27 10:24:34 修改 · 1.6k 阅读

原创 ·

本内容遵循 CC 4.0 BY-SA 版权协议

· GEO检测

MySQL批量新增或修改时出现异常： Lock wait timeout exceeded； try restarting transaction 在使用MySQL数据库进行批量新增或修改操作时，有时候会遇到"nested exception is java.sql.SQLException: Lock wait timeout exceeded;try restarting transaction"异常。本文将介绍异常出现的原因，并提供解决该异常的方法。阅读详情

一、异常发现
在进行接口调用时，响应时间超长，之后接口返回异常，查看日志发现为Lock wait timeout exceeded; try restarting transaction的错误。

二、异常定位
因为使用的数据库为mysql，而InnoDB表类型会出现锁等待的情况，在出现锁等待时，会根据参数innodb_lock_wait_timeout（默认50s）的配置，判断是否需要进行timeout的操作，如果等待时间超过了设置的时间就会报错。

1、锁表语句确认
可以在information_schema查询数据库使用情况，information_schema这张数据表保存了MySQL服务器所有数据库的信息。如数据库名，数据库的表，表栏的数据类型与访问权限等。再简单点，这台MySQL服务器上，到底有哪些数据库、各个数据库有哪些表，每张表的字段类型是什么，各个数据库要什么权限才能访问等等信息都保存在information_schema表里面。

我们可以用下面三张表来查原因：

1、 innodb_trx ## 当前运行的所有事务
2、innodb_locks ## 当前出现的锁
3、innodb_lock_waits ## 锁等待的对应关系

如果数据库中有锁的话，查看 innodb_trx就可以看到对应的信息。通过查询知道是哪条语句锁了，图中红色语句为占用系统资源的语句，我们需要杀掉这个锁，执行 kill 线程id号。上面这条记录的id为319618246 ，所以我们执行：kill 319618246即可。

SELECT * FROM information_schema.INNODB_TRX

以下为执行前后对比：

2、实际场景排查
按照经验列举锁等待超时出现的情况：

1、在同一事务内先后对同一条数据进行插入和更新操作

2、多台服务器操作同一数据库

3、瞬时出现高并发现象，spring事务造成数据库死锁，后续操作超时抛出异常

4、事务A对记录C进行更新/删除操作的请求未commit时，事务B也对记录C进行更新/删除操作。此时，B会等A提交事务，释放行锁。当等待时间超过innodb_lock_wait_timeout设置值时，会产生“LOCK WAIT”事务。

5、数据库内存不足，导致无法执行写操作。

本次问题是因为update语句导致的锁表，因为是在疲劳测试（压测12小时）过程中出现的问题，使用10并发进行压测，每秒差不多15笔申请调用，所以上述的1234都不符合，查看发现mysql资源不足，内存达到100%，确认实际异常原因。

三、解决思路
1、本次解决方式
删除无用数据。并使用下边的方法2。

2、其他场景解决思路扩展
1、【治标方法】innodb_lock_wait_timeout 锁定等待时间改大
修改超时时间将 #innodb_lock_wait_timeout = 50 修改为 innodb_lock_wait_timeout = 500。

缺点：全局更改，影响也是全局的，等待时间加长，容易使等待事务增多导致堆积问题。

2、【治标方法】事务信息查询
通过SELECT * FROM information_schema.innodb_trx查询未提交事务，查到一个一直没有提交的只读事务（trx_state=”LOCK WAIT”），找到对应线程，执行：kill 线程ID。线程id为表中的trx_mysql_thread_id字段。

3、【治标方法】如果杀掉线程依然不能解决，可以查找执行线程耗时比较久的任务，kill掉
执行

SELECT * from information_schema.`PROCESSLIST` WHERE Time > 1000 AND USER = 'xxx' ORDER BY TIME desc;

找到线程：kill 线程ID。

4、【根本解决方法！】找到锁表的事务，分析锁表原因，进行优化
根据实际业务进行解决，举例（网上找的）：司机APP进行运单签收，需要对et_waybill_info表某些记录进行更新操作。一直处于锁等待状态，直到超时报错。经排查发现：系统定时器定时执行任务，将所有未标识亮的已装车或签收的运单，按批次处理，如果运单装车了但长时间未上传GPS、温湿度等信息，会一直被定时器处理。数据量越积越大，队列长时间等待，对et_waybill_info表锁住没有释放，致使签收要操作et_waybill_info表无法拿到锁，进行数据操作。

临时解决方案：停掉定时器任务。

根本解决方案：优化定时器。