Hibernate Exception: Connection Pool exhausted error after Spring upgrade

I am using Spring + Hibernate + Dbcp + Tomcat in my application. For some reason after a recent spring upgrade to version 3.0.6 the application has started to throw the following exception.

    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

My configuration is as follows

<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>

Normally I'd say give c3p0 a try (different connection pool implementation).

However, I think the problem lays in how you application handles db connections. I'd be interested to find out what are the values you're using for the dbcp connection pool (the data source configuration parameters). Try posting them please.

I'd use the following to make sure abandoned connections get evicted and logged:

removeAbandoned = true
logAbandoned = true

and I'd also try decreasing the value for:

removeAbandonedTimeout

to force connections to be kicked out earlier, so that you can identify the flows in your application that are responsible holding those connections the longest. After fixing the problem, you can re-adjust the value for it.

I had a similar issue. In my case I was using spring security combined with openSessionInViewFilter but in a wrong order. My spring security code was opening a connection and never releasing it, this was done before the OpenSessionInViewFilter.

You're asking "Is there a way for me to find out the number of active and idle connections at any given time"

You can extend BasicDataSource and get some more info:

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