我们刚刚从 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&amp;useDynamicCharsetInfo=false&amp;rewriteBatchedStatements=true&amp;useLocalSessionState=true&amp;useLocalTransactionState=true&amp;alwaysSendSetIsolation=false&amp;cacheServerConfiguration=true&amp;noAccessToProcedureBodies=true&amp;useUnicode=true&amp;characterEncoding=UTF-8"
                username="root" validationQuery="SELECT 1"/>

环境:ubuntu 和 tomcat 6.db - 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;
            }
        }

所以根据这个，你的连接是Null。

con的值取回就行了:

PooledConnection con = idle.poll();

如果您跟踪代码，您将看到 idle 为(取决于您的配置，但默认情况下)FairBlockingQueue .您可以查看实现以获取提示。

一般来说，您总是必须关闭结果集、语句和连接，并且应该将使用过的连接正确地释放回池中。 未正确执行此操作可能会导致连接永远不会关闭 => 永远无法再次使用(连接池“泄漏”)。

我建议您对池的状态构建一些详细的日志记录并对其进行监控以隔离问题。

Apache 的一些防止数据库连接池泄漏的指南:

removeAbandoned="true"

废弃的数据库连接被删除和回收

removeAbandonedTimeout="60"

设置数据库连接在被视为放弃之前空闲的秒数

logAbandoned="true"

记录放弃数据库连接资源的代码的堆栈跟踪。请记住，“记录废弃的连接会增加每次连接借用的开销，因为必须生成堆栈跟踪。”

我仍然认为稍微增加 maxWait 值(1200、1500、1700 - 只是实验，从用户角度来看响应时间不会有差异)将清除那些罕见的情况，在这种情况下，您还是有问题。

关于mysql - tomcat jdbc连接池中缺少连接，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/11450999/