c3p0 creating more connections than specified in configuration

Question

My application is using Hibernate 4.1.7 and c3p0 0.9.1.

I've set the c3p0.max_size property in my hibernate.cfg.xml file for my application to 50, but the number of JDBC connections created has been exceeding that value. Also, inactive/idle connections are not being removed as I've also specified in my Hibernate configuration. Here's a snip from my configuration:

<property name="c3p0.acquire_increment">1</property>
<property name="c3p0.autoCommitOnClose">false</property>
<property name="c3p0.max_size">50</property>
<property name="c3p0.min_size">1</property>
<property name="c3p0.numHelperThreads">1</property>
<property name="c3p0.maxIdleTime">30</property> 
<property name="c3p0.maxIdleTimeExcessConnections">20</property>
<property name="c3p0.maxConnectionAge">45</property>

I am explicitly closing my sessions and session factories in a finally block within my code. Here's the class that I'm using to create my SessionFactory instance:

package ics.sis.util;

import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;
import org.hibernate.service.ServiceRegistry;
import org.hibernate.service.ServiceRegistryBuilder;

import ics.global.runtime.Environment;
import ics.util.properties.PropertiesISUWrapper;

public class HibernateSessionFactory {
    private static SessionFactory sessionFactory;
    private static ServiceRegistry serviceRegistry;
    private static final PropertiesISUWrapper ISU_PROPERTIES = new PropertiesISUWrapper(Environment.getName(),"VzAppIntegration"); 

    public static SessionFactory create() {
        Configuration configuration = new Configuration();
        configuration.configure();

        configuration.setProperty("hibernate.connection.url", ISU_PROPERTIES.getUrl());
        configuration.setProperty("hibernate.connection.password", ISU_PROPERTIES.getPassword());

        serviceRegistry = new ServiceRegistryBuilder().applySettings(configuration.getProperties()).buildServiceRegistry();        
        sessionFactory = configuration.buildSessionFactory(serviceRegistry);
        return sessionFactory;
    }

}

Here's one of the main methods performing a database transaction:

public static int insert(int aidm, String termCode, String wappCode) throws SQLException, ClassNotFoundException {      
        // Initialize session and transaction
        SessionFactory sessionFactory = HibernateSessionFactory.create();
        Session session = sessionFactory.openSession();
        Transaction tx = null;
        int applSeqno = 0;
        Stvwapp wapp = null;

        try {
            tx = session.beginTransaction();

            applSeqno = generateApplSeqNo(session, aidm);
            SarheadId sarheadIdDao = new SarheadId();
            sarheadIdDao.setSarheadAidm(aidm);
            sarheadIdDao.setSarheadApplSeqno((short)applSeqno);

            // Find STVWAPP row by WAPP code
            Query query = session.getNamedQuery("findStvwappByWappCode"); 
            query.setString("wappCode", wappCode);

            if (query.list().size() == 0) {
                throw new RuntimeException("Invalid WAPP code specified: " + wappCode);
            } else {
                wapp = (Stvwapp) query.list().get(0);
            }

            Sarhead sarheadDao = new Sarhead();
            sarheadDao.setId(sarheadIdDao);
            sarheadDao.setSarheadActivityDate(new java.sql.Timestamp(System.currentTimeMillis()));
            sarheadDao.setSarheadAddDate(new java.sql.Timestamp(System.currentTimeMillis()));
            sarheadDao.setSarheadAplsCode("WEB");
            sarheadDao.setSarheadApplAcceptInd("N");
            sarheadDao.setSarheadApplCompInd("N");
            sarheadDao.setSarheadApplStatusInd("N");
            sarheadDao.setSarheadPersStatusInd("N");
            sarheadDao.setSarheadProcessInd("N");
            sarheadDao.setSarheadTermCodeEntry(termCode);
            sarheadDao.setStvwapp(wapp);

            session.save(sarheadDao);
        } finally {
            tx.commit();
            session.close();
            sessionFactory.close(); 
        }

        return applSeqno;
    }

Update

I changed the log level of c3p0 to DEBUG to get more verbose logging on connection pooling and I'm seeing it check every 3 or 4 seconds for expired connections. Also, I'm seeing the following line being logged, which to me looks like there are two connections total in the pool. However, in Toad, I'm monitoring the total JDBC connections open and it shows 6. So I'm trying to figure out why there's a discrepancy between these numbers.

[Env:DEVL] [] - 2012-12-06 12:14:07 DEBUG BasicResourcePool:1644 - trace com.mchange.v2.resourcepool.BasicResourcePool@7f1d11b9 [managed: 1, unused: 1, excluded: 0] (e.g. com.mchange.v2.c3p0.impl.NewPooledConnection@7b3f1264)

Answer 1

You are saying that:

I've set the c3p0.max_size property in my hibernate.cfg.xml file for my application to 50, but the number of JDBC connections created has been exceeding that value.

This happens because in your insert method you are invoking create() method. Each time in your create() method, you build a brand new sessionFactory like this:

 configuration.buildSessionFactory(serviceRegistry);

and in that method you also close the sessionFactory. Both creating and closing sessionFactory instance are extramely costly operations (you can see the closing method here).

Above all, when your application serves multiple requests at a time in several threads, each thread creates its own sessionFactory(each creating its own pool). So at a time there are several sessionFactory and Connection pool exists in your system. While you should create it only once for the lifetime of your application. For that reason when there are multiple copies of pool, then the total number of connection of all the pools can exceed your max limit, you configured for one pool.

I would suggest you to rewrite your HibernateSessionFactory class like bellow:

public class HibernateSessionFactory {
    private static SessionFactory sessionFactory = HibernateSessionFactory.create();
    private static ServiceRegistry serviceRegistry;
    private static final PropertiesISUWrapper ISU_PROPERTIES = new PropertiesISUWrapper(Environment.getName(),"VzAppIntegration"); 

    private static SessionFactory create() {
        Configuration configuration = new Configuration();
        configuration.configure();

        configuration.setProperty("hibernate.connection.url", ISU_PROPERTIES.getUrl());
        configuration.setProperty("hibernate.connection.password", ISU_PROPERTIES.getPassword());

        serviceRegistry = new ServiceRegistryBuilder().applySettings(configuration.getProperties()).buildServiceRegistry();        
        return configuration.buildSessionFactory(serviceRegistry);

    }

    public static SessionFactory getSessionFactory(){
       return sessionFactory;
    }

}

and in insert() method instead of calling HibernateSessionFactory.create(), simply call HibernateSessionFactory.getSessionFactory().

Answer 2

The problem was that I was creating a new session factory multiple times within my application, whereas I only needed to call it once upon the application start up. I ended up creating a class that implemented the ServletContextListener interface that created a new session factory and destroyed/closed it when the context was destroyed.