Hibernate session.close() not returning connection to pool

Question

My application has long running transactions and hence I tried the option session.close() at the end of every method to ensure that the connection objects are not held indefinitely for long time.

When session.close() option is used, I could see that the Hibernate's session object and the corresponding Connection object obtained from session.connection() are destroyed properly. But the issue is with the connection pool. The connection obtained by the session is not released back to the connection pool even after closing the session. Other requests are found waiting for connection from the pool.

I am using JTA transaction in my application. In hibernate.cfg.xml, I have set connection.release_mode to auto (default) and connection.autocommit to true.

Has anyone faced this issue? Please let me know what am I missing here.

Follow-up: This is my hibernate configuration file details:

<property name="connection.datasource">MXoraDS</property> 
<property name="dialect">org.hibernate.dialect.Oracle9Dialect</property> 
<property name="connection.release_mode">after_statement</property> 
<property name="hibernate.transaction.manager_lookup_class">org.hibernate.transaction.WeblogicTransactionManagerLookup</property> 
<property name="hibernate.transaction.factory_class">org.hibernate.transaction.JTATransactionFactory</property>
<property name="hibernate.current_session_context_class">org.hibernate.context.JTASessionContext</property> 
<property name="transaction.auto_close_session">true</property> 
<property name="max_fetch_depth">2</property>

We use JSF and EJB 2.1 at the application layer connecting to Oracle DB. The after_statement doesn't seem to release the connection to the pool. Please let me know if u need any more details.

Answer 1

I am using JTA transaction in my application. In hibernate.cfg.xml, I have set connection.release_mode to auto (default) and connection.autocommit to true.

Could you try to define the hibernate.connection.release_mode property to after_statement explicitly? I know this is supposed to be the default but, depending on your context (could you be using Spring?), auto might not behave as expected (see here and here).

For reference, here is what the Table 3.4. Hibernate JDBC and Connection Properties writes about the property hibernate.connection.release_mode:

Specifies when Hibernate should release JDBC connections. By default, a JDBC connection is held until the session is explicitly closed or disconnected. For an application server JTA datasource, use after_statement to aggressively release connections after every JDBC call. For a non-JTA connection, it often makes sense to release the connection at the end of each transaction, by using after_transaction. auto will choose after_statement for the JTA and CMT transaction strategies and after_transaction for the JDBC transaction strategy.

e.g. auto (default) | on_close | after_transaction | after_statement

This setting only affects Sessions returned from SessionFactory.openSession. For Sessions obtained through SessionFactory.getCurrentSession, the CurrentSessionContext implementation configured for use controls the connection release mode for those Sessions. See Section 2.5, “Contextual sessions”

If it doesn't help, please add more details about your environment and configuration (Spring?), how you get the session, etc.