Connection pool problem with Spring and programmatic transaction management

Question

Hi everyone !

I need your help in order to solve connection pool problem with Spring. I’m using Spring with Java 1.4 (no annotation).

Here is the datasource and the connection pool definition:

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
    <property name="driverClassName">
        <value>${database.jdbcName}</value>
    </property>
    <property name="url" value="jdbc:sybase:${database.url}">
    </property>
    <property name="username">
        <value>${database.username}</value>
    </property>
    <property name="password">
        <value>${database.password}</value>
    </property>
    <property name="maxActive">
         <value>${database.maxActive}</value>
    </property>
    <property name="maxIdle" >
         <value>${database.maxIdle}</value>
    </property>

<bean id="sessionFactory" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
    <property name="dataSource">
        <ref bean="dataSource" />
    </property>     
    <property name="hibernateProperties">
        <props>         
            <prop key="hibernate.dialect">org.hibernate.dialect.SybaseDialect</prop>
            <prop key="show_sql">true</prop>
            <prop key="hibernate.connection.release_mode">after_transaction</prop>  
            <prop key="hibernate.dbcp.maxWait">100000</prop>
            <prop key="hibernate.connection.autocommit">false</prop>
            <prop key="cache.provider_class">org.hibernate.cache.NoCacheProvider</prop>             
        </props>
    </property>     
    ...     

When all the connections are active, if the system need a new connection I got a message saying “opening JDBC connection” and all the process are stopped. I don’t understand why all the threads are locked.

All services and DAO classes are defined as singletons in Spring application context.

Any idea?

Thanks for your help.

Béranger

Answer 1

What values are you using for maxActive and maxIdle? I can give you a more definitive answer if I know those values, but in the meantime you can also try changing the value of hibernate.dbcp.maxWait from 100000 to 1 and test your application again.

Also make sure you haven't you accidentally made some of your DAO methods synchronized.

Answer 2

This was a weird one but I finally solved my problem.

The reason of this connection locking was that I used both declarative and programmatic transaction management with Spring. I think that my programmatic transaction management was wrong.

Here is the code used to begin a transaction:

//Return Spring context
ApplicationContext context = ApplicationContextHolder.getContext();
// transactionManager 
PlatformTransactionManager transactionManager = (PlatformTransactionManager) context.getBean("txManager");
//Set propagation required
DefaultTransactionDefinition td = new DefaultTransactionDefinition();
td.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRED);
// Begin
TransactionStatus transactionStatus = transactionManager.getTransaction(td);

As you can see I set the programmatic transactions as “required”. But when those transactions were started in a transactional context, Spring didn’t recognized the current transaction and started a new one. It leaded to a connection locking...

I solved this mystery by removing the programmatic transaction management.