c3p0 how to shutdown all the database connections and re-open them when need?

Question

I have a TimerTask which runs one time (about 1 or 2 hours) each day. And at each running, it will create hundreds of threads to do some compute work for each table in MySQL database. and I use the c3p0 as the database source connection pool (each thread get the connection before computing and close the connection after computing). I set the connection pool configuration as below,

cpDs = new ComboPooledDataSource();
cpDs.setMinPoolSize(10);
cpDs.setMaxPoolSize(20);
cpDs.setMaxStatementsPerConnection(10);

During testing, I found all the database connections were lost in the next day's running, and lots of "Communications link failure due to underlying exception" were shown in the log file. so I added the following configurations in order to test the connection before using it.

// 7 hours, less than MYSQL default value - 8 hours
cpDs.setMaxIdleTime(25200);
cpDs.setTestConnectionOnCheckout(true);
cpDs.setPreferredTestQuery("select 1");

but I observe that there are always 10 connections stay sleeping/idle state (via SQL 'show processlist;') when the TimerTask is not running, and I often see the famous "APPARENT DEADLOCK!!!" warning (which the bug is still in open state in the c3p0 project http://sourceforge.net/tracker/?func=detail&aid=3432139&group_id=25357&atid=383690).

So is there a way to close all the connections when all the computation work are finished and re-construct the connections in the next day when the task are performed again? Thank you.

Regards, Joey

Answer 1

If you'd like all of the connections to close, set minPoolSize and initialPoolSize to 0. Also, I would suggest reducing maxIdleTime to a smaller value like 600 (10 minutes). This combination of settings will enable the pool to "drain" quickly after your workers are finished.

You can also force all connections to close using one of the reset methods exposed in ComboPooledDataSource, but if the pool is configured correctly that shouldn't be necessary.