org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot get a connection, pool error Timeout waiting

今天在运行项目的时候，突然遇到这个问题，在网上找资料，说的是连接池泄漏，无法从连接池中获取有效的数据库连接。之前以为代码出了问题，结果重启了tomcat后就没出现该异常，在此记录，以免以后遇到同样的问题。

14:44:13,718  WARN org.springframework.jdbc.support.SQLErrorCodesFactory:230 - Error while extracting database product name - falling back to empty error codes
org.springframework.jdbc.support.MetaDataAccessException: Could not get Connection for extracting meta data; nested exception is org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is org.apache.commons.dbcp.SQLNestedException: Cannot get a connection, pool error Timeout waiting for idle object
 at org.springframework.jdbc.support.JdbcUtils.extractDatabaseMetaData(JdbcUtils.java:293)
 at org.springframework.jdbc.support.JdbcUtils.extractDatabaseMetaData(JdbcUtils.java:320)
 at org.springframework.jdbc.support.SQLErrorCodesFactory.getErrorCodes(SQLErrorCodesFactory.java:216)
 at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.setDataSource(SQLErrorCodeSQLExceptionTranslator.java:140)
 at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.<init>(SQLErrorCodeSQLExceptionTranslator.java:103)
 at org.springframework.orm.hibernate3.SessionFactoryUtils.newJdbcExceptionTranslator(SessionFactoryUtils.java:145)
 at org.springframework.orm.hibernate3.HibernateAccessor.getDefaultJdbcExceptionTranslator(HibernateAccessor.java:453)
 at org.springframework.orm.hibernate3.HibernateAccessor.convertHibernateAccessException(HibernateAccessor.java:410)
 at org.springframework.orm.hibernate3.support.OpenSessionInViewFilter.doFilterInternal(OpenSessionInViewFilter.java:198)
 at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76)
 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.Http11AprProcessor.process(Http11AprProcessor.java:859)
 at org.apache.coyote.http11.Http11AprProtocol$Http11ConnectionHandler.process(Http11AprProtocol.java:579)
 at org.apache.tomcat.util.net.AprEndpoint$Worker.run(AprEndpoint.java:1555)
 at java.lang.Thread.run(Thread.java:619)
Caused by: org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is org.apache.commons.dbcp.SQLNestedException: Cannot get a connection, pool error Timeout waiting for idle object
 at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:82)
 at org.springframework.jdbc.support.JdbcUtils.extractDatabaseMetaData(JdbcUtils.java:280)
 ... 43 more
Caused by: org.apache.commons.dbcp.SQLNestedException: Cannot get a connection, pool error Timeout waiting for idle object
 at org.apache.commons.dbcp.PoolingDataSource.getConnection(PoolingDataSource.java:114)
 at org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:1044)
 at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:113)
 at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:79)
 ... 44 more
为了在开发阶段尽可能的暴露是否存在连接未完全释放的问题，将JNDI配置数据库连接的maxActive和maxIdle参数都设置的比较小。

有一种情况就是，如果某个页面存在大量的数据库查询操作（例如：此页面加载时将要建立5个connection），那么当我在此页面频繁的刷新页面时，将会抛出异常

 org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot get a connection, pool error Timeout waiting for idle object

经过调查发现，抛出此异常的原因为连接池泄漏，也就是再也无法从连接池中获取有效的数据库连接。

频繁的刷新页面导致上一次获取的connection还没有得到有效释放，下一次的connection请求已经到达，如是刷新多次后导致池资源耗尽，即所谓的连接池泄漏。

而且，在此种情况下，最糟糕的是异常发生后导致整个服务向连接池发起connection请求都将一直抛出如上异常，无法恢复到正常状态。服务宕机，必须重启tomcat才能恢复，这真是个可怕的事情。