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.

Answer 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...

Answer 2

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.