DataSource.getConnection freezes for 3 min when the db is down

[modest]

Hi Guys,

i use oracle.jdbc.pool.OracleDataSource.

i see that OracleDataSource.getConnection freezes for 3 min when the db is down.

both loginTimeout and ConnectionWaitTimeout are set.

i tested it with plain jdbc and it returns after configured timeout.

[Marten Deinum]

Configuration? Please use [ code][ /code] tags when posting that.

[modest]

<bean id="dataSource" class="oracle.jdbc.pool.OracleDataSource" destroy-method="close">
<property name="URL">
<value>${database.url}</value>
</property>
<property name="user">
<value>${database.user}</value>
</property>
<property name="password">
<value>${database.password}</value>
</property>
<property name="loginTimeout">
<value>1</value>
</property>
<property name="connectionCachingEnabled">
<value>true</value>
</property>
<property name="connectionCacheName">
<value>MISConnectionCache</value>
</property>
<property name="connectionCacheProperties">
<props>
<prop key="MinLimit">10</prop>
<prop key="MaxLimit">11</prop>
<prop key="InitialLimit">10</prop>
<prop key="ConnectionWaitTimeout">1</prop>
<prop key="MaxStatementsLimit">30</prop>
</props>
</property>
<property name="implicitCachingEnabled">
<value>true</value>
</property>
<property name="maxStatements">
<value>30</value>
</property>
</bean>

[karldmoore]

Just off the top of my head, does it wait for every connection it tries to make? e.g. if you set the initial limit to something low would it return quicker?

[modest]

well, once db is down, all connections in connection pool are marked as invalid.
so, the responce returns immediately untill the connection is taken from the pool. the exception thrown is 'java.sql.SQLException: Closed Connection'.

when all connections from pool are checked out, next DataSource.getConnection tries to open new connection and then it freezes.
the exception thrown after that is: 'java.sql.SQLException: Io exception: The Network Adapter could not establish the connection'

[karldmoore]

I would guess there might be another timeout setting e.g. how long to wait when trying to connect.

[modest]

I would guess there might be another timeout setting e.g. how long to wait when trying to connect.

where? In Spring?

There are such parameters in OracleDataSource:

connectionWaitTimeout - Time limit to wait, when a new connection request arrives, and there are no connections to check out. It is configured to 1 (sec).

loginTimeout - The maximum number of millises that the pool will wait for a connection to be returned before throwing an exception. It is configured to 1 (ms).

It works with plain Jdbc code.

[karldmoore]

No, I specifically meant on the dataSource you are using. I don't really see why you would experience different behviour with or without Spring. The dataSource is the thing that actually manages the connections for you. If you configure this in the same way in both examples I would expect the same behaviour.