Connection pool problem with Spring and programmatic transaction management

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>
            <pr开发者_如何学Cop 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

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.

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.