Spring ORM with C3P0 or DBCP is leaking connections

Question

On the latest versions of both DBCP and C3P0, using Spring's Ibatis support, I am running into an issue where both leak connections.

The scenario is that there is a log running SQL which locks multiple tables. This causes the connections in my pool to max out as users trigger queries which hit the locked tables. Finally, the administrator goes into MySQL and does a kill query <id> on the long running SQL.

If there is enough threads (in my case around 50 or more) which are waiting around for a DB thread to be checked back in to the pool, then I see something like the following in 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 

and these threads wait around FOREVER.

This does not happen if the pool is maxed out, and only a few (around 5) threads are waiting for a free connection in the pool.

I know there is configuration that can fix this problem (setting timeout etc.), but I am interested in why this is happening in the first place? Why are the active threads not being returned to the pool, when there are 50 or more threads waiting for a connection and I kill the long running SQL?

Update: I should have made it clear that I am using Spring 3.0.2 and Ibatis 2.3. I use the SqlMapClientTemplate, which manages my connections for me. At this point, I am beginning to think it is Ibatis 2.3 not handling heavy load correctly.

Answer 1

When using c3po, I strongly recommend trying this feature. Connection leaks usually appear when you perform database modifications outside transaction. Connection with uncommited writes cannot be reused and is lost from the pool. If you enable this debugging with reasonably long timeout, you will be able to see the stack traces with suspicious database operations.

Make sure the operations seen in the stack traces manage transactions correctly. Also monitor connection pool usage by setting c3p0 loggers to debug level.