How best to close connections and avoid inactive sessions while using C3P0?

Question

I am using c3p0 for my connection pooling. The ComboPooledDataSource I use is configured as below.

@Bean
public DataSource dataSource() {

    ComboPooledDataSource dataSource = new ComboPooledDataSource();
    dataSource.setUser("user");
    dataSource.setDriverClass("oracle.jdbc.OracleDriver");
    dataSource.setJdbcUrl("test");
    dataSource.setPassword("test");
    dataSource.setMinPoolSize("10");
    dataSource.setMaxPoolSize("20");
    dataSource.setMaxStatements("100");
    return dataSource;
}

I am facing some issues with this. I get warnings that this might leak connections. Also the below error from time to time,

as all the connections are being used up.

java.sql.SQLException: Io exception: Got minus one from a read call
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:255)
    at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:387)
    at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:439)
    at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:165)
    at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:35)
    at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:801)
    at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:135)
    at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection

(WrapperConnectionPoolDataSource.java:182)
    at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection

(WrapperConnectionPoolDataSource.java:171)
    at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource

(C3P0PooledConnectionPool.java:137)
    at com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1014)
    at com.mchange.v2.resourcepool.BasicResourcePool.access$800(BasicResourcePool.java:32)
    at com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask.run(BasicResourcePool.java:1810)
    at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547)

And from the DB stat, could see almost 290 inactive connections. I am having around 8 applications deployed in two servers,

connecting to the same DB.

My queries are

1. How do I make sure th connections are closed and not to have these many inactive connections?
2. Would configuring idle time and timeout resolve this issue?
3. What would happen if the server is brought down/tomcat is shutdown, will the connections remain open?
4. Connections are mainly used during startup to load cache, so is there a way of not using these connections afterwards?
5. What should I do for the existing inactive connections?

Answer 1

• Given maxPoolSize of 20 and eight deployments, you should expect to see up to 180 Connections, which may be inactive if the application has seen periods of traffic which has now subsided. You have configured nothing to encourage a fast scaling down of your pools -- set maxIdleTime and/or maxIdleTimeExcessConnections and/or maxConnectionAge.
• You should probably tell Spring how to close the DataSource you've defined. Use @Bean(destroyMethodName="close") instead of @Bean alone above your dataSource() method.
• You have not configured any sort of Connection testing, so even broken Connections might remain in the pool. Please see Simple Advice On Connection Testing.
• If the issue were a Connection leak, clients would eventually hang indefinitely, as the pool would be out of Connections to check out, but would already have reached maxPoolSize, and so wouldn't be able to acquire more from the DBMS. Are you seeing clients hang like that?

• The way you avoid Connection leaks is, post-Java7, to always acquire Connections from your DataSource via try-with-resources. That is, use...

  try ( Connection conn = myDataSource.getConnection() ) {
    ...
  }
  

  rather than just calling getConnection() in a method that might throw an Exception or in a try block. If you are using an older version of Java, you need to use the robust resource cleanup idiom, that is, acquire the Connection in a try block and be sure that conn.close() is always closed in the finally block, regardless of any other failures in the finally block. If you are not working with the DataSource directly, but letting Spring utilities work with it, hopefully those utilities are doing the right thing. But you should post whatever warning you are receiving that warns you of potential Connection leaks!

• If your application has little use for Connections after it has "warmed up", and you want to minimize the resource footprint, set minPoolSize to a very low number, and use maxIdleTime and/or maxIdleTimeExcessConnections and/or maxConnectionAge as above to ensure that the pool promptly scales down when Connections are no longer in demand. Alternatively you might close() the DataSource when you are done with its work, but you are probably leaving that to Spring.