热门标签
热门文章
- 1Target-aware Transformer 知识蒸馏代码复现_crcd蒸馏代码复现
- 2《网络是怎样连接的》笔记整理
- 3LeetCode_Array_126. Word Ladder II单词接龙II（C++）【双向BFS构图+DFS寻找路径】_c++单词接龙dfs
- 4Spring Boot 邮件发送的 5 种姿势！
- 52024年Python最新万字博文教你python爬虫Beautiful Soup库【详解篇】(1)，阿里巴巴面试算法题
- 6【MySQL】我必须得告诉大家的MySQL优化原理3（下）INNODB配置_unknown suffix 'i' used for variable 'innodb-log-f
- 7【web渗透思路】敏感信息泄露（网站+用户+服务器）_渗透测试,敏感信息泄露
- 8Python+VS Code+Selenium+EdgeDriver实现网页自动化_selenium edgedriver
- 9数字办公新纪元 ：ONLYOFFICE 2.5重磅来袭_ficitx5 onlyoffice
- 10wordpress 通过域名无法访问_教你搭建Wordpress网站
当前位置: article > 正文
解决异常【MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction】_cause: com.mysql.jdbc.exceptions.jdbc4.mysqltransa
作者：Gausst松鼠会 | 2024-06-17 02:31:33
赞
踩
cause: com.mysql.jdbc.exceptions.jdbc4.mysqltransactionrollbackexception: lo
错误日志：
- 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;
声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/Gausst松鼠会/article/detail/729308
推荐阅读
相关标签
