Re: Dbcp // Validation query timeout
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,
Re: Dbcp // Validation query timeout
On 5/11/07, Tworkiewicz, Adam <[EMAIL PROTECTED]> wrote: Hi, We use dbcp for connection pooling in our application that talks to Oracle 10g RAC (2 nodes). Our dbcp config is attached below. We are seeing undesired behaviour when we test db failover. Since we use load balancing connections in the pool point to both db servers. When we shutdown one of the db nodes our ping query hangs waiting for TCP/IP traffic. Since the box is down there is no traffic and the query hangs until a TCP/IP timeout occures. Is there a way to set up a timeout on the validation query? Currently, there is no way to do that. Would you mind opening JIRA ticket requesting this feature? You can do that here: http://jakarta.apache.org/commons/dbcp/issue-tracking.html Patches welcome. Phil - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: Dbcp // Validation query timeout
On 5/11/07, Greg Hawkes <[EMAIL PROTECTED]> wrote: 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. Just so I understand the use case here, is this something that happens every n queries, or does the database just periodically slow down uniformly, so all queries are slow? Also, what versions of commons dbcp and commons pool are you running? 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. These are valid points. Please open JIRA tickets and make suggestions for improvement in the tickets and discussion on commons-dev. Patches are welcome. The core design issue is in commons pool's GenericObjectPool (GOP), which arguably violates Block's principle above in order to ensure thread safety in its 1.x design. Work on a 2.0 version of pool has begun and discussion of this and other topics is welcome on the commons-dev mailing list. Version 1.3 of commons pool added synchronization to GOP to deal with thread safety issues reported in POOL-26 and some other issues resolved in the 1.3 release. Unfortunately, this may have exacerbated the problem mentioned here. If you are using dbcp 1.2.2 and pool 1.3, you could fall back to pool 1.2 (still using dbcp 1.2.2); but in this case you should review the release notes for pool 1.3 to see what other bug fixes you would be missing: http://jakarta.apache.org/commons/pool/release-notes-1.3.html 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... Not a problem. To start, you could just add the sources for your new classes to a JIRA ticket. Make sure you can legally contribute them and for new classes its best to include the apache license header (cut and paste from any current source, or see http://www.apache.org/licenses/LICENSE-2.0.html#apply). Have a look at http://www.apache.org/dev/contributors.html http://jakarta.apache.org/commons/svninfo.html for more information about how to checkout sources from subversion and submit patches. Feel free to ask here or on commons-dev if you have problems getting set up. Thanks for the feedback and thanks in advance for your contributions. Phil - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]