tomcat connection pool does not close connection?

Question

I am running into ORA Cursor exceed issues with a third party application using a tomcat connection pooling.

Besides solving those issues, we wanted to release idling sessions by setting :

minEvictableIdleTimeMillis =  60000
timeBetweenEvictionRunsMillis = 60000

But somehow, it seems that those session are not released at all (even without any traffic):

select  a.value, s.username, s.sid, s.serial#, s.machine, to_char(cast(s.logon_time as date),'hh24:mi:ss') as activesince from v$sesstat a, v$statname b, v$session s where a.statistic# = b.statistic#  and s.sid=a.sid and b.name = 'opened cursors current' and s.username = 'username' order by value desc;

This shows me "old" session/cursors and statements that are definitely oder than 1 min.

Am I missing an other option? Thanks and cheers, E.

Answer 1

this is a very tricky topic

Preface

the connection pool is designed, to limit the maximum simultanous open connections to a database on one hand and to reuse open connections in the pool on the other.

establishing a physical connection to a databse takes time. this time is 'saved' by keeping the connections open (in the pool).

Connection-Handling

before java 7, you have to ensure to close the connection, after it is used. mostly in a finally-block:

Connection conn = [retrieve DB-Connection];
try {
    // do something
} catch (SQLException e) {
    // handle exception
} finally {
    conn.close();
}

in combination with a connection pool, the connection is not physically closed, its just released to the connection pool, after Statements and ResultSets are closed, to be reusable.

since java 7, the above code should/can look like that:

try (Connection conn = [retrieve DB-Connection]) {
    // do something
} catch (SQLException e) {
    // handle exception
}

it's the new "try-catch with resources" feature. whenever the try-block is left, the resources within the parantheses of try are closed (autocloseable). the parantheses can contain severeal, semicolon-seperated autocloseable resources.

try (Connection conn = [retrieve DB-Connection];
     Statement stat = conn.createStatement();
     ResultSet result = stat.executeQuery("SELECT 1 FROM DUAL")) {
    // do something
} catch (SQLException e) {
    // handle exception
}

if the connections arent closed/released manually or handled by "try-catch with resources", the connection-pool has got a configurable fall-back-feature -

the abandoning feature

which handles unclosed/unreleased (abandoned) connections.

please refer to your tomcat-version related documentation of "JNDI-Resources how to" -> "JDBC Data Sources". this link relates to version 9.0

The abandoning feature is disabled by default and can be configured using the following properties:

• removeAbandoned - true or false: whether to remove abandoned connections from the pool. Default: false
• removeAbandonedTimeout - The number of seconds after which a borrowed connection is assumed to be abandoned. Default: 300
• logAbandoned - true or false: whether to log stack traces for application code which abandoned a statement or connection. This adds serious overhead. Default: false

Cursors and the "ORA Cursor exceed"-Exception

the maximum of open cursors is a variable database-property.

so, by executing a lot of queries within one single connection and not closing 'previous' opened/created resultsets and statements, the ORA Cursor exceed exception may occur

the following example leads to five open cursors. in this case, the try-catch with resources is used and the ResultSets, Statements and the Connection are closed automatically by leaving the try-block:

try (Connection conn = [retrieve DB-Connection];
     Statement stat0 = conn.createStatement();
     ResultSet result0 = stat0.executeQuery(...);
     Statement stat1 = conn.createStatement();
     ResultSet result1 = stat1.executeQuery(...);
     Statement stat2 = conn.createStatement();
     ResultSet result2 = stat2.executeQuery(...);
     Statement stat3 = conn.createStatement();
     ResultSet result3 = stat3.executeQuery(...);
     Statement stat4 = conn.createStatement();
     ResultSet result4 = stat4.executeQuery(...);) {
    // do something
} catch (SQLException e) {
    // handle exception
}

its better to split them:

try (Connection conn = [retrieve DB-Connection]) {
    try (Statement stat0 = conn.createStatement();
         ResultSet result0 = stat0.executeQuery(...)) {
        // do something
    }
    try (Statement stat1 = conn.createStatement();
         ResultSet result1 = stat1.executeQuery(...)) {
        // do something
    }
    try (Statement stat2 = conn.createStatement();
         ResultSet result2 = stat2.executeQuery(...)) {
        // do something
    }
    try (Statement stat3 = conn.createStatement();
         ResultSet result3 = stat3.executeQuery(...)) {
        // do something
    }
    try (Statement stat4 = conn.createStatement();
         ResultSet result4 = stat4.executeQuery(...);) {
        // do something
    }
} catch (SQLException e) {
    // handle exception
}

before java 7, the error may also occur, if Statements and ResultSets arent closed manually and the connection itself isnt closed/released

the following example is a 'bad practice' example, neither the ResultSets and Statements nor the Connection are closed

Connection conn = [retrieve DB-Connection];
try {
    Statement stat0 = conn.createStatement();
    ResultSet result0 = stat0.executeQuery(...);
    Statement stat1 = conn.createStatement();
    ResultSet result1 = stat1.executeQuery(...);
    Statement stat2 = conn.createStatement();
    ResultSet result2 = stat2.executeQuery(...);
    Statement stat3 = conn.createStatement();
    ResultSet result3 = stat3.executeQuery(...);
    Statement stat4 = conn.createStatement();
    ResultSet result4 = stat4.executeQuery(...);
} catch (SQLException e) {
    // handle exception
} 

lets assume the maximum for open cursors is 20 and there is one bad-practice-code-snippet, the application may run very fast into the "ORA Cursor exceed"-Exception

in this case, the connection-pools

abandoning feature

takes care for you and closes/releases the connection(s) and implicitly the Statements and ResultSets within.

Conclusion

the

abandoning feature

is only a fallback.

its better to ensure the ResultSets, Statements and Connections are handled and closed correct.