Hibernate 异常：Spring 升级后出现连接池耗尽错误

Question

我在我的应用程序中使用 Spring + Hibernate + Dbcp + Tomcat。由于某种原因，在最近春季升级到版本 3.0.6 后，应用程序开始抛出以下异常。

    org.springframework.transaction.CannotCreateTransactionException: Could not open Hibernate Session for transaction; nested exception is org.hibernate.exception.GenericJDBCException: Cannot open connection
at org.springframework.orm.hibernate3.HibernateTransactionManager.doBegin(HibernateTransactionManager.java:596)
at org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:371)
at org.springframework.transaction.interceptor.TransactionAspectSupport.createTransactionIfNecessary(TransactionAspectSupport.java:335)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:105)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
at $Proxy7.findURLByPattern(Unknown Source)
at com.xxx.security.DAOBasedFilterInvocationDefinitionMap.lookupAttributes(DAOBasedFilterInvocationDefinitionMap.java:80)
at org.acegisecurity.intercept.web.AbstractFilterInvocationDefinitionSource.getAttributes(AbstractFilterInvocationDefinitionSource.java:39)
at org.acegisecurity.intercept.AbstractSecurityInterceptor.beforeInvocation(AbstractSecurityInterceptor.java:236)
at org.acegisecurity.intercept.web.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:104)
at org.acegisecurity.intercept.web.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:72)
at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:275)
at org.acegisecurity.ui.ntlm.HttpFilter.doFilter(HttpFilter.java:51)
at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:275)
at org.acegisecurity.ui.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:110)
at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:275)
at org.acegisecurity.ui.logout.LogoutFilter.doFilter(LogoutFilter.java:106)
at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:275)
at org.acegisecurity.context.HttpSessionContextIntegrationFilter.doFilter(HttpSessionContextIntegrationFilter.java:286)
at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:275)
at org.acegisecurity.util.FilterChainProxy.doFilter(FilterChainProxy.java:149)
at org.acegisecurity.util.FilterToBeanProxy.doFilter(FilterToBeanProxy.java:98)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:215)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:174)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:117)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:108)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:151)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:874)
at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:665)
at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:528)
at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:81)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:689)
at java.lang.Thread.run(Thread.java:619)
    Caused by: org.hibernate.exception.GenericJDBCException: Cannot open connection
at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:103)
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:91)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:29)
at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:420)
at org.hibernate.jdbc.ConnectionManager.getConnection(ConnectionManager.java:144)
at org.hibernate.jdbc.JDBCContext.connection(JDBCContext.java:119)
at org.hibernate.transaction.JDBCTransaction.begin(JDBCTransaction.java:57)
at org.springframework.orm.hibernate3.HibernateTransactionManager.doBegin(HibernateTransactionManager.java:551)
... 36 more
    Caused by: org.apache.commons.dbcp.SQLNestedException: Cannot get a connection, pool exhausted, cause: Timeout waiting for idle object
at org.apache.commons.dbcp.PoolingDataSource.getConnection(PoolingDataSource.java:148)
at org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:518)
at org.springframework.orm.hibernate3.LocalDataSourceConnectionProvider.getConnection(LocalDataSourceConnectionProvider.java:81)
at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:417)
... 40 more
    Caused by: java.util.NoSuchElementException: Timeout waiting for idle object
at org.apache.commons.pool.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:801)
at org.apache.commons.dbcp.AbandonedObjectPool.borrowObject(AbandonedObjectPool.java:119)
at org.apache.commons.dbcp.PoolingDataSource.getConnection(PoolingDataSource.java:140)
... 43 more

我的配置如下

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
    <property name="driverClassName" value="${db.driverClassName}" />
    <property name="url" value="${db.url}" />
    <property name="username" value="${db.username}" />
    <property name="password" value="${db.password}" />
    <property name="maxActive" value="10" />
    <property name="maxIdle" value="6" />
    <property name="maxWait" value="120" />
    <property name="defaultAutoCommit" value="true" />
    <property name="removeAbandoned" value="true" />
    <property name="removeAbandonedTimeout" value="60" />
</bean>

<bean id="sessionFactory" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
    <property name="dataSource">
        <ref bean="dataSource" />
    </property>

    <property name="configLocations">
        <list>
            <value>classpath:com/xxx/model/hibernate/hibernate-gas.cfg.xml</value>
        </list>
    </property>

    <property name="hibernateProperties">
        <props>
            <prop key="hibernate.dialect"> org.hibernate.dialect.SQLServerDialect </prop>
            <prop key="hibernate.jdbc.fetch_size">10</prop>
            <prop key="hibernate.jdbc.batch_size">50</prop>
            <prop key="hibernate.generate_statistics">true</prop>
        </props>
    </property>
</bean>

<bean id="hibernateTemplate" class="org.springframework.orm.hibernate3.HibernateTemplate">
    <property name="sessionFactory">
        <ref bean="sessionFactory" />
    </property>
</bean>


<bean id="myTransactionManager" class="org.springframework.orm.hibernate3.HibernateTransactionManager">
    <property name="sessionFactory">
        <ref local="sessionFactory" />
    </property>
    <property name="nestedTransactionAllowed" value="true" />
</bean>

<bean id="propagationRequired" abstract="true" class="org.springframework.transaction.interceptor.TransactionProxyFactoryBean">
    <property name="transactionManager">
        <ref bean="myTransactionManager" />
    </property>
    <property name="transactionAttributes">
        <props>
            <prop key="select*">
                PROPAGATION_REQUIRED,readOnly, timeout_300
            </prop>
            <prop key="search*">
                PROPAGATION_REQUIRED,readOnly, timeout_300
            </prop>
            <prop key="*">
                PROPAGATION_REQUIRED, timeout_300,
                -Exception
            </prop>
        </props>
    </property>
</bean>

<bean id="propagationRequiresNew" abstract="true" class="org.springframework.transaction.interceptor.TransactionProxyFactoryBean">
    <property name="transactionManager">
        <ref bean="transactionManager" />
    </property>
    <property name="transactionAttributes">
        <props>
            <prop key="*">
                PROPAGATION_REQUIRES_NEW, timeout_300,
                -Exception
            </prop>
        </props>
    </property>
</bean>

Answer 1

通常我会说尝试一下 c3p0 （不同的连接池实现）。

但是，我认为问题在于应用程序如何处理数据库连接。 我有兴趣了解您为 dbcp 连接池使用的值（数据源配置参数）。请尝试发布它们。

我将使用以下内容来确保废弃的连接被逐出并记录：

removeAbandoned = true
logAbandoned = true

并且我还会尝试减小以下值：

removeAbandonedTimeout

以强制更早地踢出连接，以便您可以识别应用程序中负责保持这些连接最长的流。解决问题后，您可以重新调整它的值。

Answer 2

我有类似的问题。
就我而言，我将 spring security 与 openSessionInViewFilter 结合使用，但顺序错误。
我的 Spring 安全代码打开一个连接并且从不释放它，这是在 OpenSessionInViewFilter 之前完成的。

Answer 3

您问“有没有办法让我找出任何给定时间的活动和空闲连接的数量”

您可以扩展 BasicDataSource 并获取更多信息：

public class WrapperDataSource extends BasicDataSource implements Serializable {

    private static final long serialVersionUID = 4139847655780946796L;
    private static final Logger log = LoggerFactory.getLogger(WrapperDataSource.class);

    @Override
    public Connection getConnection() throws SQLException {
        if(log.isTraceEnabled()){ 
            log.trace("Number of active connections:" + super.getNumActive());
            log.trace("Number of idle connections:" + super.getNumIdle());
            log.trace("Number of max active:" + super.getMaxActive());
        }
        return super.getConnection();
    }

    @Override
    public Connection getConnection(String username, String password) throws SQLException {
        if(log.isTraceEnabled()){ 
            log.trace("Number of active connections:" + super.getNumActive());
            log.trace("Number of idle connections:" + super.getNumIdle());
            log.trace("Number of max active:" + super.getMaxActive());
        }
        return super.getConnection(username, password);
    }
}