ReleaseMode with Iterator

phide · **Joined:** Mon Dec 19, 2005 5:36 pm **Posts:** 14

We have a problem with connection. We access some objects with iterator, for each of them, there is a transaction. The code is like this (not exact code, but pretty self-documented):

Code:

ThreadLocalSession.beginSession();
Session sess = ThreadLocalSession.getCurrentSession();
Query q = sess.createQuery(QUERY_OBJECT);
Iterator ite = q.iterate();

while(ite.hasNext()){
  Object o = ite.next();
  ThreadLocalSession.beginTransaction();

  // Blabla

  ThreadLocalSession.endTransaction();
}

Sometime, we get the exceptioin said that we can not get next object because the connection is closed. I investigated the issue, and find out that the session does not held an jdbc connection all the time. When to get/release connection is controled by ReleaseMode. For our case, the session release the connection every time it commit a transaction. We have to change the relese mode to ON_CLOSE. It works. But the document said that is is hightly discouraged, which is pretty scary.

1. What I do not understand is that session is getting/realseing connection all the time, sometimes it works, sometimes it does not. Should it not work at all?
2. How is hibernate iteration implemented? Is it using database cusor?
3. Why ReleaseMode.ON_CLOSE is highly discouraged?

It seems like a pretty general question. Any idea/information is welcomed!

Thanks

Error:

Exception in thread "Thread-11" org.hibernate.exception.GenericJDBCException: could not get next iterator result
at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:91)
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:79)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:29)
at org.hibernate.impl.IteratorImpl.next(IteratorImpl.java:125)
at com.brightcove.payments.PaymentSchedulerFacade.processRecordForConsumerAccount(PaymentSchedulerFacade.java:328)
at com.brightcove.payments.PaymentSchedulerFacade.createConsumerInvoiceFromConsumerRecord(PaymentSchedulerFacade.java:111)
at com.brightcove.payments.ThreadConsumerPurchases.run(PaymentSchedulerFacadePayMediaLongTest.java:292)
Caused by: java.sql.SQLException: Closed Connection: next
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:162)
at oracle.jdbc.driver.DatabaseError.check_error(DatabaseError.java:861)
at oracle.jdbc.driver.OracleResultSetImpl.next(OracleResultSetImpl.java:171)
at org.hibernate.impl.IteratorImpl.postNext(IteratorImpl.java:83)
at org.hibernate.impl.IteratorImpl.next(IteratorImpl.java:120)

LaLiLuna · **Posted:** Mon Jan 09, 2006 6:14 pm

I propose to put your transaction around your loop. You are loading data outside of the transaction. May be this leads to the problem.

Regards Sebastian

phide · **Joined:** Mon Dec 19, 2005 5:36 pm **Posts:** 14

Yes. That is the problem. But there is something we want to do, becuase we have to iterate thousands of objects.

I am trying to understand how iteration is implemented. It seems depends on what connection pool you are using. And, in term of our transaction setup, different connection has different behaviors.

Dongsheng

phide · **Joined:** Mon Dec 19, 2005 5:36 pm **Posts:** 14

I mean I really need to keep the transacton setup (thousand of small transaction instead of a huge transaction)

Is anyway to make it work instead of change the ReleaseMode to ON_CLOSE?

phide · **Joined:** Mon Dec 19, 2005 5:36 pm **Posts:** 14

Thanks for reply.

LaLiLuna · **Posted:** Mon Jan 09, 2006 7:16 pm

I assume that you have little influence what the session is doing outside an transaction except the solution you found.

Just some ideas to work around
a)
if you are not obliged to use small transaction, use pagination to iterate through less than your total items. eg. 100 per each transaction
b)
fetch the ids into an array/list and loop over this list and fetch each item inside a transaction

Regards Sebastian

Please rate, if this helped.

phide · **Joined:** Mon Dec 19, 2005 5:36 pm **Posts:** 14

Thanks. that helped.

I am still curious about how iterator is implenmented here.

Dongsheng

phide · **Joined:** Mon Dec 19, 2005 5:36 pm **Posts:** 14

Actually, one thing I really do not understand is that for each iteration, the connection is new, but somehow the .next() still return correction results, especially for the one thread case.

By the way, I am uisng Hibernate built in connection provider.

DS