Around the latest versions of both DBCP and C3P0, using Spring's Ibatis support, I'm encountering an problem where both leak connections.

The scenario is the fact that there's a log running SQL which locks multiple tables. This will cause the connections during my pool to max out as customers trigger queries which hit the locked tables. Finally, the administrator adopts MySQL and does a kill query <id> around the lengthy running SQL.

If there's enough threads (during my situation around 50 or even more) that are holding out for any DB thread to become checked in towards the pool, i quickly see something similar to the next inside a thread dump:

 java.lang.Thread.State: WAITING (on object monitor)
    at java.lang.Object.wait(Native Method)
    at com.mchange.v2.resourcepool.BasicResourcePool.awaitAvailable(BasicResourcePool.java:1315)
    at com.mchange.v2.resourcepool.BasicResourcePool.prelimCheckoutResource(BasicResourcePool.java:557)
    - locked <0x00002aaacbb01118> (a com.mchange.v2.resourcepool.BasicResourcePool)
    at com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource(BasicResourcePool.java:477)
    at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:525)
    at com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource.getConnection(AbstractPoolBackedDataSource.java:128)
    at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:113)
    at 

or

  java.lang.Thread.State: WAITING (on object monitor)
    at java.lang.Object.wait(Native Method)
    at java.lang.Object.wait(Object.java:485)
    at org.apache.commons.pool.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:1104)
    - locked <0x00002aab0f030620> (a org.apache.commons.pool.impl.GenericObjectPool$Latch)
    at org.apache.commons.dbcp.PoolingDataSource.getConnection(PoolingDataSource.java:106)
    at org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:1044)
    at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:113)
    at 

which threads hold out FOREVER.

This doesn't happen when the pool is at their maximum, and just a couple of (around 5) threads are awaiting a totally free connection within the pool.

I understand there's configuration that may fix this issue (setting timeout etc.), however i am thinking about why this really is happening to begin with? How come the active threads not came back towards the pool, when you will find 50 or even more threads awaiting an association and that i get rid of the lengthy running SQL?

Update: I ought to have managed to get obvious that i'm using Spring 3..2 and Ibatis 2.3. I personally use the SqlMapClientTemplate, which handles my connections for me personally. At this time, I'm starting to believe it is Ibatis 2.3 not handling heavy load properly.

Similar to what @BalusC asks, are you currently closing connections within the finally clause of the Java try-catch-finally, and wrapping each connection.close() method in the own try { con.close() } catch (Exception ignore) {}

Optionally you want to do so for ResultSet then Statement then Connection for the reason that order. Declare your Connection, Statement, and ResultSet outdoors your initial try block as null and instantiate inside your try block.

Connection con = null;
Statement stmt = null;
ResultSet rs = null;

try {

    con = getConnectionFromPoolMethod();

    // ...
    // instantiate your statement and result set as normal
    // make your sql call; 
    // extract data from result set to appropriate POJO

} catch (Exception ex) {
    // handle your exception, log, wrap, enhance or rethrow or whatever
} finally {
    if (rs != null) try { rs.close(); } catch (Exception ignore) {}
    if (stmt != null) try { stmt.close(); } catch (Exception ignore) {}
    if (con != null) try { con.close(); } catch (Exception ignore) {}
}

If you are up for this, instead of all of the above code in each and every finally that systems a SQL call, you are able to choose to make use of the DbUtils utility class of Apache Commons DbUtils.

import org.apache.commons.dbutils.DbUtils;

and apply the closeQuietly techniques. The finally block may be like this rather:

} finally {
    DbUtils.closeQuietly(con, stmt, rs);
}