解决异常【MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction】
发布日期：2021-06-29 13:14:14
浏览次数：2
分类：技术文章
本文共 23928 字，大约阅读时间需要 79 分钟。
错误日志：
org.springframework.dao.CannotAcquireLockException: ### Error updating database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction### The error may involve cn.changemax.dao.FilmInfoDAO.batchInsert-Inline### The error occurred while setting parameters### SQL: insert into t_film_info ( film_id, film_link, film_name, film_image_link, pic_id, film_star, film_intro, film_director, film_type, film_area, film_years, film_play_number, create_time, modify_time) values ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, now(), now() ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, now(), now() ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, now(), now() ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, now(), now() ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, now(), now() ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, now(), now() ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, now(), now() ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, now(), now() ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, now(), now() ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, now(), now() ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, now(), now() ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, now(), now() ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, now(), now() ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, now(), now() ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, now(), now() ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, now(), now() ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, now(), now() ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, now(), now() ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, now(), now() ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, now(), now() ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, now(), now() ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, now(), now() ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, now(), now() ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, now(), now() ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, now(), now() ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, now(), now() ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, now(), now() ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, now(), now() ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, now(), now() ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, now(), now() ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, now(), now() ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, now(), now() ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, now(), now() ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, now(), now() ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, now(), now() ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, now(), now() ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, now(), now() ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, now(), now() ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, now(), now() ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, now(), now() ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, now(), now() ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, now(), now() ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, now(), now() ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, now(), now() ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, now(), now() ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, now(), now() ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, now(), now() ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, now(), now() ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, now(), now() ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, now(), now() ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, now(), now() ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, now(), now() ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, now(), now() ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, now(), now() ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, now(), now() ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, now(), now() ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, now(), now() ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, now(), now() ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, now(), now() )### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction; ]; Lock wait timeout exceeded; try restarting transaction; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:262) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72) at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73) at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:446) at com.sun.proxy.$Proxy101.insert(Unknown Source) at org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:278) at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:58) at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:59) at com.sun.proxy.$Proxy122.batchInsert(Unknown Source) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:344) at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:197) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:139) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185) at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212) at com.sun.proxy.$Proxy123.batchInsert(Unknown Source) at cn.changemax.service.impl.FilmInfoServiceImpl.selectivityInsertFilmInfoList(FilmInfoServiceImpl.java:232) at cn.changemax.task.SupplementaryFilmDataTask.threadPoolUpdateFilm(SupplementaryFilmDataTask.java:106) at cn.changemax.task.SupplementaryFilmDataTask.configureTasks(SupplementaryFilmDataTask.java:64) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.springframework.scheduling.support.ScheduledMethodRunnable.run(ScheduledMethodRunnable.java:65) at org.springframework.scheduling.support.DelegatingErrorHandlingRunnable.run(DelegatingErrorHandlingRunnable.java:54) at org.springframework.scheduling.concurrent.ReschedulingRunnable.run(ReschedulingRunnable.java:93) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) at java.util.concurrent.FutureTask.run(FutureTask.java:266) at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(ScheduledThreadPoolExecutor.java:180) at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) at java.lang.Thread.run(Thread.java:748)Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:423) at com.mysql.jdbc.Util.handleNewInstance(Util.java:425) at com.mysql.jdbc.Util.getInstance(Util.java:408) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:952) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3976) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3912) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2530) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2683) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2486) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1858) at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1197) at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3051) at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(FilterEventAdapter.java:440) at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3049) at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.execute(PreparedStatementProxyImpl.java:167) at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:498) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:59) at com.sun.proxy.$Proxy176.execute(Unknown Source) at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:46) at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:74) at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:50) at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117) at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:63) at com.sun.proxy.$Proxy174.update(Unknown Source) at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:198) at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:185) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:433) ... 33 common frames omitted
错误产生的原因就是：我有一条sql在数据库执行，很长时间，刚好的delete操作，然后这边项目中刚好执行一条批量新增的sql，两者超时冲突了。
所以说这两者就涉及到事务锁的问题的了，接口响应时间超长，耗时几十秒才返回错误提示，后台日志中出现Lock wait timeout exceeded; try restarting transaction的错误，出现了高并发现象。
那么我们来说说如何解决方案：
当务之急，也是要看看数据库中有没有比较长时间执行的sql：
show processlist;
当前所运行的所有事务
SELECT * FROM information_schema.INNODB_TRX;
当前所有的锁
SELECT * FROM information_schema.INNODB_LOCKs;
锁等待的对应的关系
SELECT * FROM information_schema.INNODB_LOCK_waits;
那么我们看到事务表中INNODB_TRX，里面是否有正在锁定的事务线程，看看ID是否在show processlist里面的sleep线程中，如果有，那么就证明了这个休眠的线程事务一直没有commit（提交）或者roolback（回滚）而是卡住了，所以，我们需要人为介入，kill掉。
如果发现了好多事务任务，那最好都kill掉。
命令为
select concat('KILL ',id,';') from information_schema.processlist where user='cms_bokong'; 通过information_schema.processlist表中的连接信息生成需要处理掉的Mysql连接的语句临时文件，然后执行文件中生成的指令。然后我们获取到了对应任务的id，一个一个 kill id就行了。
然后我们再去找还在进行事务的任务，就会发现空掉了。
应急处理完成后，我们就需要核对原因，防止以后再出现：
1.mysql的引擎检查，可以检查一下数据库引擎是不是InnoDB（mysql5.5.5以前默认是MyISAM,Mysql5.5.5以后默认是InnoDB），show engines；#检查命令
如果不是INNDB，那么就改为InnDB；
命令为：
查看表使用的存储引擎
show table status from db_name where name='table_name';
修改表的存储引擎
alter table table_name engine=innodb;
转载地址：https://changemax.blog.csdn.net/article/details/102562393 如侵犯您的版权，请留言回复原文章的地址，我们会给您删除此文章，给您带来不便请您谅解！
发表评论
最新留言
逛到本站，mark一下
[***.202.152.39]2024年04月22日 03时20分41秒
关于作者
喝酒易醉，品茶养心，人生如梦，品茶悟道，何以解忧？唯有杜康！
-- 愿君每日到此一游！
推荐文章
弹性事务框架ETF4J——面向Java微服务的交易最终一致性解决方案
2019-04-29
【Scala 教程】Scala 条件与循环语句
2019-04-29
【Scala 教程】Scala 集合类型
2019-04-29
JAVA 线程同步机制 synchronized
2019-04-29
MySQL 安装教程（无脑版）
2019-04-29
IDEA 怎么删除一个Module
2019-04-29
走进数据科学：最好是通过比网课更好的方法
2019-04-29
AI革命第一步：最容易被忽略但必不可少的物联网
2019-04-29
2020年开发运维工具清单：选择开发运维工具堆栈吧
2019-04-29
效率提升法则：高效人士不会去做的4件事
2019-04-29
8.PostgreSQL约束
2019-04-29
【技术分享】使用AES加密技术保障数据安全
2019-04-29
【应用实例】布线多？成本高？不可靠？泽耀方案没烦恼！
2019-04-29
数据可视化工具：Matplotlib绘图
2019-04-29
用Python写个超级小恐龙跑酷游戏，上班摸鱼我能玩一天
2019-04-29
闺蜜看我用Python画了一幅樱花图，吵着要我给他介绍程序员小哥哥
2019-04-29
【Python爬虫实战】知乎热榜数据采集，上班工作摸鱼两不误，知乎热门信息一网打尽
2019-04-29
Python抓取哔哩哔哩up主信息：只要爬虫学的好，牢饭吃的早
2019-04-29
白红宇的个人博客 - 记录点点滴滴的事 - 您是第 310126830 位访客
访问时间: 2024-05-02 20:23:05
访问IP: 18.118.193.232
Copyright © 2020 - 2023 blog.css8.cn 京ICP备2021015314号-1
手机版