My problem:
As part of the process of evaluating Hibernate as a replacement for our application's current EJB 1.1 CMP persistence, I created a set of JUnit tests that serially execute various Hibernate saves, updates and finds behind a stateless session bean. When I attempt to use a pooled datasource (hibernate.connection.datasource jdbc/MyPooledDS) or CMT datasource (hibernate.connection.datasource jdbc/MyEJBDS), the database connections are not properly released on session.close() and after running the tests a couple of times the database runs out of connections.

The same tests run fine with the basic datasource (hibernate.connection.datasource jdbc/MyDS), i.e. all connections are released back to the database on session.close().

One version of the test code serially calls a "CMT-flavour" stateless session bean, which uses a version of the ThreadLocal session class to open a new session, execute a save/update/find, flush the session and finally close the session.

Another version of the test code serially calls a "NON-CMT-flavour" stateless session bean, which uses another version of the ThreadLocal session class to open a new session, begin a Hibernate transaction, execute a save/update/find, commit the transaction and finally close the session.

Both versions produce the same problem.

If we don't use the CMT datasource, then the SQL executed by Hibernate will not join the CMT transaction started by the session bean (we've had this problem in the past with mixed JDBC and EJB CMP code in a session bean transaction). So this is unfortunately not an option for us at this stage.

We have been using the Orion CMT datasource with JDBC and CMP EJBs successfully for 4 years without encountering this problem - so I am not sure why it would start now?

My guess is that Hibernate is handing the connections back to Orion, where they are being pooled, but Hibernate's next request for a connection somehow always triggers Orion to fetch a new connection from the database and Hibernate never gets the opportunity to reuse any of the pooled connections.

My hibernate.properties basically looks like this:

hibernate.query.substitutions true 1, false 0, yes 'Y', no 'N'
hibernate.dialect net.sf.hibernate.dialect.SybaseDialect
hibernate.connection.datasource jdbc/MyEJBDS
hibernate.connection.username x
hibernate.connection.password y
hibernate.connection.pool_size 0 (also tried 1 and commented out)
#hibernate.connection.provider_class net.sf.hibernate.connection.DatasourceConnectionProvider (also tried uncommented)
#hibernate.transaction.factory_class net.sf.hibernate.transaction.JTATransactionFactory (also tried uncommented)
#hibernate.transaction.manager_lookup_class net.sf.hibernate.transaction.OrionTransactionManagerLookup (also tried uncommented)
hibernate.jdbc.batch_size 0 (also tried with batch size of 42)
hibernate.jdbc.use_streams_for_binary true
hibernate.max_fetch_depth 1
#hibernate.cache.use_query_cache true (also tried uncommented)
hibernate.session_factory_name hibernate/session_factory
ALL OTHER PROPERTIES ARE COMMENTED OUT.

Hibernate version: 2.1.2
Orion version: 2.0.2
Database: Sybase ASE 12.5.1

Excerpt from my Orion data-sources.xml:

<data-source
name="MyDS"
class="com.evermind.sql.ConnectionDataSource"
location="jdbc/MyDS"
pooled-location="jdbc/MyPooledDS"
xa-location="jdbc/xa/MyDS"
ejb-location="jdbc/MyEJBDS"
connection-driver="com.sybase.jdbc2.jdbc.SybDriver"
schema="database-schemas/sybase.xml"
url="jdbc:sybase:Tds:localhost:5000/myDB?PACKETSIZE=4096"
username="x"
password="y"
>
<property name="REPEAT_READ" value="true"/>
<property name="DYNAMIC_PREPARE" value="true"/>
</data-source>

We have ruled out Orion as the cause of the connection leak, by creating a simple JDBC plugin that also requests, uses and closes connections from the Orion CMT datasource (basically emulating what our Hibernate prototype is doing). The simple JDBC code running behind the same session bean reuses CMT datasource connections from Orion's pool and does not end up causing our Sybase database to run out of connections.

My guess at this stage is that Hibernate 2.1.2 might be closing each connection more than once, because, with Orion's JDBC debugging on, I can see that Orion's pool size fluctuates between zero and one when used together with Hibernate.

Worse still, these "closed" connections do not become immediately available in the database, which hints that they might not have been cleanly closed down by Hibernate (i.e. perhaps not all result sets and prepared statements were closed).

Noone here will be able to help you with bugs in Orion's datasource. Please direct this question to your appserver vendor.

(of course, Hibernate does not leak connections)


TIA

As per my follow-up message above, the problem does lie with Hibernate, NOT with Orion. Hibernate is definitely closing the connection (hence there is no connection leak in this regard), BUT it is most likely also issueing more than one close on a connection, which is effectively causing the connection to be removed from Orion's connection pool.

Using the exact same pooled or CMT datasource via JDBC behind the same session bean does NOT empty Orion's connection pool and does NOT cause closed connections to become unavailable for extended periods of time.