c3p0 not releasing connection when max_size reached

Question

I am trying to execute a get on the database 10 times in one method. I am using Hibernate 3.5.x and Spring 3.2.13 with c3p0 0.9.5.

My hibernate.cfg.xml contains the following:

<property name="hibernate.c3p0.acquire_increment">1</property>
<property name="hibernate.c3p0.idle_test_period">300</property>
<property name="hibernate.c3p0.max_size">10</property>
<property name="hibernate.c3p0.max_statements">0</property>
<property name="hibernate.c3p0.min_size">10</property>
<property name="hibernate.c3p0.timeout">600</property>

This all works fine with the debugging from c3p0 saying:

[managed: 10, unused: 0, excluded: 0] 
[managed: 10, unused: 1, excluded: 0] 
[managed: 10, unused: 2, excluded: 0] 
[managed: 10, unused: 3, excluded: 0] 
[managed: 10, unused: 4, excluded: 0] 
[managed: 10, unused: 5, excluded: 0] 
[managed: 10, unused: 6, excluded: 0] 
[managed: 10, unused: 7, excluded: 0] 
[managed: 10, unused: 8, excluded: 0] 
[managed: 10, unused: 9, excluded: 0]

This all happens very quickly (less then 0.5 secs)

But if I add an extra get i.e. 11 then I get the following:

[managed: 10, unused: 0, excluded: 0] repeatedly

It does not change until the connection times out.

I am using Springs Transaction Manager like this:

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

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

and use the following which basically calls findOne()

public abstract class AbstractDAO<T extends Serializable> {
    private Class< T > clazz;

    @Autowired
    private SessionFactory sessionFactory;

    public void setSessionFactory(SessionFactory sessionFactory) {
        this.sessionFactory = sessionFactory;
    }

    public void setClazz(final Class<T> clazzToSet) {
        clazz = clazzToSet;
    }

    @Transactional
    public T findOne(final long id) {
        return (T) getCurrentSession().get(clazz, id);
    }

...
}

Please let me know what other information may help diagnose.

What could I have done incorrectly?

Answer 1

So it seems like you cannot use more then hibernate.c3p0.max_size connections within the same thread i.e. if hibernate.c3p0.max_size is set to 10, then you cannot have 11 gets within the same thread. I guess c3p0 only releases the connection once the thread is completed.

The way I tested this was to have 10 gets in the method and then sleep for 15 secs. I executed the method twice, the first method ran using the 10 connections. After 15 secs, the method was completed and the second execution ran successfully using up another 10 connections which then got released after 15 secs.

I guess I can understand why this is. I guess I will have to refactor/redesign my use of connections...

Update: I found this hibernate.connection.release_mode property which I set to after_statement. This seems to release the connection after execution. See here for more info