PostgreSQL not closing transactions using JPA, JEE6

Question

I have a method that I use to gather data for a report, while running I monitor the PostgreSQL server and I see that the system does not close the transacition after they end.

The problem is that in time, the server crash out of memory

public Integer ogetNumber() {


        EntityManager em = emf.createEntityManager();

        Object obj = null;


        try {
            Query qry = em.createQuery("SELECT MAX(p.number) FROM Number p");
            obj = qry.getSingleResult();
            if (obj == null) {
                return 0;
            }
            return (Integer) obj;
        } catch (Exception e) {
            System.out.printf(e.getMessage());
        } finally {
             if (em != null) {
                em.close();
            }



        }
         return (Integer) obj;
    }

My persistence.xml:

<?xml version="1.0" encoding="UTF-8"?> <persistence version="1.0"
    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_1_0.xsd">  
<persistence-unit name="PersistenceUnit" transaction-type="RESOURCE_LOCAL">
    <provider>org.hibernate.ejb.HibernatePersistence</provider>
    <class>entities.Validacion</class>
    <class>entities.ValidacionDB</class>

    <exclude-unlisted-classes>false</exclude-unlisted-classes>
    <properties>
      <property name="hibernate.show_sql" value="false"/>
      <property name="hibernate.connection.username" value="postgres"/>
      <property name="hibernate.connection.driver_class" value="org.postgresql.Driver"/>
      <property name="hibernate.connection.password" value="password"/>
      <property name="hibernate.connection.url" value="jdbc:postgresql://localhost:5432/test"/>
      <property name="hibernate.cache.provider_class" value="org.hibernate.cache.NoCacheProvider"/>
      <property name="hibernate.hbm2ddl.auto" value="update"/>
    </properties>
</persistence-unit>

Where are you opening a transaction here ? What is the configuration of the EntityManagerFactory ? Which implementation of JPA are you using ? — Alexandre Cartapanis, Jan 28 '16 at 16:03
I just get a entitymanager "em" , set the query, get the resultlist and return it. Then I close the "em". I am using JPA 2.0 — Alexev, Jan 28 '16 at 16:04
EntityManagerFactory emf = Persistence.createEntityManagerFactory("PersistenceUnit"); — Alexev, Jan 28 '16 at 16:05
and adding to the above questions (put your answers IN THE QUESTION, not in the comments - include persistence.xml), clearly PostgreSQL is not responsible for "closing the transaction" ... it just does what the JPA implementation tells it to do. What connection pool? — Neil Stockton, Jan 28 '16 at 16:05
Making a query is not enough to open a transaction. you should add a "em.getTransaction().open();" and a "em.getTransaction().commit()". — Alexandre Cartapanis, Jan 28 '16 at 16:06
The method returns the right values, it just dont close the conexion — Alexev, Jan 28 '16 at 16:12

score 0 · Answer 1 · answered Jan 28 '16 at 18:59

Here's the best way to do this work in general that won't fail. First, you want to use CDI (Context Dependency Injection) to inject an instance of the EntityManger into your session beans:

@PersistenceContext(unitName = "persistenceUnitNameHere", type=PersistenceContextType.TRANSACTION)
private EntityManager entityManager;

Now delineating the transactions per method or per class or both:

@TransactionAttribute(REQUIRED)

Now simply use the EntityManager and do not close it. There is no need for commit/rollback as that will be performed at the method boundaries given the transaction attribute is inserted properly. Just allow RunTimeExceptions to percolate up from the method call for rollbacks.

That's a lot of information in a short paragraph but the particulars you can look up in various EE6&7 docs. This approach will prevent you from ever leaking EntityManager connections plus performance will improve as the App Server maintains entity managers for you.

You are also less likely to muck up transaction boundaries IMHO.

I finally "kill" the entitymanagerfactory and that solve my problems. I rather use your solution but I am working with anothers person program so I cannot choose the container and Tomcat7 does not support CDI (or else I will be using it for sure) — Alexev, Jan 29 '16 at 12:35
Oh goodness. I didn't realize you were using tomcat. Ya, CDI isn't an option for you then. — sagneta, Jan 29 '16 at 14:38
I actually "solve2 the problem by destroying the factory itself. anyway a couple day after that some people point me to http://stackoverflow.com/questions/7862700/best-practice-to-get-entitymanagerfactory where there is a best solution — Alexev, Feb 03 '16 at 13:27

PostgreSQL not closing transactions using JPA, JEE6

1 Answers1