#74 close c3p0 connection does not release the connection

open
nobody
None
5
2014-08-14
2009-07-28
shaoxian yang
No

Hi, I wrote a java program, where i create a ComboPooledDataSource. Then, i just execute one sql over and over. while the program runs, i monitor the mysql administrator user connection. The test is single threaded, and just execute some query over and over. So at most, the program only need 1 open connection at any given time. I set the connection pool min size to be 5, and max to be 30. If I run the query for many round, I see the open connections ramp up. Ideally, I would hope there is only 5 connections (only 1 is used to serve my query). However, the open connection quickly ramp up to more than 5, sometimes, it runs to max connection very quickly. I wrote the test, because one time i see more than max connections being generated, and it hangs my mysql server. Therefore, i am very concerned on this.

This is my test case, please take a look and let me know if it is valid:

@Test
public void testC3P0() throws Exception{

ComboPooledDataSource ds = new ComboPooledDataSource();
ds.setDriverClass("com.mysql.jdbc.Driver");
ds.setUser("root");
ds.setPassword("root");
ds.setJdbcUrl("jdbc:mysql://localhost:3306/podcasts_catalog");
ds.setMinPoolSize(5);
ds.setMaxPoolSize(30);
ds.setAcquireIncrement(1);

Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;

for (int i = 0; i < 2000000; i++) {
try {
conn = ds.getConnection();
String sql = "select * from category order by pos";
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
// while (rs.next()) {
// System.out.println("name: " + rs.getString("name"));
// }
} finally {
if (rs != null)
rs.close();
if (ps != null)
ps.close();
if (conn != null && !conn.isClosed())
conn.close();
}
//sleep(1); (if do not thread.sleep 1ms, then connection ramp up to maximum size almost immediately. if sleep for 1ms, it will ramp up more than min, but not reaching to max so quickly)

}

DataSources.destroy(ds);

}

The only reason that connections ramp up i can think of, is that conn.close() does not really release the actual connection, so that when new query executed, it accquires new connection from pool.

Discussion

  • Steve Waldman
    Steve Waldman
    2009-07-28

    hi,

    connections are refurbished and released asyncronously. so in a hard loop it is possible that the Thread running the loop would prevent the release, although the IO ops in executing your query might give some space for the async release to occur. But perhaps your JDBC driver does local caching of the repeated query, or you are using an in-memory database. (However, caching would be kind of bad, though, unless you've turned autocommit off.) That the explicit Thread.sleep() should helps indicates that this is the issue.. If you Thread.sleep() for 100 ms rather than 1 ms, I'd expect you'd see no ramping at all. In a more realistic scenario, where DB transactions involve significant latency for network IO and/or DB query time, there is no need to sleep or yield explicitly, client threads naturally take pauses that permit asynchronous maintenance tasks.

    Obviously, all of this depends on the particulars of the Thread implementation of your JVM and its scheduling policy.

    BTW you are close, but not quite there, in assuring that there would be no Connection leaks in this code. Consider what happens if rs.close() fails on an SQLException. (It can happen.)

     
  • shaoxian yang
    shaoxian yang
    2009-07-28

    Thanks for your comment, swaldman.

    How soon will those asynchronous close operation finish? From what I observe in the test case above (with running many iteration without any interview between each round, which takes around 200s. The connetions never goes down during that period. The open connection only goes up close/equal to max size. Is it reasonable?

    The other thing I notice is that the test takes longer to run with connection pool than without it.
    Isn't that connection pool supposed to make your jdbc query faster?
    I agree with you that letting the process pause for around 100ms will not ramp up the open connections. I will still double check that tomorrow. But i think I observed that before.

    Basically, I am more worrying about highly concurrent system, where multiple query requests just come in over and over. If every connection takes around 1 second to close, that is slow. How do I verify the previous used connection is back to use again? Any particular logging I can turn on to observe this?

    As for your comment on depending on "particulars of the Thread
    implementation of your JVM and its scheduling policy". Why is that, because the multithread implementation of pool?

    I know my conn.close() is faulty. That was a test, not production ready code. Thanks for pointing out though.

    Shaoxian Yang