c3p0如何关闭所有数据库连接并在需要时重新打开它们? [英] c3p0 how to shutdown all the database connections and re-open them when need?

问题描述

我有一个TimerTask，它每天运行一次(大约1或2个小时).并且在每次运行时，它将创建数百个线程来为MySQL数据库中的每个表执行一些计算工作.我将c3p0用作数据库源连接池(每个线程在计算之前获取连接，在计算之后关闭连接).我将连接池配置设置如下，

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");

但是我观察到，当TimerTask未运行时，总是有10个连接保持睡眠/空闲状态(通过SQL'show processlist;')，而且我经常看到著名的"APPARENT DEADLOCK ！！！"警告(在c3p0项目中，该错误仍处于打开状态 http://sourceforge.net/tracker/?func=detail&aid=3432139&group_id=25357&atid=383690 ).

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.

关于， 乔伊

推荐答案

如果要关闭所有连接，请将minPoolSize和initialPoolSize设置为0.此外，我建议将maxIdleTime减小为较小的值，例如600(10分钟).这些设置的组合将使您的工作人员完成工作后，池可以快速耗尽".

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.

您还可以使用ComboPooledDataSource中公开的reset方法之一来强制关闭所有连接，但是如果池配置正确，则不必要.

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.

这篇关于c3p0如何关闭所有数据库连接并在需要时重新打开它们?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！