java - hikari/jdbc 连接正在等待超时，报告无效/lp 或帐户被锁定错误

Question

我正在使用 hikaricp 和 Oracle 的 jdbc 驱动程序。当我更改 l/p 或锁定数据库上的帐户时，连接会在尝试添加连接时失败之前等待超时值(10 分钟)。有没有办法让它们立即失败而不是等到超时？

这是我的设置。

<bean id="myDataSource" class="com.zaxxer.hikari.HikariDataSource">
   <constructor-arg index="0">
      <bean parent="baseHikariConfig"
            p:poolName="hikariCP-readOnlyPool"
            p:jdbcUrl="#{jdbcUrl}"
            p:username="#{jdbcUsername}"
            p:password="#{jdbcPassword}"
            p:maximumPoolSize="#{JdbcPoolSize}"
            />
   </constructor-arg>
</bean>

<bean id="baseHikariConfig" class="com.zaxxer.hikari.HikariConfig" abstract="true"
         p:poolName="abstractSpringHikariCP"
         p:driverClassName="oracle.jdbc.driver.OracleDriver"
         p:connectionTimeout="600000"
         p:idleTimeout="600000"
         p:maxLifetime="1800000"
         p:leakDetectionThreshold="3600000"
         p:initializationFailFast="true"
         p:minimumIdle="0"
         p:maximumPoolSize="#{ConnectionPoolSize}"
         p:registerMbeans="true"
         >
   <property name="dataSourceProperties">
      <props>
         <prop key="oracle.jdbc.ReadTimeout">
            #{queryTimeout}
         </prop>
         <prop key="defaultRowPrefetch">1000</prop>
         <prop key="oracle.jdbc.StreamBufferSize">65536</prop>
         <prop key="v$session.program">Tomcat</prop>
         <prop key="oracle.jdbc.RetainV9LongBindBehavior">true</prop>
      </props>
   </property>
</bean>

这是我在帐户锁定时看到的堆栈跟踪。

org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is java.sql.SQLException: Timeout after 600001ms of waiting for a connection.
    at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:80)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:615)
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:680)
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:712)
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:722)
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:772)
    at org.springframework.jdbc.core.JdbcTemplate.queryForList(JdbcTemplate.java:834)
    [..snip.. my code..]
    at java.lang.Thread.run(Thread.java:745)
Caused by: java.sql.SQLException: Timeout after 600001ms of waiting for a connection.
    at com.zaxxer.hikari.pool.BaseHikariPool.getConnection(BaseHikariPool.java:209)
    at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:91)
    at sun.reflect.GeneratedMethodAccessor41.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at net.bull.javamelody.JdbcWrapper$3.invoke(JdbcWrapper.java:765)
    at net.bull.javamelody.JdbcWrapper$DelegatingInvocationHandler.invoke(JdbcWrapper.java:285)
    at com.sun.proxy.$Proxy45.getConnection(Unknown Source)
    at net.sf.log4jdbc.sql.jdbcapi.DataSourceSpy.getConnection(DataSourceSpy.java:149)
    at sun.reflect.GeneratedMethodAccessor41.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at net.bull.javamelody.JdbcWrapper$3.invoke(JdbcWrapper.java:765)
    at net.bull.javamelody.JdbcWrapper$DelegatingInvocationHandler.invoke(JdbcWrapper.java:285)
    at com.sun.proxy.$Proxy47.getConnection(Unknown Source)
    at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:111)
    at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:77)
    ... 67 more
Caused by: java.sql.SQLException: ORA-28000: the account is locked
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:461)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:394)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:386)
    at oracle.jdbc.driver.T4CTTIfun.processError(T4CTTIfun.java:1121)
    at oracle.jdbc.driver.T4CTTIoauthenticate.processError(T4CTTIoauthenticate.java:502)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:541)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:264)
    at oracle.jdbc.driver.T4CTTIoauthenticate.doOAUTH(T4CTTIoauthenticate.java:435)
    at oracle.jdbc.driver.T4CTTIoauthenticate.doOAUTH(T4CTTIoauthenticate.java:1020)
    at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:707)
    at oracle.jdbc.driver.PhysicalConnection.connect(PhysicalConnection.java:755)
    at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:38)
    at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:599)
    at com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:57)
    at com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:63)
    at com.zaxxer.hikari.pool.BaseHikariPool.addConnection(BaseHikariPool.java:376)
    at com.zaxxer.hikari.pool.HikariPool.lambda$addBagItem$2(HikariPool.java:75)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
    ... 1 more

Answer 1

您的 Oracle 帐户已被锁定。

alter user <user_name> account unlock;