Validating Connection Before Handing over to WebApp in ConnectionPooling

Question

I have connection pooling implemented in spring using Oracle Data Source. Currently we are facing an issue where connections are becoming invalid after a period of time. (May be Oracle is dropping those idle connections after a while). Here are my questions:

1. Can Oracle database be configured to drop idle connections automatically after a specific period of time. Since we expect those connections to lie idle for a while; if there is any such configuration; it may be happening.

2. In our connection pooling properties in spring we didn't have "validateConnection" property. I understand that it validates the connection before handing it over to web application? But does that mean that if a connection passes validateConnection test then it'll always connect to database correctly. I ask this, as I read following problem here: http://forum.springsource.org/showthread.php?t=69759

3. If suppose validateConnection doesn't do the whole 9 yards of ensuring that connection is valid, is there any other option like "testBeforBorrow" in DBCP , which runs a test query to ensure that connection is active before handing it over to webapp?

I'll be grateful if you could provide answers to one ore more queries listed above.

Cheers

Answer 1

You don't say what application server you are using, or how you are configuring the datasource, so I can't give you specific advice.

Connection validation often sounds like a good idea, but you have to be careful with it. For example, we once used it in our JBoss app servers to validate connections in the pool before handing them to the application. This Oracle-proprietary mechanism used the ping() method on the Oracle JDBC driver, which checks that the connection is still alive. It worked fine, but it turns out that ping() executes "select 'x' from dual' on the server, which is a surprisingly expensive query when it's run dozens of times per second.

So the moral is, if you have a high-traffic server, be very careful with connection validation, it can actually bring your database server to its knees.

As for DBCP, that has the ability to validate connections as their borrowed from the pool, as well as returned to the pool, and you can tell it what SQL to send to the database to perform this validation. However, if you're not using DBCP for your connection pooling, then that's not much use to you. C3PO does something similar.

If you're using an app server's data source mechanism, then you have to find out if you can configure that to validate connections, and that's specific to your server.

One last thing: Spring isn't actually involved here. Spring just uses the DataSource that you give it, it's up to the DataSource implementation to perform connection validation.

Answer 2

Configuration of data source "was" as follows:

<bean id="datasource2"
 class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<property name="driverClassName">
         <value>org.apache.commons.dbcp.BasicDataSource</value>
    </property>
<property name="url">
         <value>ORACLE URL</value>
    </property>
<property name="username">
         <value>user id</value>
    </property>
<property name="password">
         <value>user password</value>
    </property>
<property name="initialSize" value="5"/>
<property name="maxActive" value="20"/>
</bean>

have changed it to:

<bean id="connectionPool1" class="oracle.jdbc.pool.OracleDataSource" destroy-method="close">
<property name="connectionCachingEnabled" value="true" />
<property name="URL">
         <value>ORACLE URL</value>
    </property>
<property name="user">
         <value>user id</value>
    </property>
<property name="password">
         <value>user password</value>
    </property>
<property name="connectionCacheProperties">
      <value>
     MinLimit:1
     MaxLimit:5
     InitialLimit:1
     ConnectionWaitTimeout:120
     InactivityTimeout:180
     ValidateConnection:true
      </value>
   </property>
</bean>