Oracle Connection Pools in Tomcat Part 3 – Combating Invalid and Stale Connections

In our Oracle Connection Pool series we have identified how to implement and basic configurations. Before we examine more higher degree advanced configurations, it is important to note one of the drawbacks of the Oracle Connection Pool.

It has come to my attention that there is a fatal flaw within the Oracle Connection Pool architecture and this has been noted in many help forums around the internet and world wide web. There are many complaints regarding Invalid and Stale Connections when utilizing the Oracle Connection Pool for one’s database connection cache needs.

Here is the error that I see when my app crashes:

<org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is java.sql.SQLException: Invalid or Stale Connection found in the Connection Cache>

This has indeed become a thorn in my side, since it is mandatory that I use the Oracle Connection Pool in order to let my app utilize Oracle only functionality. That is to say, I just can’t throw away my relational db and replace it with some NoSQL MongoDB type of thing. Rather, indeed I am stuck with the Oracle Connection Pool, so what can one do to combat these Invalid and Stale Connections that are persisting behind the scenes of my app?

First, let’s check the configurations and see if we can do anything there. As noted in Part 2 of this series, there are certain connectionCacheProperties that Oracle has put forth so us architects and developers may tweak the connection pool to act accordingly depending upon our business requirements.

Checking out some Oracle documentation regarding the Oracle Connection Pool and associated Connection Cache Properties, it is fair to say that Oracle accounted for the existence of Invalid and/or Stale Connections.

An important property to note is: ValidateConnection

Setting ValidateConnection to true causes the connection cache to test every connection it retrieves against the underlying database. If a valid connection cannot be retrieved, then an exception is thrown.

Default: false

Great! Now add this to your database connection information along with some other properties to govern your connection pool wisely.

<Resource auth="Container"
description="Oracle Datasource"
factory="oracle.jdbc.pool.OracleDataSourceFactory"
connectionCacheName=myCacheName"
connectionCacheProperties="{MaxStatementsLimit=10, MinLimit=1, MaxLimit=200, ValidateConnection=true}"
connectionCachingEnabled="true"
name="jdbc/test"
type="oracle.jdbc.pool.OracleDataSource"
url="jdbc:oracle:thin:@oracleserver:1521/oracleserver"
user="testschema"
password="testschema"/>

Henceforth, Oracle should double check each connection and promptly exterminate any bogus connections that attempt to make it through to our app.

Has anyone had any success using the infamous ValidateConnection property? Does it do its job? We shall see in the next edition of Oracle Connection Pools in Tomcat.

Advertisements

About javaclaus

Java Programmer, Code master, mountain biker, snowboarder, etc.
This entry was posted in Database, Oracle, Spring Framework, Tomcat and tagged , , , , . Bookmark the permalink.

3 Responses to Oracle Connection Pools in Tomcat Part 3 – Combating Invalid and Stale Connections

  1. vkudire says:

    When do you post the next article about ValidateConnection property?
    I think this property merely checks if the connection is valid or not, If not it throws an exception instead of getting a new connenction

    • javaclaus says:

      Thanks for the reply. The next addition of the Oracle Connection Pool series will be coming shortly. Are you using code similar to what I have posted and have you had success using the ValidateConnection property?

  2. Kofa says:

    ValidateConnection=true just results in an exception.
    OracleImplicitConnectionCache
    protected Connection getConnection(String user, String passwd, Properties connAttr)
    throws SQLException
    {
    […]
    OraclePooledConnection pc = null;
    […]
    if ((pc != null) && (pc.physicalConn != null))
    {
    conn = pc.getConnection();
    if (conn != null)
    {
    if ((this.cacheValidateConnection) && (testDatabaseConnection((OracleConnection)conn) != 0))
    {
    ((OracleConnection)conn).close(4096);
    […]
    SQLException __ex__ = DatabaseError.createSqlException(getConnectionDuringExceptionHandling(), 143);
    __ex__.fillInStackTrace();
    throw __ex__;
    }
    […]

    We’re now checking whether these settings are of any use:
    InactivityTimeout
    Sets the Maximum time, in seconds, a connection in the cache can remain idle (that is not checked out of the cache). Defaults to 0.
    PropertyCheckInterval
    Sets the time interval for the cache daemon thread to enforce the time out limits. Defaults to 900 seconds (15 minutes).
    http://docs.oracle.com/cd/E14072_01/appdev.112/e13995/oracle/jdbc/pool/OracleDataSource.html

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s