1

I'm using: Hibernate 3.6.8 with mySQL, Tomcat 6, JDBC connection pooling

I've run into a problem where Hibernate is opening a large number of connections, which eventually causes my web app to not be able to get a new connection. At first I thought the problem was that I was running without a connection pool, so I hooked up Tomcat JDBC pooling.

This made the problem worse. Instead of failing after a handful of queries, it failed immediately. This turned out to be because Hibernate was trying to open a larger number of connections than my pool size (30). I checked, and everywhere I'm creating an EntityManager, I'm calling close(), within a finally clause if there's a try, so I wasn't sure why they were staying open.

Looking at the source for close(), I discovered that it doesn't actually close if there's a transaction open, so I tried calling getTransaction().commit() on the entity manager, even though its just a select statment. This had no effect. Then I discovered this configuration flag, and turned it on:
<property name="hibernate.ejb.discard_pc_on_close" value="true" />

This made a difference, I got an exception when I tried to render the object for output: failed to lazily initialize a collection of role : XXX.Comment.attachments, no session or session was closed

The issue seems to be that I make a number of queries to create a collection of Comments, and all of these queries keep a connection open. The Comment class contains this:

@OneToMany
@JoinTable(name = "Object", joinColumns = { @JoinColumn(name = "parentId") }, inverseJoinColumns = { @JoinColumn(name = "id") })
public List<Attachment> getAttachments()
{
    if (attachments == null)
    {
        attachments = new ArrayList<Attachment>();
    }
    return attachments;
}

I'll mention that there's some inheritance going on here.
Attachment is a subclass of KObject (table name Object in the db), and KObject contains the parentId used to map back to Comment (which is also a subclass of KObject).

For clarification, here's my call to the entity manager:

public <T> T findById(Class<T> objClass, String id)
{
    EntityManager em = theEMF.createEntityManager();
    em.getTransaction().begin();
    try {
        T obj = em.find(objClass, id);
        return obj;
    } catch(NoResultException e) {
        return null;
    } finally {
        em.getTransaction().commit();
        em.clear();
        em.close();
    }   
}

theEMF is a static member variable that's initialized on first use.

I thought the solution would be making it @OneToMany(fetch=FetchType.EAGER), but this had no effect. What am I doing wrong?

Ryan
  • 11
  • 2
  • How do you manage transactions in your application? Do you use any kind of dependency injection library/framework? – Olaf Feb 22 '13 at 21:33
  • Do you use OpenSessionInViewFilter or something like that? – Amir Pashazadeh Feb 22 '13 at 21:35
  • I use the EntityManager, which I understand is a wrapper around the session. I've added code on that to the question. – Ryan Feb 22 '13 at 22:00
  • I've also tried using "fetch all" in the HQL request, but that also seemed to have no effect. – Ryan Feb 22 '13 at 22:36
  • Adding this configuration: solves the connection issue, but causes all writes to the database to throw this exception: java.sql.SQLException: Can't call commit when autocommit=true – Ryan Feb 23 '13 at 00:56
  • To fix the autocommit problem I need to add to the resource url: relaxAutoCommit=true. However, now there's a new problem where inserts don't work. If I turn show_sql on, I see the inserts, but the database doesn't. I tried adding an extra flush in, but no difference. – Ryan Feb 23 '13 at 06:37
  • The after_statement release_mode is causing a rollback to happen after the insert, but before the commit. How can I fix this? – Ryan Feb 23 '13 at 08:14

0 Answers0