Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version: hibernate3.0.5.jar

Mapping documents:

Code between sessionFactory.openSession() and session.close():
We use Session Beans with CMP, within that we use hibernate for persistence (instead of entity beans). if I am not wrong, hibernate transaction will attach itself to container transaction. (sessionFactory.getCurrentSession())

Full stack trace of any exception that occurs: NO Exception

Name and version of the database you are using: DB2 9.1

The generated SQL (show_sql=true): Hibernate: update PROCESS_RUN set COMPLETED_BATCHES=COMPLETED_BATCHES+1, END_DTTM=getDate() where PROCESS_RUN_ID=?

Debug level Hibernate log excerpt:

Description:

We are using MDB, and every bean after completing its processing, updates the PROCESS_RUN table (update PROCESS_RUN set COMPLETED_BATCHES=COMPLETED_BATCHES+1, END_DTTM=getDate() where PROCESS_RUN_ID=?). This way we can keep track of number of beans that has finished processing. if total number of batches == completed, we know the processing has finished and we can notify the user.

Problem: This solution works fine with low volumes, with higher volume I have seen that completed batches is always less than total batches even if all the batches successful.

scenario: total batches = 2, completed = 0;
if two threads execute the update SQL at the same time, and they refer to same tuple in the database. both of these threads will have different objects (java identity) because they are in different hibernate session. Therefore both will have PROCESS_RUN object with completed batches = 0, and both will end up doing completed batches = completed batches + 1 = 1 and persist. therefore, though the completed batches should be 2, it ends up in 1.

Select for update (lockmode.upgrade) din't help either, and it effects the performance as well.

I will appreciate any suggestions.