Lock wait timeout exceeded; try restarting transaction

绝地灬酷狼 2022-04-10 04:10 249阅读 0赞

锁等待超时 Lock wait timeout exceeded; try restarting transaction，是当前事务在等待其它事务释放锁资源造成的

### Cause: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
    ; SQL []; Lock wait timeout exceeded; try restarting transaction; nested exception is java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
    	at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:259)
    	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
    	at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:74)
    	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:399)
    	at com.sun.proxy.$Proxy30.update(Unknown Source)
    	at org.mybatis.spring.SqlSessionTemplate.update(SqlSessionTemplate.java:269)
    	at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:54)
    	at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:52)
    	at com.sun.proxy.$Proxy46.updateSearchCountBydealStatus(Unknown Source)
    	at com.autoserve.weijinrong.loan.modules.plan.service.ProjectPlanService.timingQueryBatchReceivables(ProjectPlanService.java:544)
    	at com.autoserve.weijinrong.loan.modules.plan.service.ProjectPlanService$$FastClassBySpringCGLIB$$a5d3cf75.invoke(<generated>)
    	at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
    	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:718)
    	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
    	at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
    	at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:281)
    	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
    	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    	at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:653)
    	at com.autoserve.weijinrong.loan.modules.plan.service.ProjectPlanService$$EnhancerBySpringCGLIB$$1f8f4733.timingQueryBatchReceivables(<generated>)
    	at com.autoserve.weijinrong.loan.modules.plan.service.ProjectPlanService$$FastClassBySpringCGLIB$$a5d3cf75.invoke(<generated>)
    	at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
    	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:718)
    	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
    	at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
    	at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:281)
    	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
    	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    	at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:653)
    	at com.autoserve.weijinrong.loan.modules.plan.service.ProjectPlanService$$EnhancerBySpringCGLIB$$2301f689.timingQueryBatchReceivables(<generated>)
    	at com.autoserve.weijinrong.loan.Task.AutoPayTask.timingQueryBatchReceivables(AutoPayTask.java:161)
    	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    	at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    	at java.lang.reflect.Method.invoke(Unknown Source)
    	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:81)
    	at java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)
    	at java.util.concurrent.FutureTask.run(Unknown Source)
    	at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(Unknown Source)
    	at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(Unknown Source)
    	at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
    	at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
    	at java.lang.Thread.run(Unknown Source)
    Caused by: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
    	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1084)
    	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4232)
    	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4164)
    	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2615)
    	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2776)
    	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2838)
    	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2082)
    	at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1307)
    	at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:2931)
    	at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(FilterEventAdapter.java:440)
    	at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:2929)
    	at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.execute(PreparedStatementProxyImpl.java:131)
    	at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:493)
    	at sun.reflect.GeneratedMethodAccessor190.invoke(Unknown Source)
    	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    	at java.lang.reflect.Method.invoke(Unknown Source)
    	at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:62)
    	at com.sun.proxy.$Proxy37.execute(Unknown Source)
    	at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:44)
    	at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:69)
    	at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:48)
    	at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:105)
    	at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:71)
    	at sun.reflect.GeneratedMethodAccessor264.invoke(Unknown Source)
    	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    	at java.lang.reflect.Method.invoke(Unknown Source)
    	at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:62)
    	at com.sun.proxy.$Proxy150.update(Unknown Source)
    	at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:152)
    	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    	at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    	at java.lang.reflect.Method.invoke(Unknown Source)
    	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:386)
    	... 41 more

### 解决办法 ###

1、数据库中执行如下sql，查看当前数据库的线程情况：

show full PROCESSLIST

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dzanp6Y2Jx_size_16_color_FFFFFF_t_70][]

2、再到 INNODB\_TRX 事务表中查看，看 trx\_mysql\_thread\_id 是否在 show full processlist 里面的 sleep 线程中（  
INNODB\_TRX 表的 trx\_mysql\_thread\_id  字段对应 show full processlist 中的Id）；如果在，就说明这个 sleep 的线程事务一直没有 commit 或者 rollback，而是卡住了，需要我们手动删除。

select * from information_schema.innodb_trx

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dzanp6Y2Jx_size_16_color_FFFFFF_t_70 1][]

3、将找到的 trx\_mysql\_thread\_id 手动删除，即可

如上图，trx\_mysql\_thread\_id 为 1816

kill  1816

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dzanp6Y2Jx_size_16_color_FFFFFF_t_70 2][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dzanp6Y2Jx_size_16_color_FFFFFF_t_70]: /images/20220403/a9f49c59177049b08b3632f4291162ef.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dzanp6Y2Jx_size_16_color_FFFFFF_t_70 1]: /images/20220403/d7b65c7b7ed84703aad96525607a4cc4.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dzanp6Y2Jx_size_16_color_FFFFFF_t_70 2]: /images/20220403/9f92ca44aa334e9ba4042cd0e1840e8c.png