-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 6 posts ] 
Author Message
 Post subject: JPA/Hibernate/Mysql - em.close() not releasing connections
PostPosted: Thu Feb 24, 2011 6:14 am 
Newbie

Joined: Sat Sep 26, 2009 2:13 pm
Posts: 6
Hi,

EntityManager.close() does not release the connection! Pls help...

I tried c3p0 and tomcat DS, and hibernate does not close the connection. With c3p0 it reused some of the but timeout/test connection, were never used.

I get the EntityManagerFactory by @PersistenceUnit(unitName = "main")
and the I use the following logic
Code:
EntityManager em = entityManagerFactory.createEntityManager();
em.getTransaction().begin();
.....
EntityTransaction tx = em.getTransaction();
tx.commit();
}finally{
em.close();
}


This is driving me crazy. I can see the connection being open in mysql:
mysql> SHOW PROCESSLIST ;

Am I doing something wrong?
-------------
DB pool:Tomcat 6 jndi (also tried c3p0)
Mysql connector: 5.1.6
Hibernate: 3.6.1.Final
JPA: 2.0-cr-1

conf:
Code:
   <persistence xmlns="http://java.sun.com/xml/ns/persistence"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd"
        version="2.0">


        <persistence-unit name="main">
                <provider>org.hibernate.ejb.HibernatePersistence</provider>

                <properties>
                        <!-- Change this to create to have Hibernate clean up after the test or
                                create-drop to keep the results after the test is done -->
                        <property name="hibernate.hbm2ddl.auto" value="update" />

                        <property name="hibernate.query.factory_class"
                                value="org.hibernate.hql.ast.ASTQueryTranslatorFactory" />
                        <property name="hibernate.cache.provider_class" value="org.hibernate.cache.HashtableCacheProvider" />
                        <property name="hibernate.archive.autodetection" value="class" />
                        <property name="hibernate.dialect" value="org.hibernate.dialect.MySQL5InnoDBDialect" />
<property name="hibernate.connection.datasource" value="java:comp/env/jdbc/idotest"/>
                </properties>

        </persistence-unit>


Top
 Profile  
 
 Post subject: Re: JPA/Hibernate/Mysql - em.close() not releasing connections
PostPosted: Thu Feb 24, 2011 9:50 pm 
Newbie

Joined: Thu Feb 24, 2011 12:07 pm
Posts: 3
I am facing a similar problem as the OP with JPA/Hibernate/MSSQL/jtds/c3p0 on Windows XP.

Following code is scheduled to run periodically, every 2 minutes.
---------------------------------------------------------------------------------------
Code:
public class Sender extends TimerTask {
   private static EntityManager em;
   private static EntityManagerFactory factory;

        public void run() {
                em = factory.createEntityManager();
                EntityTransaction tx = em.getTransaction();
                try
      {
         tx.begin();
                        /* Query db and do email sending work here */
         tx.commit();
      }catch(Exception ex)
      {
         if(tx.isActive())
         {
            tx.rollback();
         }
      }      
      finally
      {
         em.close();
      }                       
        }
}

---------------------------------------------------------------------------------------
Even when there are no results returned and nothing to send, Activity Monitor shows that
'CPU' and 'Physical IO' increased.
Shouldn't em.close(); cause the connection to be cleared from the list of processes
during the 2minutes prior to the next pass?

Right now, it never is and the figures soon run into the 10,000 to 20,000.

I've also tested with combinations of OpenJPA, PostgreSQL, MS JDBC Driver 3.0.
Same thing. pg_stat_activity on PostgreSQL reports the connection as long as my app is running.
Either I'm doing something wrong in the code, or it's the expected behaviour.


Top
 Profile  
 
 Post subject: Re: JPA/Hibernate/Mysql - em.close() not releasing connections
PostPosted: Mon Feb 28, 2011 3:48 am 
Newbie

Joined: Thu Feb 24, 2011 12:07 pm
Posts: 3
The issue seems to have been fixed by setting c3p0.max_size to just what the application needs, i.e 1, and more importantly I believe, setting c3p0.idle_test_period to a decent 300 seconds. Previously we had an extra 0 there.


Top
 Profile  
 
 Post subject: Re: JPA/Hibernate/Mysql - em.close() not releasing connections
PostPosted: Mon Feb 28, 2011 5:05 am 
Newbie

Joined: Sat Sep 26, 2009 2:13 pm
Posts: 6
Thanks for the reply.

After debugging, looking into hibernate source code and trying pretty much everything I found out 2 things.
Long hours tend to limit you skills and memory.

I added a logging statement that opened a new EntityManager.
The confusing part was that connections were not exactly 'leaked' since I used a ThreadLocal and Tomcat re-uses the request Threads. So even though I was leaving connections opened, at the next request Tomcat was re-using them (but these could not be expired).

After I tried a hello world test to open and close the connection (and see everything worked fine), it was just a matter of few minutes to find my error.


Top
 Profile  
 
 Post subject: Re: JPA/Hibernate/Mysql - em.close() not releasing connections
PostPosted: Mon Feb 28, 2011 11:20 pm 
Newbie

Joined: Thu Feb 24, 2011 12:07 pm
Posts: 3
jid1 wrote:
After I tried a hello world test to open and close the connection (and see everything worked fine), it was just a matter of few minutes to find my error.


error which was?... same c3p0 config issue too, or sth else?


Top
 Profile  
 
 Post subject: Re: JPA/Hibernate/Mysql - em.close() not releasing connections
PostPosted: Tue Mar 01, 2011 4:25 am 
Newbie

Joined: Sat Sep 26, 2009 2:13 pm
Posts: 6
"I added a logging statement that opened a new EntityManager. "...and did not close it.

So it was a code issue for me.

Some helpful advise:
mysql> SHOW PROCESSLIST ;
Which will show you what your connections are doing.
Also, try lowering you my.cnf and c3p0 timeouts and observe what happens to see what is wrong.

Hope it helps..


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 6 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.