Showing posts with label reconnect. Show all posts
Showing posts with label reconnect. Show all posts

Friday, September 28, 2007

Refreshing the database connection pool with Apache DBCP

After deploying CAS one problem that cropped up was that after 2 hours Oracle would close the database connection while Apache's DBCP would hold on the connection in its pool unaware what the database has done. Then next time a database query CAS would through the following exception

java.sql.SQLException: ORA-03135: connection lost contact

Our dba suggested we use OCI instead of thin client and he configured ORACLE configuration on the system. However it didn't work. It seems this scenario is not part of the failover, probably because DB responds with a RESET instead of a timeout, so at the connectivity is not lost with the server. I tried looking for a RECONNECT setting for Oracle JDBC but couldn't find it; however, DBCP provides testing of the connection in the pool before using it. So I configured the testing as follows

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
<property name="driverClassName">
<value>oracle.jdbc.driver.OracleDriver</value>
</property>
<property name="url">
<value>jdbc:oracle:oci:@<DB></value>
</property>
<property name="username">
<value>USERNAME</value>
</property>
<property name="password">
<value>PASSWORD</value>
</property>
<property name="validationQuery" value="select <something> from <table>" />
<property name="testOnBorrow" value="true"/>
</bean>

validationQuery is a quick query that is used to test the connection whenever the connection is borrowed from the pool. If the connection fails then it is removed from the pool and DBCP creates a new connection if it can't find any valid connection. This has been working so far without error. Only overhead is the execution of validationQuery. I would actually prefer to have a RECONNECT option as it would probably be with lower overhead. And for that the search continues.