tomcat-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Filip Hanik - Dev Lists <devli...@hanik.com>
Subject Re: tomcat-jdbc pool interceptor
Date Wed, 09 Dec 2009 01:55:38 GMT
hi Kevin,
hi Kevin, what you seem to be missing in your config is

testOnBorrow="true"

Filip

On 12/02/2009 05:11 AM, Kevin Jackson wrote:
> 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
>
>
>    


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


Mime
View raw message