问题描述
我们只是从dbcp迁移到tomcat jdbc连接池。我们在加载中尝试了系统，并收到以下异常：
java.sql.SQLException: [IA1856] Timeout: Pool empty. Unable to fetch a connection in 1 seconds, none available[size:125; busy:90; idle:0; lastwait:1000].
at org.apache.tomcat.jdbc.pool.ConnectionPool.borrowConnection(ConnectionPool.java:632)
at org.apache.tomcat.jdbc.pool.ConnectionPool.getConnection(ConnectionPool.java:174)
at org.apache.tomcat.jdbc.pool.DataSourceProxy.getConnection(DataSourceProxy.java:124)
at com.inneractive.model.mappings.BasicPersistenceEntityMapping.getConnection(BasicPersistenceEntityMapping.java:233)
at com.inneractive.model.mappings.BasicPersistenceEntityMapping.callWithConnection(BasicPersistenceEntityMapping.java:243)
at com.inneractive.model.mappings.PersistenceEntityMapping.get(PersistenceEntityMapping.java:194)
at com.inneractive.model.data.client.ClientUtils.GetClientByExamples(ClientUtils.java:353)
at com.inneractive.client.ExternalAdRingsClientStart.getClientInfoByRequestParametersOrInsert(ExternalAdRingsClientStart.java:1329)
at com.inneractive.client.ExternalAdRingsClientStart.newClientSession(ExternalAdRingsClientStart.java:245)
at com.inneractive.simpleM2M.web.SimpleM2MProtocolBean.generateCampaign(SimpleM2MProtocolBean.java:235)
at com.inneractive.simpleM2M.web.SimpleM2MProtocolBean.generateCampaign(SimpleM2MProtocolBean.java:219)
at com.inneractive.simpleM2M.web.AdsServlet.doGet(AdsServlet.java:175)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:617)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:555)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:298)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:859)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:588)
at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:396)
at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
at java.lang.Thread.run(Thread.java:662)
注意：
[size:125; busy:90; idle:0; lastwait:1000]
不忙的连接在哪里？此后，忙碌号码一直在下降，但是我们仍然没有设法建立任何连接。
有任何想法吗？
组态：
<Resource auth="Container" driverClassName="com.mysql.jdbc.Driver"
factory="org.apache.tomcat.jdbc.pool.DataSourceFactory" loginTimeout="10000"
maxActive="35" maxIdle="35" maxWait="1000" name="jdbc/mysql"
password="-----" testOnBorrow="true" testOnReturn="false" type="javax.sql.DataSource"
url="jdbc:mysql://localhost:3306/my_db?elideSetAutoCommits=true&useDynamicCharsetInfo=false&rewriteBatchedStatements=true&useLocalSessionState=true&useLocalTransactionState=true&alwaysSendSetIsolation=false&cacheServerConfiguration=true&noAccessToProcedureBodies=true&useUnicode=true&characterEncoding=UTF-8"
username="root" validationQuery="SELECT 1"/>
env：ubuntu和tomcat6。数据库-mysql
最佳答案
看一下ConnectionPool.java的源代码，您似乎在borrowConnection()方法中命中了以下代码片段：
//we didn't get a connection, lets see if we timed out
if (con == null) {
if ((System.currentTimeMillis() - now) >= maxWait) {
throw new SQLException("[" + Thread.currentThread().getName()+"] " +
"Timeout: Pool empty. Unable to fetch a connection in " + (maxWait / 1000) +
" seconds, none available["+busy.size()+" in use].");
} else {
//no timeout, lets try again
continue;
}
}
因此，据此，您的连接为空。
在以下行中检索con的值：
PooledConnection con = idle.poll();
如果您跟踪代码，则将看到idle是(取决于您的配置，但默认情况下)FairBlockingQueue。您可以检查实现以获取提示。
通常，您始终必须关闭ResultSet，语句和连接，并且使用的连接应正确释放回池中。如果操作不正确，可能会导致连接从未关闭过。再也无法重用(连接池”leaks”)。
我建议您构造一些有关池状态的详细日志记录，并对其进行监视以找出问题所在。
Apache的一些防止数据库连接池泄漏的准则：
removeAbandoned="true"
废弃的数据库连接被删除并回收
removeAbandonedTimeout="60"
设置数据库连接空闲之前被认为已放弃的秒数
logAbandoned="true"
记录放弃了数据库连接资源的代码的堆栈跟踪。请记住，“记录废弃的连接会增加每次连接借用的开销，因为必须生成堆栈跟踪。”
我仍然认为稍微增加maxWait值(1200、1500、1700-仅是实验，从用户角度看响应时间没有差异)将清除那些仍然有问题的罕见情况。
次佳答案
“where are the connections that are not busy ?”
听起来好像它们已被删除，并且由于某种原因您的连接池没有尝试重新连接它们。
将此添加到您要连接的URL：
autoReconnect=true
并将其作为属性添加到资源中应该会导致死连接自动重新连接。
validationQuery="SELECT 1"
另外，这还应允许您查看连接断开的情况：
logAbandoned="true"
堆栈溢出有多个类似的问题。
Tomcat connection pooling,idle connections,and connection creation JDBC Connection pool not reopening Connections in tomcat
但是，也可能是您没有完全释放连接，这是导致连接断开的原因。 JDBC MySql connection pooling practices to avoid exhausted connection pool
第三种答案
似乎是池中的错误，size变量增加了，然后尝试创建连接，但是如果创建失败…我们的size值很大，并且池中没有实际连接-很糟糕：
//if we get here, see if we need to create one
//this is not 100% accurate since it doesn't use a shared
//atomic variable - a connection can become idle while we are creating
//a new connection
if (size.get() < getPoolProperties().getMaxActive()) {
//atomic duplicate check
if (size.addAndGet(1) > getPoolProperties().getMaxActive()) {
//if we got here, two threads passed through the first if
size.decrementAndGet();
} else {
//create a connection, we're below the limit
return createConnection(now, con, username, password);
}
} //end if