Spring Hibernate C3P0: Connections not released

Question

C3P0 is not releasing connections after a transaction completes. Here's the stack trace:

java.lang.Exception: DEBUG STACK TRACE: Overdue resource check-out stack trace. at com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource(BasicResourcePool.java:555) at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutAndMarkConnectionInUse(C3P0PooledConnectionPool.java:755) at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:682) at com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource.getConnection(AbstractPoolBackedDataSource.java:140) at org.hibernate.engine.jdbc.connections.internal.DatasourceConnectionProviderImpl.getConnection(DatasourceConnectionProviderImpl.java:122) at org.hibernate.internal.AbstractSessionImpl$NonContextualJdbcConnectionAccess.obtainConnection(AbstractSessionImpl.java:386) at org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.acquireConnectionIfNeeded(LogicalConnectionManagedImpl.java:87) at org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.getPhysicalConnection(LogicalConnectionManagedImpl.java:112) at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.connection(StatementPreparerImpl.java:47) at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$5.doPrepare(StatementPreparerImpl.java:146) at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:172) at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareQueryStatement(StatementPreparerImpl.java:148)

............... at com.ikoda.dao.user.UserDaoImpl.getUserChildByEmailPassword(UserDaoImpl.java:329)

..................................... at com.ikoda.service.springtransactions.AdminUserServiceImpl.doUserChildLoginByEmail(AdminUserServiceImpl.java:100) at com.ikoda.service.springtransactions.AdminUserServiceImpl.doLoginByEmail(AdminUserServiceImpl.java:89)

..........................

The service methods called are as follows:

    @Transactional(readOnly = true, propagation = Propagation.REQUIRED)
public User doLoginByEmail(String email, String password) throws UserStructureException
{
    User u = doUserMainLoginByEmail(email, password);
    if (null != u)
    {
        return u;
    }
    else
    {
        return doUserChildLoginByEmail(email, password);
    }
}




private UserMain doUserMainLoginByEmail(String email, String password) throws UserStructureException
{
    try
    {

        UserMain returnedUser = userDao.getUserMainByEmailPassword(email, password, DeliveryPropertiesSingleton.getInstance().getBusinessId());

        if (null != returnedUser && returnedUser.getPassword().equals(password))
        {

            return returnedUser;
        }
        return null;
    }
    catch (IndexOutOfBoundsException ioe)
    {
      ...
    }
    catch (Exception e)
    {
        ...
    }

}


private  UserChild doUserChildLoginByEmail(String email, String password) throws UserStructureException
{
    try
    {


        UserChild returnedUser = userDao.getUserChildByEmailPassword(email, password, DeliveryPropertiesSingleton.getInstance().getBusinessId());

        if (null != returnedUser && returnedUser.getPassword().equals(password))
        {

            return returnedUser;
        }
        return null;
    }
    catch (IndexOutOfBoundsException ioe)
    {
        ...
    }
    catch (Exception e)
    {
       ....
    }

}

The dao methods are as follows:

@Transactional(propagation = Propagation.MANDATORY)
public UserMain getUserMainByEmailPassword(String email, String password, Long businessId)
{

    Session session = this.sessionFactory1.getCurrentSession();


    List<UserMain> result = session.createQuery("from UserMain where email like '" + email + "' and password like '" + password + "' and  businessid =" + businessId).list();

    if(result.size()==1)
    {
            UserMain u = result.get(0);

            return u;
    }
    return null;
}


@Transactional(propagation = Propagation.MANDATORY)
public UserChild getUserChildByEmailPassword(String email, String password, Long businessId)
{

    Session session = this.sessionFactory1.openSession();

    List<UserChild> result = session.createQuery("from UserChild where email like '" + email
            + "' and password like '" + password + "' and  businessid =" + businessId).list();

    if(result.size()==1)
    {
        UserChild u = result.get(0);

        return u;
    }
    return null;

}

The pool configuration and transaction configuration are as follows:

	<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"
		destroy-method="close">
		<property name="driverClass" value="org.postgresql.Driver" />
		<property name="jdbcUrl" value="jdbc:postgresql://localhost/xxx?useUnicode=true&#38;characterEncoding=utf8" />
		<property name="user" value="xxx" />
		<property name="password" value="xxxxxxx" />

		<property name="acquireIncrement" value="2" />
		<property name="minPoolSize" value="3" />
		<property name="maxPoolSize" value="100" />
		<property name="maxIdleTime" value="600" />
		<property name="unreturnedConnectionTimeout" value="45" />
		<property name="debugUnreturnedConnectionStackTraces" value="true" />
	</bean>

	<bean id="transactionManager"
		class="org.springframework.orm.hibernate5.HibernateTransactionManager">
		<property name="sessionFactory" ref="sessionFactory1" />

	</bean>

	<tx:annotation-driven transaction-manager="transactionManager" />

Any advice would be deeply appreciated

c3p0 is telling you your application is not releasing Connections. it is helping you find an application problem, which it looks like you have found. (yay!) — Steve Waldman

Jake Jake · Accepted Answer · 2017-01-21T20:56:15

Ok, dead simple.

The DAO methods used openSession which requires an explicit close. For spring/hibernate transaction managed persistence, it should be getCurrentSession

Spring Hibernate C3P0: Connections not released

1 Answers