TomcatExpert

JDBC Connection Pool Timeout Errors

posted by SpringSource on February 9, 2010 12:44 PM

If the database server times out a connection, the connection pool on Tomcat side would not be aware of its disconnection.

If the database server times out a connection, the connection pool on Tomcat side would not be aware of its disconnection, and could throw the following exception in Tomcat:

Caused by: com.ibm.db2.jcc.c.DisconnectException: A communication error has been detected.
Communication protocol being used: Reply.fill().
Communication API being used: InputStream.read().
Location where the error was detected: Read timed out.
Communication function detecting the error: *. Protocol specific error codes(s) TCP/IP SOCKETS DB2ConnectionCorrelator: CCD3560A.K404.080507130918
at com.ibm.db2.jcc.b.a.a(a.java:373)
at com.ibm.db2.jcc.b.gb.b(gb.java:191)
at com.ibm.db2.jcc.b.gb.c(gb.java:238)
at com.ibm.db2.jcc.b.gb.c(gb.java:353)
at com.ibm.db2.jcc.b.gb.v(gb.java:1362)

In order to prevent the exception, the connection pool can validate the connection.

In doing so, two attributes can be added to the JDBC resource, testOnBorrow and validationQuery respectively:

name="jdbc/dsSos"
auth="Container"
type="javax.sql.DataSource"
username="******"
password="*******"
driverClassName="com.ibm.db2.jcc.DB2Driver"
factory="org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory"
maxIdle="2"
maxWait="10000"
url="jdbc:db2://mydbURL:50000/MYDB"
maxActive="15"
removeAbandoned="true"
removeAbandonedTimeout="300"
testOnBorrow="true"
validationQuery="some sql..."
logAbandoned="true"
"some sql..."

In the above example should be a valid SQL query that the connection pool will execute to ensure a live connection is returned to the application. The embedded query should be simple and efficient. For example:

For Oracle:

validationQuery="select 1 from dual"

 

For MySQL:

validationQuery="select 1"
For more than 10 years, SpringSource employees have been supporting Apache technologies, with unparalleled experience and commitment to the Apache Software Foundation. More than 400 of the Fortune 500 count on SpringSource to support their mission-critical business applications. Leaders of the Apache Software Foundation, including Board Members, work at SpringSource and dedicate a significant amount of time further developing the Apache Tomcat open source project. Over the last 2 years, 95% of the issues fixed in the Apache Tomcat project were fixed by SpringSource engineers. For more information on how SpringSource can help your enterprise, see the SpringSource website, or call 800/444-1935.

Comments

Impact of two parameters

The addition of these two parameters very onerous database?

JAva

Post new comment

CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.