You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@commons.apache.org by "Tworkiewicz, Adam" <Ad...@sabre-holdings.com> on 2007/05/11 19:43:30 UTC
Dbcp // Validation query timeout
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?
Thanks,
Adam
<bean id="myDataSource"
class="org.apache.commons.dbcp.BasicDataSource"
destroy-method="close">
<property name="driverClassName"
value="oracle.jdbc.driver.OracleDriver" />
<property name="url"
value="jdbc:oracle:thin:@(DESCRIPTION
=(ADDRESS = (PROTOCOL = TCP)(HOST 10.19.60.28)(PORT= 1521))(ADDRESS =
(PROTOCOL = TCP)(HOST = 10.19.60.30)(PORT =
1521))(FAILOVER=ON)(LOAD_BALANCE = yes)(CONNECT_DATA =(SERVER =
DEDICATED)(SERVICE_NAME = sgsasd))))" />
<property name="username" value="****" />
<property name="password" value="****" />
<property name="validationQuery" value="select 1 from
dual" />
<property name="testOnBorrow" value="true" />
<property name="initialSize" value="15" />
<property name="maxActive" value="15" />
<property name="maxIdle" value="15" />
</bean>
---------------------------------------------------------------------
To unsubscribe, e-mail: commons-dev-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-dev-help@jakarta.apache.org
Re: Dbcp // Validation query timeout
Posted by Phil Steitz <ph...@gmail.com>.
On 5/11/07, Greg Hawkes <gr...@onemancoding.com> 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 <gr...@onemancoding.com>
>
> 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: commons-dev-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-dev-help@jakarta.apache.org
Re: Dbcp // Validation query timeout
Posted by Greg Hawkes <gr...@onemancoding.com>.
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 <gr...@onemancoding.com>
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,
*/
private boolean isValid(Connection conn) {
String sql = getValidationQuery();
if (sql == null) {
// No validation query; assume connection is valid
return true;
}
Statement stmt = null;
ResultSet rs = null;
try {
stmt = conn.createStatement();
stmt.setQueryTimeout(getValidationTimeout());
rs = stmt.executeQuery(sql);
if (!rs.next()) {
// Query did not return a record
return false;
}
return true;
} catch (SQLException sqx) {
// Exception occurred while executing validation query
return false;
} finally {
if (rs != null) {
// Close the database record set
try {
rs.close();
} catch (SQLException sqx) {
}
}
if (stmt != null) {
// Close the database statement
try {
stmt.close();
} catch (SQLException sqx) {
}
}
}
}
}
--------------------
Tworkiewicz, Adam 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?
>
> Thanks,
> Adam
>
Re: Dbcp // Validation query timeout
Posted by Phil Steitz <ph...@gmail.com>.
On 5/11/07, Tworkiewicz, Adam <Ad...@sabre-holdings.com> 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: commons-dev-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-dev-help@jakarta.apache.org