Version: Hibernate 3.1.3

I'm having problems with a simple saveOrUpdate call:

HibernateUtil.beginTransaction();
HibernateUtil.getSession().saveOrUpdate(obj);
HibernateUtil.commitTransaction();

The object obj is an object with two child relations. As obj is saved it also creates the necessary child associations. However when it does that it first needs to create a unque identifier for each object. To do this I'm using the HiLo generator method for each class.

This seems to work fine:

14:34:25,015 DEBUG AbstractBatcher:493 - opening JDBC connection
14:34:25,156 DEBUG SQL:130 - select next_hi from rcote.hibernate_unique_key for update
14:34:25,156 DEBUG SQL:151 - update rcote.hibernate_unique_key set next_hi = ? where next_hi = ?
14:34:25,156 DEBUG TableHiLoGenerator:64 - new hi value: 16288
14:34:25,171 DEBUG AbstractSaveEventListener:106 - generated identifier: 1645088, using strategy: org.hibernate.id.TableHiLoGenerator
14:34:25,171 DEBUG AbstractSaveEventListener:139 - saving [uk.ac.ebi.ook.model.hibernate.Term#1645088]
14:34:25,171 DEBUG Cascade:237 - processing cascade ACTION_SAVE_UPDATE for: uk.ac.ebi.ook.model.hibernate.Term
14:34:25,171 DEBUG Cascade:259 - done processing cascade ACTION_SAVE_UPDATE for: uk.ac.ebi.ook.model.hibernate.Term
14:34:25,171 DEBUG Cascade:237 - processing cascade ACTION_SAVE_UPDATE for: uk.ac.ebi.ook.model.hibernate.Term
14:34:25,171 DEBUG Cascade:259 - done processing cascade ACTION_SAVE_UPDATE for: uk.ac.ebi.ook.model.hibernate.Term

But has you can see from the trace, it isn't letting go of the connections.

I have the proxool connection pool configured, but I've also tried C3PO and the default pool but encountered the same problem.

If I use the proxool pool and set the maximum number of connections as 50, eventually the application errors when it hits that number of open connections.

I've debugged the code and I've managed to track the problem down to the isolater class, which is used to transactionally isolate the fetch of the next value of the primary key.

The problem is in this part of the code in the class org.hibernate.engine.transaction.Isolater. It seems that the TableGenerator class forces the opening of a new connection each time a new sequence value is generated when it calls the isloater:

/**
* An isolation delegate for JDBC-based transactions. Basically just
* grabs a new connection and does the work on that.
*/
public static class JdbcDelegate implements Delegate {
private final SessionImplementor session;

public JdbcDelegate(SessionImplementor session) {
this.session = session;
}

public void delegateWork(IsolatedWork work) throws HibernateException {
Connection connection = null;
boolean wasAutoCommit = false;
try {
connection = session.getBatcher().openConnection();
if ( connection.getAutoCommit() ) {
wasAutoCommit = true;
connection.setAutoCommit( false );
}

work.doWork( connection );

connection.commit();
}
catch( Throwable t ) {
try {
if ( connection!= null && !connection.isClosed() ) {
connection.rollback();
}
}
catch( Throwable ignore ) {
log.trace( "unable to release connection on exception [" + ignore + "]" );
}

if ( t instanceof HibernateException ) {
throw ( HibernateException ) t;
}
else if ( t instanceof SQLException ) {
throw JDBCExceptionHelper.convert(
session.getFactory().getSQLExceptionConverter(),
( SQLException ) t,
"error performing isolated work"
);
}
else {
throw new HibernateException( "error performing isolated work", t );
}
}
finally {
if ( wasAutoCommit ) {
try {
connection.setAutoCommit( true );
}
catch( Throwable ignore ) {
log.trace( "was unable to reset connection back to auto-commit" );
}
session.getBatcher().closeConnection( connection );
}
}
}
}

If you look above it's easy to see that the code is only closing the connection when using normal JDBC transactions (not JTA) in the case where autocommit was enabled.

As you can see, when I enable autocommit=true in the hibernate configuration file, the connections end up being closed correctly:

14:52:31,468 DEBUG AbstractBatcher:493 - opening JDBC connection
14:52:31,468 DEBUG SQL:130 - select next_hi from rcote.hibernate_unique_key for update
14:52:31,640 DEBUG SQL:151 - update rcote.hibernate_unique_key set next_hi = ? where next_hi = ?
14:52:31,656 DEBUG AbstractBatcher:508 - closing JDBC connection (open PreparedStatements: 0, globally: 0) (open ResultSets: 0, globally: 0)

So... Any suggestions? Is this a bug? It looks like it... What are the possible work arounds? Should I try to use JTA transactions? Is it possible for a standalone application?

I think this has been fixed in the release 3.2 CR3

[HHH-1734] - Connection leak when using hilo strategy in SE environment