Too many connections Hibernate and mysql

Question

I'm at a complete loss, I'm running a batch job using both hibernate and mysql and after a few hours I get an exception saying I'm using to many connections. I've read all the articles on SO, but none seem to relate to me. I'm using Tapestry-hibernate with a very simple configuration, http://tapestry.apache.org/using-tapestry-with-hibernate.html. No where's am I creating a new SessionFactory, once the application starts up, I just inject the hibernate Session into my class.

This is my current connection view with mysql. enter image description here

My batch job is threaded and everytime a new thread fires off, the threads_connected seems to increment.

my cfg.xml file.

<hibernate-configuration>
<session-factory>
    <property name="hibernate.connection.datasource">jdbc/company</property>
    <property name="hbm2ddl.auto">validate</property>
    <property name="hibernate.show_sql">false</property>

    <property name="hibernate.search.default.directory_provider">filesystem</property>
    <property name="hibernate.search.default.indexBase">/users/george/Documents/indexes </property>

    <property name="hibernate.cache.use_second_level_cache">true</property>
    <property name="hibernate.cache.use_query_cache">false</property>
    <property name="hibernate.cache.region.factory_class">org.hibernate.cache.ehcache.EhCacheRegionFactory</property>
</session-factory>

Sample of basic session usage in class - "please note below code is not production code, just used to illustrate session usage.

private final Session session;

public LineReaderParserImpl(Session session) {
    this.session = session;
}

public void parse() {
    exec.submit(new Runnable() {
        public void run() {
          for (int i = 0; i < 10000; i++) {
            Object object = session.createCriteria()...

            session.save(object);
            session.getTransaction().commit();

            if (currentRow % 250 == 0 || currentRow == totalRows) {
                try {
                    session.getTransaction().commit();
                } catch (RuntimeException ex) {
                    try {
                        session.getTransaction().rollback();
                    } catch (RuntimeException rbe) {
                        throw ex;
                    } finally {
                        session.clear();
                        session.beginTransaction();
                    }
                }
            }  
         }              
    }
}

why does you have ` session.beginTransaction();` in finally - do you not want to close — Scary Wombat, Nov 01 '13 at 04:25
when I close it it crashes, so I begin transaction for the next iteration. Is this incorrect? SEVERE: null org.hibernate.SessionException: Session is closed! at org.hibernate.internal.AbstractSessionImpl.errorIfClosed(AbstractSessionImpl.java:129) at org.hibernate.internal.SessionImpl.fireSaveOrUpdate(SessionImpl.java:731) at org.hibernate.internal.SessionImpl.saveOrUpdate(SessionImpl.java:727) at org.hibernate.internal.SessionImpl.saveOrUpdate(SessionImpl.java:723) at $Session_1332660432a2a61f.saveOrUpdate(Unknown Source) at $Session_1332660432a2a5de.saveOrUpdate(Unknown Source) — Code Junkie, Nov 01 '13 at 04:28
This is wrong. Close your connection (release to pool) and after using it (not in finally) and open it (get from pool) before needing to use. — Scary Wombat, Nov 01 '13 at 04:42
@user2310289 I repaired my transaction matching the best answer found here http://stackoverflow.com/questions/16593965/how-to-properly-close-and-open-a-hibernate-session and that seem to resolve the incrementing threads connected, now I just need to resolved the incrementing connections. It may be a result of chandan's answer. — Code Junkie, Nov 01 '13 at 05:02

score 1 · Accepted Answer · answered Nov 01 '13 at 08:38

1

The hibernate session provided by tapestry-hibernate is PerThread scoped. PerThread scoped services are cleaned up via PerthreadManager.cleanupThread(). Tapestry automatically cleans up request threads and threads managed by ParallelExecutor. If you are managing your own thread, you must call PerthreadManager.cleanupThread() explicitly.

answered Nov 01 '13 at 08:38

lance-java

25,497
4
59
101

Thanks lance, fixing my transaction as user2310289 suggested above fixed my thread connection issue. I ran batch jobs all night without running out of connections. I did notices connectiins in terminal seemed to increase, but I dont believe that is meaningful? Do I just add PerthreadManager.cleanupThread() to the end of the thread? – Code Junkie Nov 01 '13 at 12:04
Lance, found a nice little post over on the nabble, http://apache-tapestry-mailing-list-archives.1045711.n5.nabble.com/Reusing-tapestry-hibernate-session-in-a-thread-td5438833.html – Code Junkie Nov 01 '13 at 13:09
Either cleanupThread() in a finally block. Or use the ParallelExecutor which does it for you. – lance-java Nov 01 '13 at 13:53
1

You might find it cleaner to call ```HibernateSessionManager.commit()``` and ```HibernateSessionManager.abort()``` instead of ```session.getTransaction().commit()``` and ```session.getTransaction().rollback()```. This is ultimately how [@CommitAfter](http://tapestry.apache.org/current/apidocs/org/apache/tapestry5/hibernate/annotations/CommitAfter.html) works. – lance-java Nov 01 '13 at 15:11
Should I also getSession from HibernateSessionManager too or should I continue to Inject Session into my class and use it? – Code Junkie Nov 01 '13 at 19:49
Both are equivalent. I'd probably use @Inject – lance-java Nov 02 '13 at 18:31

score -1 · Answer 2 · answered Nov 01 '13 at 04:34

-1

Considering that Session Object is not thread-Safe maybe there's something wrong with the way you are defining the session object which is getting injected.

It is not intended that implementors be threadsafe. Instead each thread/transaction should obtain its own instance from a SessionFactory.

answered Nov 01 '13 at 04:34

chandan Singh

67
5

Interesting, do you think maybe that might be the cause of the threads connected to continue to creep up? To be completely honest, I really don't know what that means of if it's really important. – Code Junkie Nov 01 '13 at 04:37
if you dont close the sessions , then all get used up because in your implementations ,maybe, you are creating & returning a new session, even though previous session is not closed. – chandan Singh Nov 01 '13 at 05:24
also its worth trying out sessionFactory.getCurrentSession() – chandan Singh Nov 01 '13 at 05:25
This is incorrect, tapestry-hibernate provides a singleton hibernate session that is thread safe. It proxies through to a PerThreadValue. More detail in my answer on this thread – lance-java Nov 01 '13 at 08:42

Too many connections Hibernate and mysql

2 Answers2

Linked