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.

Carrero

you should provide more information about your configuration. like which transactionmanager & transaction configuration, how do you manage your transactions, etc – Kiushu

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.

Cheater

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> – Carrero

More details: <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. – Carrero

Answer 2

if you are using the JDBCTransactionManager, the connection will be returned to the connectionpool when the transactions ends.

Kiushu

Hi thanks for your response. We are using Weblogic Transaction Manager (org.hibernate.transaction. WeblogicTransactionManagerLookup) and JTATransactionFactory. The transaction is managed by the container which takes care of the connection object. When using CMT, is it not possible programatically return the connection to the connection pool by closing the hibernate session? – Carrero

in that case, weblogic adds the connection to the transaction. when the transaction ends, weblogic will return the connection to the pool – Kiushu

Yeah it returns the connection to the pool after the transaction ends either by commit or rollback. But inside the transaction I have calls to external applications which takes longer time to execute. I am trying to close the connection and return it to the pool just before the call to the long running methods and again reconnect if required. This way I will not hold the connection for long time. Please tell me if this is possible. Can I return a connection to the pool using session.close or disconnect inside a long running transaction? – Carrero

you cannot return the connection to the pool while a transaction is in progress. suspending the transaction has no effect. so refactor your program so the long running calls are performed outside the transaction, i.e. before you start your transaction. or add more connections to your connection pool – Kiushu