java 锁超时时间_java - 数据库锁等待超时

因为写的大作业经常用到数据库连接 所以自己写了个数据库连接的类 package DB_Link_info;/* * 数据库链接信息 */public class DB_link_Info {public static final String driverName = "com.microsoft.sqlserver.jdbc.SQLServerDriver";public static

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() ~[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() ~[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.这段时间尝试了一下MongoDB，感觉十分易用，方便，相比关系型的数据库来说优势也很大，于是尝试了下使用java连接MongoDB，并进行了 基本的增删改查操作。 首先先在控制台中连接数据库，查看数据库有几个表。 现在，新建一个maven工程，pom.xml中的依赖如下：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后就一直报错，停不下来，完全不知道数据库发生了什么, 需要分析数据库的什么语句导致锁超时了.查找锁超时的语句,找到该表的其他sql语句,判断是执行时间过长引起的还是死锁之类的.show processlist;具体的命令你可以再查查. delete语句这种执行,会导致间隙锁,看你的sql语句命中的范围应该比较大.也会有比较大的概率导致锁获取失败., 在你的业务逻辑中，会同时操作多次表，前面在update或者delete表的时候，它还没有释放 数据库中的行锁，你下面又去delete该行就会报错的，获取锁等待超时。 你可以在delete的时候，加Thred.sleep(5);让线程睡一会儿。 或者在方法上手动开启事务，保证上一次执行成功，释放了锁之后，你才能进行 delete 操作， 希望 对你有 帮助~~~！！！