Hi Adam,
Funny you should ask... I have recently been wondering that exact same
thing. In my case, a very, very slow validation query (approx 1.7
hours!) was causing Tomcat to crash. All 150 Tomcat worker threads were
eventually consumed, waiting for DBCP to return a connection.
Now, I have no idea why the database (Oracle 10g) was so thoroughly
wedged; I have yet to investigate that side of it. I had a look around
the DBCP code, though, and found two things: 1) there is no facility for
a validation time-out, and; 2) the validation query is executed /within
a synchronized block/ on the connection pool. This is in violation of
the design principals described in Joshua Bloch's Effective Java, Item
49, and is an excellent example of why Bloch is right on the money. In
this case, any client that wants a connection is forced to wait until
the pool validates the connections for all previous clients.
For this reason, I believe that the existing design of DBCP is flawed.
The validation query facility built into classes such as BasicDataSource
should not be used. Instead, any validation query should be executed
/after/ the connection is obtained from the connection pool.
To achieve this, I developed the ValidatingDataSource, below. This
class is a wrapper around a PoolingDataSource, that overrides
getConnection(). It also provides accessors for the validation query and
the validation query time-out. When the client calls getConnection(),
the ValidatingDataSource obtains a connection from the PoolingDataSource
and invokes the validation query on it. If the query succeeds, the
connection is return to the client. If it fails, or times-out, the
connection is removed from the pool, and another connection obtained.
This process continues indefinitely; some sort of limit would be a good
idea, and I'll implement one Real Soon Now.
I have also created a BetterDataSource class, that extends
BasicDataSource to use the ValidatingDataSource. However, the name
BetterDataSource is rather pretentious and I'm too embarrassed to list
the code here.
Regards,
Greg [EMAIL PROTECTED]
PS: I apologise for including this chunk of code in my reply. I really
should learn how to use the DBCP patch process...
/*
* ValidatingDataSource.java
*/
package com.greghhawkes.util.dbcp;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.sql.DataSource;
import org.apache.commons.dbcp.DelegatingConnection;
import org.apache.commons.dbcp.PoolableConnection;
import org.apache.commons.dbcp.PoolingDataSource;
/**
* A data source wrapper that validates the connection returned by the
underlying PoolingDataSource.
* @author Greg Hawkes
*/
public class ValidatingDataSource implements javax.sql.DataSource {
private PoolingDataSource m_delegate;
private String m_validationQuery;
private int m_validationTimeout;
/**
* Creates a new instance of ValidatingDataSource
*/
public ValidatingDataSource(PoolingDataSource delegate) {
m_delegate = delegate;
}
/**
* Obtain and (optionally) validate a connection from the underlying
datasource.
*/
public Connection getConnection() throws SQLException {
m_delegate.setAccessToUnderlyingConnectionAllowed(true);
for ( ; ; ) {
Connection conn = m_delegate.getConnection();
if (conn == null) {
return null;
} else if (isValid(conn)) {
return conn;
}
// Validation failed; destroy the connection
DelegatingConnection dc = (DelegatingConnection) conn;
PoolableConnection c = (PoolableConnection) dc.getDelegate();
c.reallyClose();
}
}
public Connection getConnection(String username, String password)
throws SQLException {
throw new UnsupportedOperationException();
}
public PrintWriter getLogWriter() throws SQLException {
return m_delegate.getLogWriter();
}
public void setLogWriter(PrintWriter out) throws SQLException {
m_delegate.setLogWriter(out);
}
public void setLoginTimeout(int seconds) throws SQLException {
m_delegate.setLoginTimeout(seconds);
}
public int getLoginTimeout() throws SQLException {
return m_delegate.getLoginTimeout();
}
public String getValidationQuery() {
return m_validationQuery;
}
public void setValidationQuery(String validationQuery) {
m_validationQuery = validationQuery;
}
public int getValidationTimeout() {
return m_validationTimeout;
}
public void setValidationTimeout(int validationTimeout) {
if (validationTimeout 0) {
validationTimeout = 0;
}
m_validationTimeout = validationTimeout;
}
/**
* Test whether the validationQuery can be executed on the connection
