Sleep mode that keeps an inactive session in oracle db even after the session is closed

Question

In my hibernation application, I wrote below code to save an EmployeeRegistration object in 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 the session, it keeps inactive sessions in oracle db. I checked the inactive session using the following query in oracle.

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

How to kill an entire inactive session through sleep mode. Can someone help me in solving this problem.

Answer 1

make sure you do not create sessionFactory multiple times.

In the finally code, try the following:

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

Each time you get a session, if necessary, a new database connection is created (es: transaction started). To overcome this behavior, it is better to use a connection pool.

Edit from docs

Hibernate's own channel aggregation algorithm, however, is rather rudimentary. It is designed to help you get started and is not intended to be used on a production system or even for performance testing.

I suggest you use the connection pool before solving problems from something specific elementary from the creators of the library ...

Answer 2

How many inactive sessions have you noticed in the Oracle database for this user?

If you see it, it could be due to the SQL terminal session that you opened with the database.

If you see that it is more than one, and the counter is incremented every time you execute the code, then you are not releasing the connections properly. It could be a connection leak.

Or it may be that you are actually using the connection pool, and inactive connections are connected to the connections that it manages. Inactive here means that they are not currently performing an action. This does not mean that there is a connection leak. Posting your config will help shed some light. You can check if you are using a connection pool by creating a stack trace or debugging code.

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