Spring ORM with C3P0 or DBCP is a connection leak

Question

In recent versions of both DBCP and C3P0, using Spring Ibatis support, I ran into a problem when both connections leak.

The scenario is that there is a log running SQL that locks multiple tables. This leads to the maximum amount of connections in my pool, as users run queries that fall into locked tables. Finally, the administrator goes into MySQL and executes the kill query <id> in long SQL.

If there are enough threads (in my case about 50 or more) that are waiting for the database thread to be checked back to the pool, then I see the following in the 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 are waiting around FOREVER.

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

I know that there is a configuration that can solve this problem (timeout, etc.), but I wonder why this happens in the first place? Why are active threads not returning to the pool when there are 50 or more threads waiting to connect and I kill long SQL?

Update: I should have clearly indicated that I am using Spring 3.0.2 and Ibatis 2.3. I use SqlMapClientTemplate, which controls mine for me. At this point, I begin to think that this Ibatis 2.3 does not handle the heavy load correctly.

Answer 1

When using c3po, I highly recommend trying this feature . Connection leaks usually occur when making database changes outside of a transaction. A connection with irregular entries cannot be reused and lost from the pool. If you enable this debugging with a sufficiently long timeout, you can see stack traces with suspicious database operations.

Verify that the operations observed in the stack trace are managing the transactions correctly. Also control the use of the connection pool by setting c3p0 loggers to the debugging level.

Answer 2

Like what @BalusC asks if you close your connections? They should be closed in the finally clause of your Java try-catch-finally . Wrap each connection.close() method in its own try { con.close() } catch (Exception ignore) {}

You do not have to do this for ResultSet , then Statement , then Connection in that order. Declare Connection , Statement and ResultSet outside your initial try block as null and instantiate the 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 use it, and not all of the above code in every finally that wraps the SQL call, you can select the DbUtils Apache Commons DbUtils utility class.

 import org.apache.commons.dbutils.DbUtils; 

and apply the closeQuietly methods. Instead, the finally block will look like this:

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

Answer 3

Just add a comment @BalusC and @JoshDM answer when you call close() on Connection in your Java code, behind the scenes it will actually just check that connection back to the connection pool and not physically close it. That's why it is important to always close connections through your Java code, regardless of whether the underlying JDBC driver combines it.

More on this on this post .