Idle transactions Hibernate / PostgreSQL

Question

I got a problem with my web app developed with Hibernate 3 and postgres. The problem is that every signle query that is executed and commited does not release connection in pool (limit = 100). I did some queries to DB after an action (SELECT, INSERT, etc...) and I got the results:

QUERY:

SELECT pid, query, state FROM pg_stat_activity;

5436;"SELECT...";"idle" <- this is ok 
6776;"SELECT..." ;active" <- this is ok
5324;"COMMIT";"idle"    
6840;"COMMIT";"idle"    
6276;"COMMIT";"idle"    
4804;"COMMIT";"idle"    
5812;"COMMIT";"idle"    
2608;"COMMIT";"idle"    
2576;"COMMIT";"idle"    
3896;"COMMIT";"idle"    
4660;"COMMIT";"idle"    
692;"COMMIT";"idle" 

As you can see the problem are transactions that are commited, they stay in connection pool and block it. Next actions cause this to grow and grow and when pool is overdosed i can't connect to DB. I know there is a way to manualy drop them (SELECT pg_terminate_backend()) but i prefer solution in Hibernate. Now let go to my code, maybe it will clear up.

This is example of method in my DBManager (class that executes queries and manages DB):

public List getGroups()
{
    Session session = sessionFactory.openSession();
    List<Group> groups = new ArrayList<Group>();

    try
    {
        session.beginTransaction();
        groups = session.createQuery( "from Group WHERE locked='FALSE' ORDER BY startHours" ).list();
        session.getTransaction().commit();

    }
    catch ( Exception e )
    {
        // logs
    }
    finally
    {
        try
        {
            session.close();
        }
        catch ( Exception e )
        {
        }
    }

    return groups;
}

Any other methods are done the same way. My session factory is defined at class level:

  public class DBManager
  {

   SessionFactory sessionFactory = new Configuration().configure().buildSessionFactory();

    public DBManager()
    {

    }

     // METHODS HERE
  }

Please tell me what am I doing wrong... Session is opened and closed via try{}catch{}finally{}. It seems that after a commit transaction is not properly closed.

Here is my hibernate.cfg:

    <!-- Database connection settings -->
    <property name="hibernate.connection.driver_class">org.postgresql.Driver</property>
    <property name="hibernate.connection.url">jdbc:postgresql://localhost:5432/szkola_plywania_test</property>
    <property name="hibernate.connection.username">postgres</property>
    <property name="hibernate.connection.password">postgres</property>

    <!-- JDBC connection pool(use the built-in) -->
    <property name="hibernate.connection.pool_size">1</property>

    <!-- SQL dialect -->
    <property name="hibernate.dialect">org.hibernate.dialect.PostgreSQLDialect</property>

    <property name="hibernate.connection.release_mode">after_statement</property>    

    <!-- Disable the second-level cache -->
    <property name="hibernate.cache.provider_class">org.hibernate.cache.NoCacheProvider</property>

    <!-- Echo all executed SQL to stdout -->
    <property name="hibernate.show_sql">true</property> <!-- wynik pokazywany w konsoli -->

    <!-- Drop and re-create the database schema on startup -->
    <property name="hibernate.hbm2ddl.auto">update</property> <!-- create - nadpisywanie, update - dopisywanie -->

    <property name="hibernate.show_sql">true</property>

 <property name="hibernate.c3p0.acquire_increment">1</property>
    <property name="hibernate.c3p0.idle_test_period">100</property> <!-- seconds --> 
    <property name="hibernate.c3p0.max_size">100</property> 
    <property name="hibernate.c3p0.max_statements">0</property> 
    <property name="hibernate.c3p0.min_size">10</property> 
    <property name="hibernate.c3p0.timeout">100</property> <!-- seconds -->

    <!-- Names the annotated entity class -->
    <mapping class="com.school.hibernate.dto.User" />
    <mapping class="com.school.hibernate.dto.MailConfig" />
    <mapping class="com.school.hibernate.dto.Group" />
    <mapping class="com.school.hibernate.dto.Child" />
    <mapping class="com.school.hibernate.dto.Term" />
    <mapping class="com.school.hibernate.dto.Counter" />
    <mapping class="com.school.hibernate.dto.Invoice" />
    <mapping class="com.school.hibernate.dto.AgeGroup" />
    <mapping class="com.school.hibernate.dto.GroupTerm" />
    <mapping class="com.school.hibernate.dto.Instructor" />
    <mapping class="com.school.hibernate.dto.PassInstance" />

Answer 1

idle is not a problem. It's just an open connection that does nothing.

idle in transaction would be a problem as those connection are actively keeping "locks" on data (which e.g. might prevent auto-vacuum from doing its job properly).

The result from pg_stat_activity that you have shown does not indicate any problem.

As you can see the problem are transactions that are commited, they stay in connection pool and block it

No they don't "block it" (at least you have not shown any indication of that). The pool just keeps them open - this is what a connection pool does.

Opening a connection to a DBMS is a quite expensive operation, so it's good to keep them open to be re-used.

If you want to reduce the number of open connections (for whatever reason), then you need to configure your connection pool to do so (e.g. by lowering the "idle" timeout). You should show us the configuration of your connection pool in order to determine if everything is fine.