CODEWITHSUNDEEP
hibernatepostgresqljpa
Mateus Viccari
Mateus Viccari

Reputation: 7709

Hibernate + PostgreSQL - Connection with state "Idle in transaction"

I have a JSF application running on Tomcat using Hibernate. I have made a little test that exposed te problem - A servlet was created, and the only thing it does is this:

EntityManager em=null;
try {
    em = ConnectDb.getEntityManager();
    em.createQuery("from Frete").getResultList();
} catch (Exception e) {
    e.printStackTrace();
} finally {
    try{em.close();}catch(Exception ex){}
}

If i execute the following sql in pg_admin, i get one record, at the column "query" i see the sql executed by hibernate, and at the column "state" it says "idle in transaction".

But the problem is that no transaction was started, the only thing i did was create a query. And the EntityManager was closed - there was no exception thrown when closing it.

Maybe this is a configuration at persistence.xml, but i don't know. Here is my persistence.xml:

<?xml version="1.0" encoding="ISO-8859-1"?>
<persistence version="2.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_2_0.xsd">
  <persistence-unit name="GestaoPU" transaction-type="RESOURCE_LOCAL">
    <provider>org.hibernate.ejb.HibernatePersistence</provider>
    <properties>
      <property name="hibernate.show_sql" value="true"/>
      <property name="hibernate.dialect" value="org.hibernate.dialect.PostgreSQLDialect"/>
      <property name="hibernate.connection.autocommit" value="false"/>
      <property name="javax.persistence.jdbc.url" value="jdbc:postgresql://localhost/AtualyGestao"/>
      <property name="javax.persistence.jdbc.password" value="atg4747"/>
      <property name="javax.persistence.jdbc.driver" value="org.postgresql.Driver"/>
      <property name="javax.persistence.jdbc.user" value="AtualyGestao"/>
      <property name="hibernate.cache.provider_class" value="org.hibernate.cache.NoCacheProvider"/>
      <property name="hibernate.format_sql" value="false"/>
      <property name="hibernate.connection.encoding" value="ISO-8859-1"/>
    </properties>
  </persistence-unit>
</persistence>

Upvotes: 9

Views: 8186

Answers (3)

Chandramohan KN
Chandramohan KN

Reputation: 54

I was also hitting this issue in multiple places. Later replaced Hibernate with JDBC and never hit this issue again!

Upvotes: -1

Chandramohan KN
Chandramohan KN

Reputation: 54

I know this is a very old post. Writing my solution just to help others who are facing this issue currently. I also faced this issue recently. When I debugged more I came to know that hibernate was creating transactions automatically even in case of select queries. And it was not committing / roll back the queries properly. Hence I explicitly added the code to start and stop the transaction and this solved the issue. So the code looks something like this:

    SessionPersister session = CommonSessionPersisterFactory.INSTANCE.openSession(); 
    Transaction transaction = null;
    try {       
        transaction = session.beginTransaction();
        //do the query or update.

        transaction.commit();
    }
    catch(Exception re){
        transaction.rollback();
    }
    finally {
        if(session != null){
            Transaction tran = session.getTransaction();
            if(tran != null && tran.isActive() && !tran.wasCommitted() && tran.wasRolledBack()){
                tran.rollback();
            }
            session.close();
        }

Upvotes: 1

ben75
ben75

Reputation: 28746

the only thing i did was create a query

It seems you also execute it :

em.createQuery("from Frete").getResultList();

When you execute this line: hibernate execute the query and to do so a transaction is created by postgresql driver.

Since the auto-commit is set to false :

  <property name="hibernate.connection.autocommit" value="false"/>

then transaction is not commited, and that's why you get this message.

BTW : instead of 

try{em.close();}catch(Exception ex){}

write this :

try{em.close();}catch(Exception ex){ex.printStacktrace();}

Upvotes: 3

Related Questions