Hi,

Here is my setup:
Hibernate version : 3.3.2
Spring version: 3.0
Transaction Manager with Spring Transaction Advice

I am having a problem with making hibernate calls in a loop. I know that it is not advisable to make database calls in a loop, unfortunately that is the way the business logic is.

The problem I am having is that when I am making repeated calls to hibernate in a loop (around 1000 calls), spring is creating a session & connection for each call and eventually the application hangs after iterating for a value equal to the max connections value in hibernate.config.xml. I believe this is the problem of using spring transaction advice of PROPAGATION=REQUIRED. So I changed the transaction advice to PROPAGATION=NEVER and I am managing the transaction myself with frequent flushes. However, the application still hangs after iterating for a while. Here is a sample code



Here is my hibernate.cfg.xml


With PROPAGATION=NEVER, the loop runs about 200 times before it hangs. With PROPAGATION=REQUIRED and not using the saveWithSession version, the code does not go more than 30 iterations. Can someone please help me how to solve this problem.

Thanks

http://docs.jboss.org/hibernate/core/3. ... ch-inserts

meybe You haven't to start new transactions for every save, meybe only one tr?
And Your app hangs. Any exceptions?

Hi,

I will have to flush the session because there may be other threads that may query the database after insert, so all the inserts cannot be in one big transaction.

No, there are no exceptions, just the dao.save call hangs indefinitely.

Can anyone please help me on this?

I fixed this problem.

The problem is in the way Spring and Hibernate manage sessions and transactions. After enabling a lot of logging information, I found that if there is any DAO call that is Spring transaction demarcated, and if you are using OpenSessionViewFilter, the sessions are not closed immediately after a transaction, rather they are put in a deferred close mode. In other words, the sessions are not closed and hence the underlying JDBC connection is not released. Since I am making calls in a loop, the transaction manager is creating session after session and not closing them fully, thus exhausting the jdbc pool. The solution to this is to set hibernate.connection.release_mode = after_transaction. This way the underlying JDBC connection is released as soon as the transaction is complete.