Tomcat/Jetty, DBCP, and Stale Connections

Posted by

5

In developing applications using Grails we were running into exceptions each morning when users try to access them. The ultimate manifestation was something like:

errors.GrailsExceptionResolver org.springframework.dao.DataAccessResourceFailureException: could not execute query; 
nested exception is org.hibernate.exception.JDBCConnectionException: could not execute query
org.codehaus.groovy.runtime.InvokerInvocationException: org.springframework.dao.DataAccessResourceFailureException: could not execute query;
nested exception is org.hibernate.exception.JDBCConnectionException: could not execute query

In DB2 the low-level exception was:

Caused by: com.ibm.db2.jcc.a.sm: [jcc][t4][2030][11211][3.51.90]
A communication error occurred during operations on the connection's underlying socket, socket input stream, or socket output stream.
Error location: T4Agent.sendRequest().  Message: Connection reset by peer: socket write error. ERRORCODE=-4499, SQLSTATE=08001

This was wrapping a java.net.SocketException.

The underlying cause is that we sever all connections to the database for a nightly backup and the applications did not find out about it until they tried to run a query against a stale connection. As it happens though, Tomcat and Jetty (and probably many others) both use Apache Commons DBCP for connection pooling and there are configuration settings that can mitigate this problem; they are documented here – http://commons.apache.org/dbcp/configuration.html.

The settings we are trying in order to remedy the problem are:

    validationQuery="select 1 from sysibm.sysdummy1"
    testWhileIdle="true"
    timeBetweenEvictionRunsMillis="600000"

We are also starting out with these settings which are not particularly related, but might be of interest to some.

    initialSize="4"
    maxActive="100"
    maxIdle="8"
    minIdle="2"
    maxWait="2000"
    removeAbandoned="true"
    logAbandoned="true"

5 thoughts on “Tomcat/Jetty, DBCP, and Stale Connections”

  3. Or you can use ‘testOnBorrow’ to true with validationQuery set to a non-null string. It would check the connection just before borrowing

    Reply

  4. Yes, the options as documented at the DBCP configuration link in the post are testOnBorrow, testOnReturn, and testWhileIdle. It all depends on your environment. Given that our problem occurred predictably once a day and we pull database connections from the pool pretty heavily at times, running a query every time we wanted a connection (or when we returned a connection) seemed like a very heavy solution. Do what makes sense in your environment!

    Reply

Leave a comment