phpstudy集成环境

 首页 > 数据库锁等待超时

数据库锁等待超时

231.035 [pool-4-thread-1] ERROR o.s.s.s.TaskUtils$LoggingErrorHandler - Unexpected error occurred in scheduled task.
org.springframework.dao.CannotAcquireLockException:

Error updating database. Cause: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction

The error may involve defaultParameterMap

The error occurred while setting parameters

SQL: delete from zgtopic where DATEADD(createtime,INTERVAL 3 DAY) <=CURRENTTIMESTAMP and creater_type=2 and status=1

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) ~[spring-jdbc-4.0.5.RELEASE.jar:4.0.5.RELEASE]
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73) ~[spring-jdbc-4.0.5.RELEASE.jar:4.0.5.RELEASE]
at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73) ~[mybatis-spring-1.2.2.jar:1.2.2]
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:371) ~[mybatis-spring-1.2.2.jar:1.2.2]
at com.sun.proxy.$Proxy90.delete(Unknown Source) ~[na:na]
at org.mybatis.spring.SqlSessionTemplate.delete(SqlSessionTemplate.java:268) ~[mybatis-spring-1.2.2.jar:1.2.2]
at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:52) ~[mybatis-3.2.5.jar:3.2.5]
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:43) ~[mybatis-3.2.5.jar:3.2.5]
at com.sun.proxy.$Proxy118.deleteTask(Unknown Source) ~[na:na]
at com.zd.zg.service.topic.TopicService.deleteTask(TopicService.java:65) ~[TopicService.class:na]
at com.zd.zg.service.topic.TopicService$$FastClassBySpringCGLIB$$a2373b48.invoke(<generated>) ~[spring-core-4.0.5.RELEASE.jar:na]
at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204) ~[spring-core-4.0.5.RELEASE.jar:4.0.5.RELEASE]
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:708) ~[spring-aop-4.0.5.RELEASE.jar:4.0.5.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157) ~[spring-aop-4.0.5.RELEASE.jar:4.0.5.RELEASE]
at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:98) ~[spring-tx-4.0.5.RELEASE.jar:4.0.5.RELEASE]
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:262) ~[spring-tx-4.0.5.RELEASE.jar:4.0.5.RELEASE]
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:95) ~[spring-tx-4.0.5.RELEASE.jar:4.0.5.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) ~[spring-aop-4.0.5.RELEASE.jar:4.0.5.RELEASE]
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:644) ~[spring-aop-4.0.5.RELEASE.jar:4.0.5.RELEASE]
at com.zd.zg.service.topic.TopicService$$EnhancerBySpringCGLIB$$9a916b3.deleteTask(<generated>) ~[spring-core-4.0.5.RELEASE.jar:na]
at com.zd.zg.task.DeleteTopicTask.deleteTask(DeleteTopicTask.java:28) ~[DeleteTopicTask.class:na]
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.7.0_85]
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) ~[na:1.7.0_85]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.7.0_85]
at java.lang.reflect.Method.invoke(Method.java:606) ~[na:1.7.0_85]
at org.springframework.scheduling.support.ScheduledMethodRunnable.run(ScheduledMethodRunnable.java:65) ~[spring-context-4.0.5.RELEASE.jar:4.0.5.RELEASE]
at org.springframework.scheduling.support.DelegatingErrorHandlingRunnable.run(DelegatingErrorHandlingRunnable.java:54) ~[spring-context-4.0.5.RELEASE.jar:4.0.5.RELEASE]
at org.springframework.scheduling.concurrent.ReschedulingRunnable.run(ReschedulingRunnable.java:81) [spring-context-4.0.5.RELEASE.jar:4.0.5.RELEASE]
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471) [na:1.7.0_85]
at java.util.concurrent.FutureTask.run(FutureTask.java:262) [na:1.7.0_85]
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(ScheduledThreadPoolExecutor.java:178) [na:1.7.0_85]
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:292) [na:1.7.0_85]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [na:1.7.0_85]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [na:1.7.0_85]
at java.lang.Thread.run(Thread.java:745) [na:1.7.0_85]

Caused by: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction

at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1074) ~[mysql-connector-java-5.1.22.jar:na]
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4096) ~[mysql-connector-java-5.1.22.jar:na]
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4028) ~[mysql-connector-java-5.1.22.jar:na]
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2490) ~[mysql-connector-java-5.1.22.jar:na]
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2651) ~[mysql-connector-java-5.1.22.jar:na]
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2734) ~[mysql-connector-java-5.1.22.jar:na]
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2155) ~[mysql-connector-java-5.1.22.jar:na]
at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1379) ~[mysql-connector-java-5.1.22.jar:na]
at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:41) ~[mybatis-3.2.5.jar:3.2.5]
at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:66) ~[mybatis-3.2.5.jar:3.2.5]
at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:45) ~[mybatis-3.2.5.jar:3.2.5]
at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:100) ~[mybatis-3.2.5.jar:3.2.5]
at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:75) ~[mybatis-3.2.5.jar:3.2.5]
at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:148) ~[mybatis-3.2.5.jar:3.2.5]
at org.apache.ibatis.session.defaults.DefaultSqlSession.delete(DefaultSqlSession.java:161) ~[mybatis-3.2.5.jar:3.2.5]
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.7.0_85]
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) ~[na:1.7.0_85]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.7.0_85]
at java.lang.reflect.Method.invoke(Method.java:606) ~[na:1.7.0_85]
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:358) ~[mybatis-spring-1.2.2.jar:1.2.2]
... 31 common frames omitted

这是什么情况，已经重启tomcat后就一直报错，停不下来，完全不知道数据库发生了什么

在你的业务逻辑中，会同时操作多次表，前面在update或者delete表的时候，它还没有释放 数据库中的行锁，你下面又去delete该行就会报错的，获取锁等待超时。

  1. 你可以在delete的时候，加Thred.sleep(5);让线程睡一会儿。

  2. 或者在方法上手动开启事务，保证上一次执行成功，释放了锁之后，你才能进行 delete 操作，

希望 对你有 帮助~~~！！！

需要分析数据库的什么语句导致锁超时了.查找锁超时的语句,找到该表的其他sql语句,判断是执行时间过长引起的还是死锁之类的.show processlist;具体的命令你可以再查查.

delete语句这种执行,会导致间隙锁,看你的sql语句命中的范围应该比较大.也会有比较大的概率导致锁获取失败.

【热门文章】
【热门文章】