Hibernate keeping inactive session in oracle db even after closing the session

Question

In my hibernate application I have written below code for saving EmployeeRegistration object into oracle db.

public Integer submitDetails(EmployeeRegistration es)
{
    Session session = factory.openSession();
    Transaction tx = null;
    Integer employeeID = null;
    try
    {
        tx = session.beginTransaction();
        employeeID = (Integer)session.save(es);
        session.flush();
        tx.commit();
    }
    catch(HibernateException e)
    {
        if(tx != null)
        {
            tx.rollback();
        }
        e.printStackTrace();
    }
    finally
    {
        if(session.isOpen())    {
        session.close();
        }
    }
    return employeeID;
}

After closing session, it keeps inactive sessions in oracle db. I have checked the inactive session using the below query in oracle.

SQL> select USERNAME,COUNT(*) FROM V$SESSION WHERE STATUS='INACTIVE' GROUP BY USERNAME ;

How to kill all the inactive session through hibernate. Can anyone help me in resolving this issue.

Do you have connection pooling enabled? Sessions are cleaned-up in oracle periodically. It may not be instant. — OldProgrammer, Jun 24 '15 at 11:25
Hibernate configuration file only contains DB details,dialect class and the mapping class name nothing else. — Badal, Jun 24 '15 at 11:53
Because you still keep the session instance in your code after session.close(). You can try to use "session = null" and wait for GC works. But someone told me it didn't work, someone told me it worked. — MageXellos, Jun 24 '15 at 14:02

Paizo · Accepted Answer · 2015-06-25T08:07:10.937

1

make sure that you are not creating the sessionFactory several times.

In your finally code try the following:

if (session != null && session.isOpen()) {
    session.flush();
    session.close();
}

Every time you get a Session a new database connection is created if needed (es: transaction started). It is better to use a connection pool to overcome this behavior.

Edit from the docs

Hibernate's own connection pooling algorithm is however quite rudimentary. It is intended to help you get started and is not intended for use in a production system or even for performance testing.

I would suggest you to use a connection pooling before dealing with issues from something defined rudimentary from the creators of the library...

edited Jun 25 '15 at 08:07

answered Jun 24 '15 at 14:44

Paizo

3,986
30
45

Do you think checking session object with null and calling flush method inside finally block will resolve the issue however earlier I had tried the same code but it didn't help me out. Anyway I will check it once again. – Badal Jun 25 '15 at 04:06
I tried the above code and it resolved the issue. Thank you :) – Badal Jun 25 '15 at 08:19

score 0 · Answer 2 · answered Jun 24 '15 at 19:11

How many inactive sessions do you notice in the Oracle database for this user?

If you see one, then it might be related to the SQL terminal session that you have open with the database.

If you see it to be more than one and the count keeps increasing every time you execute your code, then you are not releasing the connections properly. This could be a connection leak.

Or it could be that you are indeed using a connection pool and the inactive connections are related to the connections it manages. Inactive here means that they are currently not executing an action. It does not mean there is a connection leak. Posting your config will help shed some light. You can validate if you are using a connection pool by either generating a stack trace or debugging through the code.

Also, make sure that there is no other application that is accessing the database with the same credentials.

The inactive session count is more than one and in configuration file I have specified the DB details,dialect class and the mapping class name nothing else, I am not using any connection pooling mechanism in this application and if you will see my above code I am closing the session as well. Is there something else needs to be done ? — Badal, Jun 25 '15 at 04:04

Hibernate keeping inactive session in oracle db even after closing the session

2 Answers2