tomcat-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Kevin Jackson <foamd...@gmail.com>
Subject tomcat-jdbc pool interceptor
Date Wed, 02 Dec 2009 12:11:28 GMT
Hi,

We have a situation with a clustered SQL Server database and tomcat-jdbc.

When the cluster fails over and the active node switches to the
passive node, all the open connections in the tomcat-jdbc pool become
invalid, but SQL Server doesn't report this and the connection remains
'ESTABLISHED' [see http://support.microsoft.com/kb/273673/]

The stack trace we commonly see is:

Caused by: java.lang.reflect.InvocationTargetException
        at sun.reflect.GeneratedMethodAccessor52.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
        at java.lang.reflect.Method.invoke(Unknown Source)
        at org.apache.tomcat.jdbc.pool.ProxyConnection.invoke(ProxyConnection.java:78)
        ... 43 more
Caused by: java.sql.SQLException: Invalid state, the Connection object
is closed.
        at net.sourceforge.jtds.jdbc.ConnectionJDBC2.checkOpen(ConnectionJDBC2.java:1634)
        at net.sourceforge.jtds.jdbc.ConnectionJDBC2.prepareStatement(ConnectionJDBC2.java:2328)
        ... 47 more

Currently we have set the following params:
    removeAbandoned="true"
    logAbandoned="true"
    maxActive="100"
    maxIdle="30"
    minIdle="20"
    initialSize="20"
    maxWait="10000"
    validationQuery="SELECT count(1) from xyz"

Although it's traditional to simply use 'select 1' as the
validationQuery for SQL Server, we want to ensure that the query isn't
actually optimized out at some point in the driver.

To avoid the problem with failovers, I think we have a couple of options:
#1 add the following config:
    testWhileIdle="true"
    timeBetweenEvictionRunsMillis="30000"
This should evict any stale connections every 5 mins (if the idleness
test is valid and it appears to be so having read the src for
PooledConnection and ConnectionPool)

#2 write a very simple JdbcInterceptor which checks the connection on reset:
package org.apache.tomcat.jdbc.pool.interceptor;

import java.sql.Statement;

import org.apache.tomcat.jdbc.pool.ConnectionPool;
import org.apache.tomcat.jdbc.pool.JdbcInterceptor;
import org.apache.tomcat.jdbc.pool.PoolConfiguration;
import org.apache.tomcat.jdbc.pool.PooledConnection;

/**
 * If the underlying database has failed over (in active-passive configuration)
 * the connections need to be closed
 * @author kevj@apache.org
 */
public class FailoverHandler extends JdbcInterceptor {

	@Override
	public void reset(ConnectionPool parent, PooledConnection con) {
		if(null == parent || null == con) return; //nothing to do
		PoolConfiguration config = parent.getPoolProperties();
		Statement stmt = null;
		try {
			stmt = con.getConnection().createStatement();
			stmt.execute(config.getValidationQuery());
			stmt.close();
		} catch (Exception e) {
			//on any exception, assume that the connection is stale
			//and completely remove it from pool, also force a check of all
idle connections
			con.release();
			parent.testAllIdle();
		}
		
	}
}

With this approach we are trading a significant amount of performance
for some reliability of the connection not being stale.  I'm happy
with this trade-off if the interceptor is correct (I know it's not
bomb-proof as I really should have null guards around everything)

Would anyone who has knowledge of this particular code-base be able to
tell me if release() + testAllIdle() will give me the kind of
guarantee I'm after or if the configuration of testWhileIdle +
timeBetweenEvictionRunsMillis is just as valid?

Thanks,
Kev

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@tomcat.apache.org
For additional commands, e-mail: dev-help@tomcat.apache.org


Mime
View raw message