High Concurrency Connectionpool - stale connections

[naganaveen]

Currently we are migrating some of existing applications from Websphere to Tomcat. We are using spring high concurrency connection pool for DB interactions.

We are facing an issue in Tomcat related to stale connections. Whenever, there is a response back from server indicating that the connection is already closed, connection pool should reject the connection. However, in our case its continuing with the same connection (application is throwing exceptions) and the pool is not getting purged.

We parallely tested the same on websphere(with the same driver & database instance) and we found that this issue is not present on websphere.


For now, we havent put any validation query / configuration as we are not sure if we have permissions to execute custom SQLs .


Is there any solution(s) where we can purge the stale connection and the entire pool when we encounter a "connection already closed error" basing on the error code received back in SQL exception ?

　
Currently this issue is occurring when interacting with Sybase DB. Not sure about other databases.

Below are the configuration details:


<Resource auth="Container" driverClassName="com.sybase.jdbc2.jdbc.SybDriver" size="38" minIdle="8" maxWait="8" minEvictableIdleTimeMillis="1800" timeBetweenEvictionRunsMillis="300" name="jdbc/<<lookup name>>" password="XX" username="XX" type="javax.sql.DataSource" url="jdbc:sybase:Tds:<server>:<port>/<dbname>" defaultTransactionIsolation="2" jdbcInterceptors="StatementCache(prepared=true,cal lable=true,max=8)"/>


Error Log from Tomcat:

java.sql.SQLException: JZ006: Caught IOException: com.sybase.jdbc2.jdbc.SybConnectionDeadException: JZ0C0: Connection is already closed. at com.sybase.jdbc2.jdbc.ErrorMessage.createIOEKilled ConnEx(ErrorMessage.java:753)

at com.sybase.jdbc2.jdbc.ErrorMessage.raiseErrorCheck Dead(ErrorMessage.java:794)

[Andy Wilkinson]

You need to configure the connection pool to test connections. Please see this thread for more details.

[DaveDudek]

Andy,
Is there an alternative to the validation query? We will try this solution but our application is very high volume and using the validation query may impact performance. Containers like WebSphere have built in functionality for determining stale connections. Does the High Concurrency Connection Pool have anything else built in for detecting stale connections and flushing the pool?

Thanks,
Dave

[fhanik]

Dave,
The pool allows you to create your own validation by implementing a Validator interface.
http://tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html
Normally a validation query and testOnBorrow="true" is all you need. Couple that with the validationInterval setting for improved performance. If you wish to do something more advanced, use the above mentioned interface..