Hi,

This is the scene.

Session sess = getSessionFactory().getCurrentSession();
txn = sess.beginTransaction();

int i = 0;
for(MyRecord record: records)
{
sess.persist();
if(i++ % 512 == 0)
{
sess.flush();
sess.clear();
}
}

sess.commit();

This code trying to call flush every 512 rows. However it gets called even for i =0. (This is bug in code :) )

So now lets say there are 513 records.
In this case flush and clear will be called twice. First when i == 0 and later when i == 512.
And later it calls commit.

So problem now is this second flush call takes 10 mins to complete. This is happening only for second call.
Meaning lets say there are 2000 records.
So created batches are based on index values will be :
0-0, 1-512, 513-1025 , 1026.... and so on. So there will be i think 4 flush() calls.
Every batch gets flushed in 3-4 seconds. But this second call to flush for 1-512 batch takes 10 mins to complete.

And it is not just flush() actually. When second call happens to be on commit(), it stucks.
Meaning for records say 400.
For i = 0, it will call flush and clear. So for remaining 399 it directly calls commit() after getting out of the loop.
So on this commit() also it stucks for 10 mins. (i.e. only when it happens to be just after first flush() call)

My table is ALLPAGE locked. It has a primary key. It has at max 10000 records. This is plain JDBC application.
Database is sybase. My Query cache: disabled, second level cache is enabled, Query translator: org.hibernate.hql.ast.ASTQueryTranslatorFactory, hibernate.current_session_context_class is thread, Session factory is local host non-jta. table data gets deleted once nightly.

If you have any suggestion, hint on why this second flush() or (commit() ) call stuck for 10 mins always, will be appreciated.

thanks

Turn up logging to TRACE mode for org.hibernate.* and you will most likely get a glimpse of what is going on under the hood.

ya that makes sense. but actually its not easily replicable in dev. Its happening in my prod environment.
which i cant change to trace level.

Well your code is more like psedo code I guess ;-)
Does this MyRecord entity u are saving contain links to other entities which are being saved as a result of cascading? For instance maybe each has a "list of many-to-many" and thus the first batch saves theirs lists and also the listed items but the latter batches only involve saving the list but not the listed items which were already saved?

no. there is no cascade or relationships on this table.
also in mapping file for this table there is no relationship declared.
So any insert or update should not cause any such cascading effect.
Even I run this process on dev and logs at debug level , can see only plain update or insert fired
directly for this table.

One thing noticed though. Even i am having explicit calls to begin, commit,rollback etc. in my code
can see that in it is starting chained mode transactions.
when i do : select @@tranchained on database it says 0 (unchained).
However db logs reveals it is doing chained mode transaction for my process.

So will that be problem ? Or is there something i need to correct on my hibernate configuration for that ?

found way to make transaction to CHAINED OFF mode by using session.connection(). But not sure will that help in this case.

Also when i checked db activity during time when my process was stuck on flush,
it says "waiting for incoming network data" and no lock contention detected.

Mmm well it will not be possible for anyone else to replicate your issue because apparently it's got to do with your configuration and setup. What database are you using? Where is it running? What kind of connection pool are u using. What kind of transaction manager?
It would be great if someone could fix the issue you were having and also tell us all what the cause is.
I keep my fingers crossed.

found solution of this issue long back.
just thought of updating on forum so that every knows the solution.
We have show_sql set to true on prod env.
when i run my process in test env i get easy hold of standard output to write my sql's and everything works fine.
But moment it runs in background cron jobs its not easy to output to sto.

So because of this i was experiencing such weird slowness. And funny part is the way it was reflecting only on batch of 2 nd to 512 th record.

Anyways though mentioned everyone else in hibernate docs to not to use show_sql in i used it by mistake and paid the price :)