Hibernate, Spring and c3p0 connection pooling cause JBoss to opens too many connections to database

Question

I have a strange problem with my application, running on JBoss 6.1, which uses Hibernate and Spring. I'm also using c3p0 to have connection pooling.

I have a datasource defined under JBoss. The datasource is a C3P0PooledDataSource. Please note that maxpoolsize is 20 and initial pool size is also 20.

<mbean code="com.mchange.v2.c3p0.jboss.C3P0PooledDataSource"
      name="jboss.jca:service=DataSourceBinding,name=db/MED_POOLED">

  <attribute name="JndiName">java:db/MED_POOLED</attribute>
  <attribute name="JdbcUrl">jdbc:oracle:thin:@dbhost:1521:SID</attribute>
  <attribute name="DriverClass">oracle.jdbc.driver.OracleDriver</attribute>
  <attribute name="User">xxx</attribute>
  <attribute name="Password">xxx</attribute>
  <attribute name="AutomaticTestTable">POOL_TEST</attribute>          
  <attribute name="CheckoutTimeout">30000</attribute>      
  <attribute name="Description">Pooled Datasource for med</attribute>
  <attribute name="IdleConnectionTestPeriod">180</attribute>     
  <attribute name="InitialPoolSize">20</attribute>  
  <attribute name="MaxPoolSize">20</attribute>   
  <attribute name="MinPoolSize">20</attribute>    
  <attribute name="TestConnectionOnCheckin">true</attribute>       
  <attribute name="MaxIdleTime">3600</attribute>
  <depends>jboss:service=Naming</depends>
</mbean>

In my spring configuration file, datasource is retrieved using the jndi name:

<bean id="defaultDataSource" class="org.springframework.jndi.JndiObjectFactoryBean">
    <property name="jndiName"><value>db/MED_POOLED</value></property>
</bean>

and then used to build a LocalSessionFactoryBean

<bean id="sessionFactory" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
    <property name="dataSource" ref="defaultDataSource"/>
    <property name="namingStrategy" ref="namingStrategy"/>
    <property name="configLocations">
        <list>
        <value>classpath:hibernate.cfg.xml</value>
        <value>classpath:jbpm.hibernate.cfg.xml</value>
        </list>
    </property>
    <property name="mappingDirectoryLocations">
        <list><value>WEB-INF/classes</value></list>
    </property>
</bean>

In hibernate.cfg.xml no datasource is defined, so the dataSource property of org.springframework.orm.hibernate3.LocalSessionFactoryBean should be used.

When I start my application server, something strange happens (al least, strange to me):

• JBoss starts, I see in the log this line

  [com.mchange.v2.c3p0.jboss.C3P0PooledDataSource] Bound C3P0 PooledDataSource to name'java:db/MED_POOLED'. Starting...

and if I check on database, 20 sessions are present.

• When the LocalSessionFactoryBean is created, suddendly 40 connections are present on Db, but in my datasource configuration I specified to create at maximum 20 connections.

I think that, in some way, the datasource is reinitialized when the LocalSessionFactoryBean is created, but I don't understand why, and when JBoss is started I have exactly double the connections I want.

Do you know what's happening? Is there something wrong with my configuration?

I'm using JBoss 6.1, the latest available version of c3p0 (0.9.5), Spring 3.0.3.RELEASE and Hibernate 3.5.3-Final

Answer 1

There are two likely ways that the number of Connections might be twice your expected pool size.

1. You might somehow be initializing two c3p0 DataSources;
2. You might be acquiring Connections for under two distinct authentications, by calling dataSource.getConnection( user, password )

The easiest way to check these two possibilities is via JMX: if the C3P0Registry MBean shows two distinct PooledDataSources, there's your problem. Otherwise, look for the property numUserPools inside your DataSource: is it two?

I'd examine those possibilities first.

[If you don't have access to JMX, you can see whether multiple DataSources get initialized by looking for log entries at INFO level from the logger named com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource, entries that begin with "Initializing c3p0 pool..." and then display the configuration of the pool being initialized.]

Another possibility is that for some reason your application is hot-redeploying, but as the old instance of the application is torn down, your c3p0 DataSource has not been closed. That will be harder to debug and lead to quirky behavior in general if c3p0's classes are loaded by multiple distinct ClassLoaders. (I don't know enough about whether/how JBoss implements hot-redeploy to know whether this is likely to be a problem.) One way to avoid this kind of issue would be to be sure that c3p0's jar files are made available at the application server level, not subsidiary to any particular application, and then set the c3p0 [0.9.5] config parameter contextClassLoaderSource to library, see http://www.mchange.com/projects/c3p0/#contextClassLoaderSource