CODEWITHSUNDEEP
javaspringhibernatec3p0
Vishal
Vishal

Reputation: 85

c3p0 not releasing connection when max_size reached

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?

Upvotes: 4

Views: 3685

Answers (2)

Vishal
Vishal

Reputation: 85

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

Upvotes: 1

Steve Waldman
Steve Waldman

Reputation: 14073

You are leaking Connections.

You are failing to close() your Connections. (Please close() them robustly, in a finally block organized so that a call to close() is guaranteed -- ie cannot be skipped by a prior Exception in finally -- or better yet if Java 7+ using try-with-resources.

Note: In Hibernate, it is Session, which wrap Connections, that you must ensure get close()ed.

Upvotes: 1

Related Questions