spring - 使用 HikariCP JDBC 连接池时的 SQLSTATE(08006)、ErrorCode(17002)
问题描述
在我们的服务器中,我们使用调度程序来执行特定任务。服务器上已经运行了许多调度程序,它们运行良好,并且可以毫无障碍地执行数据库操作。但在以下情况下,由于一些奇怪的问题,我得到以下异常:
00:01:01,322 WARN [com.zaxxer.hikari.proxy.ConnectionProxy] (schedulerFactoryBean_Worker-4) Connection oracle.jdbc.driver.T4CConnection@98b70f9 (springHikariCP) marked as broken because of SQLSTATE(08006), ErrorCode(17002).
00:01:01,326 ERROR [stderr] (schedulerFactoryBean_Worker-4) java.sql.SQLRecoverableException: Io exception: Socket read timed out
00:01:01,327 ERROR [stderr] (schedulerFactoryBean_Worker-4) at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:101)
00:01:01,328 ERROR [stderr] (schedulerFactoryBean_Worker-4) at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133)
00:01:01,329 ERROR [stderr] (schedulerFactoryBean_Worker-4) at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:199)
00:01:01,331 ERROR [stderr] (schedulerFactoryBean_Worker-4) at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:263)
00:01:01,332 ERROR [stderr] (schedulerFactoryBean_Worker-4) at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:521)
00:01:01,333 ERROR [stderr] (schedulerFactoryBean_Worker-4) at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:791)
00:01:01,333 ERROR [stderr] (schedulerFactoryBean_Worker-4) at oracle.jdbc.driver.T4CStatement.executeMaybeDescribe(T4CStatement.java:855)
00:01:01,334 ERROR [stderr] (schedulerFactoryBean_Worker-4) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1186)
00:01:01,334 ERROR [stderr] (schedulerFactoryBean_Worker-4) at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1377)
00:01:01,335 ERROR [stderr] (schedulerFactoryBean_Worker-4) at oracle.jdbc.driver.OracleStatementWrapper.executeQuery(OracleStatementWrapper.java:387)
00:01:01,335 ERROR [stderr] (schedulerFactoryBean_Worker-4) at com.zaxxer.hikari.proxy.StatementProxy.executeQuery(StatementProxy.java:99)
00:01:01,336 ERROR [stderr] (schedulerFactoryBean_Worker-4) at com.zaxxer.hikari.proxy.StatementJavassistProxy.executeQuery(StatementJavassistProxy.java)
00:01:01,336 ERROR [stderr] (schedulerFactoryBean_Worker-4) at com.inov8.microbank.server.dao.stakeholdermodule.hibernate.StakeholderBankInfoHibernateDAO.getStakeholderBankInfoModelList(StakeholderBankInfoHibernateDAO.java:130)
00:01:01,337 ERROR [stderr] (schedulerFactoryBean_Worker-4) at com.inov8.microbank.server.service.stakeholdermodule.StakeholderBankInfoManagerImpl.getStakeholderBankInfoModelList(StakeholderBankInfoManagerImpl.java:258)
00:01:01,338 ERROR [stderr] (schedulerFactoryBean_Worker-4) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
00:01:01,339 ERROR [stderr] (schedulerFactoryBean_Worker-4) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
00:01:01,339 ERROR [stderr] (schedulerFactoryBean_Worker-4) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
00:01:01,340 ERROR [stderr] (schedulerFactoryBean_Worker-4) at java.lang.reflect.Method.invoke(Method.java:606)
00:01:01,340 ERROR [stderr] (schedulerFactoryBean_Worker-4) at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:318)
00:01:01,341 ERROR [stderr] (schedulerFactoryBean_Worker-4) at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
00:01:01,343 ERROR [stderr] (schedulerFactoryBean_Worker-4) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
00:01:01,344 ERROR [stderr] (schedulerFactoryBean_Worker-4) at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110)
00:01:01,345 ERROR [stderr] (schedulerFactoryBean_Worker-4) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
00:01:01,346 ERROR [stderr] (schedulerFactoryBean_Worker-4) at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
00:01:01,346 ERROR [stderr] (schedulerFactoryBean_Worker-4) at com.sun.proxy.$Proxy78.getStakeholderBankInfoModelList(Unknown Source)
00:01:01,347 ERROR [stderr] (schedulerFactoryBean_Worker-4) at com.inov8.microbank.server.service.dailyjob.FundTransferScheduler.getStakeholderBankInfoModelList(FundTransferScheduler.java:587)
00:01:01,348 ERROR [stderr] (schedulerFactoryBean_Worker-4) at com.inov8.microbank.server.service.dailyjob.FundTransferScheduler.executeInternal(FundTransferScheduler.java:93)
00:01:01,348 ERROR [stderr] (schedulerFactoryBean_Worker-4) at org.springframework.scheduling.quartz.QuartzJobBean.execute(QuartzJobBean.java:113)
00:01:01,349 ERROR [stderr] (schedulerFactoryBean_Worker-4) at org.quartz.core.JobRunShell.run(JobRunShell.java:202)
00:01:01,349 ERROR [stderr] (schedulerFactoryBean_Worker-4) at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:573)
00:01:01,350 ERROR [stderr] (schedulerFactoryBean_Worker-4) Caused by: oracle.net.ns.NetException: Socket read timed out
00:01:01,351 ERROR [stderr] (schedulerFactoryBean_Worker-4) at oracle.net.ns.Packet.receive(Packet.java:249)
00:01:01,351 ERROR [stderr] (schedulerFactoryBean_Worker-4) at oracle.net.ns.DataPacket.receive(DataPacket.java:92)
00:01:01,352 ERROR [stderr] (schedulerFactoryBean_Worker-4) at oracle.net.ns.NetInputStream.getNextPacket(NetInputStream.java:172)
00:01:01,353 ERROR [stderr] (schedulerFactoryBean_Worker-4) at oracle.net.ns.NetInputStream.read(NetInputStream.java:117)
00:01:01,354 ERROR [stderr] (schedulerFactoryBean_Worker-4) at oracle.net.ns.NetInputStream.read(NetInputStream.java:92)
00:01:01,355 ERROR [stderr] (schedulerFactoryBean_Worker-4) at oracle.net.ns.NetInputStream.read(NetInputStream.java:77)
00:01:01,356 ERROR [stderr] (schedulerFactoryBean_Worker-4) at oracle.jdbc.driver.T4CMAREngine.unmarshalUB1(T4CMAREngine.java:1034)
00:01:01,357 ERROR [stderr] (schedulerFactoryBean_Worker-4) at oracle.jdbc.driver.T4CMAREngine.unmarshalSB1(T4CMAREngine.java:1010)
00:01:01,357 ERROR [stderr] (schedulerFactoryBean_Worker-4) at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:588)
00:01:01,358 ERROR [stderr] (schedulerFactoryBean_Worker-4) at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:183)
00:01:01,359 ERROR [stderr] (schedulerFactoryBean_Worker-4) at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:780)
00:01:01,359 ERROR [stderr] (schedulerFactoryBean_Worker-4) ... 24 more
00:01:01,363 INFO [stdout] (schedulerFactoryBean_Worker-4) ERROR [schedulerFactoryBean_Worker-4] | *******>>>ERROR MESSAGE<<<*******
00:01:01,364 INFO [stdout] (schedulerFactoryBean_Worker-4) Hibernate flushing: Cannot release connection; uncategorized SQLException for SQL [???]; SQL state [99999]; error code [17008]; Closed Connection; nested exception is java.sql.SQLException: Closed Connection
00:01:01,365 ERROR [org.quartz.core.JobRunShell] (schedulerFactoryBean_Worker-4) Job DEFAULT.Funds Transfer threw an unhandled Exception: : java.lang.RuntimeException: Unable to load Stakeholder bank info list
at com.inov8.microbank.server.service.dailyjob.FundTransferScheduler.executeInternal(FundTransferScheduler.java:97) [classes:]
at org.springframework.scheduling.quartz.QuartzJobBean.execute(QuartzJobBean.java:113) [spring-context-support-3.1.1.RELEASE.jar:3.1.1.RELEASE]
at org.quartz.core.JobRunShell.run(JobRunShell.java:202) [quartz-2.2.1.jar:]
at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:573) [quartz-2.2.1.jar:]
这是我的 HikariCp 配置:
<bean id="dataSource" class="com.zaxxer.hikari.HikariDataSource" destroy-method="close">
<constructor-arg index="0">
<bean id="hikariConfig" class="com.zaxxer.hikari.HikariConfig">
<property name="poolName" value="springHikariCP" />
<property name="minimumIdle" value="5"/>
<property name="maximumPoolSize" value="5"/>
<property name="transactionIsolation" value="TRANSACTION_READ_COMMITTED"/>
<property name="connectionTestQuery" value="select 1 from dual" />
<!-- <property name="connectionInitSql" value="begin
dbms_output.put_line('CALL nothing; DECIMALS=0');
end;" />-->
<property name="initializationFailFast" value="false"/>
<property name="idleTimeout" value="30000"/>
<property name="dataSource" ref="oracleDataSource"/>
</bean>
</constructor-arg>
</bean>
<bean id="oracleDataSource" class="oracle.jdbc.pool.OracleDataSource">
<property name="user" value="${datasource.username}"/>
<property name="password" value="${datasource.password}"/>
<property name="URL" value="${datasource.url}"/>
<property name="connectionProperties">
<props>
<prop key="oracle.jdbc.ReadTimeout">120000</prop><!--In Milliseconds-->
</props>
</property>
</bean>
在搜索了几个小时后我无法得到准确的答案,所以这就是我现在在 Stack Overflow 询问的原因。
解决方案
Spring Cloud Task 创建了一个名为“TASK_LOCK”的数据库锁(TABLE)。它必须是干净的,并且应该在退出之前释放所有锁。
就我而言,在 WARN 语句告诉之前有一个错误 - 无法处理 @BeforeTask 或 @AfterTask 注释,因为:名称为“调度程序”的任务已经在运行。
推荐阅读
- asp.net-core-mvc - 身份服务器 4 使用 FrontChannelLogoutUri 从所有客户端注销不起作用
- amazon-web-services - AWS Lambda 调用 Fargate 任务
- reactjs - ANT 设计推拉混淆
- azure-devops - 无法在远程服务器(VM)中为 Azure 发布管道安装代理(持续部署)
- javascript - “点击 1 获得 1,点击 2 获得 2”
- http - 如何充当中间人服务器在客户端和远程服务器之间添加 HTTP 标头?
- python - 反正有没有在正则表达式的搜索功能中使用变量?
- psql - 我怎样才能回滚 psql -f 命令?
- android - 如何在视图绑定中的活动中获取回收者视图当前项目文本值
- typescript - I'm getting a confusing legend in Highcharts (angular-highcharts)