My Spring application leaks database connections whereas I use the default Roo configuration

Question

I am encountering an serious issue with my application. It leaks database connections whereas I use the default Spring Roo datasource configuration as follows:

<bean class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close" id="dataSource">
    <property name="driverClassName" value="${database.driverClassName}" />
    <property name="url" value="${database.url}" />
    <property name="username" value="${database.username}" />
    <property name="password" value="${database.password}" />
    <property name="testOnBorrow" value="true" />
    <property name="testOnReturn" value="true" />
    <property name="testWhileIdle" value="true" />
    <property name="timeBetweenEvictionRunsMillis" value="1800000" />
    <property name="numTestsPerEvictionRun" value="3" />
    <property name="minEvictableIdleTimeMillis" value="1800000" />
    <property name="validationQuery" value="SELECT 1" />
    <property name="maxActive" value="2"/>
    <property name="logAbandoned" value="true"/>
    <property name="removeAbandoned" value="true"/>
</bean>

Here is the controller method that causes the leak:

@RequestMapping(value = "getMessages", method = RequestMethod.GET, produces = "application/json")
    @ResponseBody
    public DeferredResult<List<Message>> getMessages(@RequestParam final Long senderId) {
        // TODO: check that recipientId was not changed by malicious user!!
        final Long recipientId = memberService.retrieveCurrentMember().getId();
        final String messageRequestKey = new StringBuilder().append(senderId).append(":").append(recipientId).toString();
        final DeferredResult<List<Message>> deferredResult = new DeferredResult<List<Message>>(null, Collections.emptyList());
        messageRequests.put(messageRequestKey, deferredResult);

        deferredResult.onCompletion(new Runnable() {
            @Override
            public void run() {
                messageRequests.remove(messageRequestKey);
            }
        });

        List<Message> unReadMessages = messageService.findUnreadMessages(senderId, recipientId);
        if (!unReadMessages.isEmpty()) {
            deferredResult.setResult(unReadMessages);
        }
        return deferredResult;
    }

It appears that connections are not returned to the pool by the above method (which is polled continuously by ajax).

Can anyone please help?

EDIT:

Hibernate config:

<persistence-unit name="persistenceUnit" transaction-type="RESOURCE_LOCAL">
        <provider>org.hibernate.ejb.HibernatePersistence</provider>
        <properties>
            <property name="hibernate.dialect" value="org.hibernate.dialect.MySQL5InnoDBDialect"/>
            <!-- value="create" to build a new database on each run; value="update" to modify an existing database; value="create-drop" means the same as "create" but also drops tables when Hibernate closes; value="validate" makes no changes to the database -->
            <property name="hibernate.hbm2ddl.auto" value="update"/>
            <property name="hibernate.ejb.naming_strategy" value="org.hibernate.cfg.ImprovedNamingStrategy"/>
            <property name="hibernate.connection.charSet" value="UTF-8"/>
            <property name="hibernate.show_sql" value="true"/>
            <property name="hibernate.format_sql" value="true"/>
            <property name="hibernate.use_sql_comments" value="true"/>
        <property name="hibernate.connection.release_mode" value="after_transaction"/>
        </properties>
    </persistence-unit>

Hibernate version:

<dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-core</artifactId>
            <version>4.1.8.Final</version>
        </dependency>
        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-entitymanager</artifactId>
            <version>4.1.8.Final</version>
            <exclusions>
                <exclusion>
                    <groupId>cglib</groupId>
                    <artifactId>cglib</artifactId>
                </exclusion>
                <exclusion>
                    <groupId>dom4j</groupId>
                    <artifactId>dom4j</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        <dependency>
            <groupId>org.hibernate.javax.persistence</groupId>
            <artifactId>hibernate-jpa-2.0-api</artifactId>
            <version>1.0.1.Final</version>
        </dependency>

I use an entity manager in the memberService and messageService but using Spring, I don't use a connection "manually". — balteo, Mar 22 '13 at 16:28
I just don't understand why the above config won't return the connections to the pool or at least close them... — balteo, Mar 22 '13 at 16:29

Martin Frey · Answer 1 · 2013-03-24T08:00:05.580

2

I'm not sure if you are using hibernate? If so take a look at the setting hibernate.connection.release_mode . We had this issue and putting it to after_transaction solved all our connection issues.

EDIT

Our config:

<beans:bean id="entityManagerFactory"
    class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
        <beans:property name="dataSource" ref="mvcDatasource" />
        <beans:property name="persistenceUnitName" value="mvcPersistenceUnit" />

        <beans:property name="persistenceProvider">
            <beans:bean class="org.hibernate.ejb.HibernatePersistence" />
        </beans:property>

        <!-- Fix Hibernate not properly connected with spring -->
        <beans:property name="jpaVendorAdapter">
            <beans:bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter" />
        </beans:property>
        <beans:property name="jpaPropertyMap">
            <beans:map>
                <!-- Connection release fix -->
                <beans:entry key="hibernate.connection.release_mode" value="after_transaction" />

                <beans:entry key="hibernate.dialect" value="${hibernate.dialect}" />
    ...
        </beans:map>
    </beans:property>
</beans:bean>

edited Mar 24 '13 at 08:00

answered Mar 22 '13 at 18:38

Martin Frey

10,025
4
25
30

Thanks Martin. I do use Hibernate. I tried as you advised to no avail... Any other idea? – balteo Mar 22 '13 at 20:15
I'm currently using v4.2 of hibernate in jpa mode. Which version are you using and can you post your config? – Martin Frey Mar 23 '13 at 06:39
Hmm, your config looks ok from the hibernate pov. Are you sure these settings are applied at runtime? Its already some month since i fixed our issue and if i remember correctly there was something in the spring config that had to be done too to really connect hibernate with spring. I think it was the vendoradapter config. Without it hibernate was not taking part in the transaction manager i believe. I update my entry with our config. Take a look if it helps. – Martin Frey Mar 24 '13 at 07:44
Thanks for the edit Martin. I have altered my config as suggested. It does not make any difference unfortunately. I must admit I really think there is something wrong with my code which is born out by the hibernate documentation: __Do not change this default behavior as failures due to the value of this setting tend to indicate bugs and/or invalid assumptions in user code.__ After removing the call to database access (messageService.findUnreadmessages) in the controller method, the problem was gone leaving the app with incorrect biz logic. – balteo Mar 24 '13 at 11:00
I just rechecked your code and probably the issue is somehow related with the DefferedResult? Havent used it myself yet but you could try to implement your function returning a plain list to see if this solves you issue? – Martin Frey Mar 24 '13 at 13:22
Yes this issue is somehow related to my use of DeferredResult. I goes away when I set the result in a separate __postMessage__ method. Still I don't understand what really happens and what really causes the leak which is rather frustrating... – balteo Mar 24 '13 at 17:59
2

The transaction manager is thread bond as far as i know. Probably its something keeping your thread open? What if you detach all your entities or are they dtos anyway? Do you use opensessioninview? – Martin Frey Mar 24 '13 at 20:51
1

**the transaction manager is thread bound. It's something keeping your thread open**: good point here! I wish I knew what this something is!! I do use the OpenEntityManagerInview but the problematic method only returns JSon to the view so is it really relevant? – balteo Mar 25 '13 at 13:34
I think there are several callbacks in defferedresult that you might be able to use to log its lifecycle. Another way could be some aop? I cannot really help you here since i never used it myself. Soz :) do you really need defferedresult in your current implementation? It looks to me that the heavy work will be the select and not the write as json. – Martin Frey Mar 26 '13 at 06:19
Martin. I did try to log information using the callbacks (onCompletion , onTimeout) but I know the issue is a threading one. I posted rephrased question on the springsource forums and will let you know when I have resolved the threading issue posting here the full explanation. – balteo Mar 27 '13 at 11:08

My Spring application leaks database connections whereas I use the default Roo configuration

1 Answers1

Linked