Hi,
In my application, I am processing the records in batches. For that I have created a batch table which contains 3 columns viz. Batch_Id, Processing_Time and Status. I have multiple instances of my application.

Processing_Time field is kept to find the scenarios where a batch processing might have started earlier but due to some condition, application is crashed mid-way. So if a record in batch table has status=2 and processing time earlier than 10 minutes, I can safely assume that it's a failed batch and need to reprocess. After successful batch processing, status is moved to 4.

My select query for batch id has where clause as status=2 and Processing_Time is earlier than 10 minutes. Now as told earlier, due to multiple installations, multiple threads are picking up the same batch id though I have written SELECT for UPDATE query and immediately updating the Processing_Time of least batch id to current time. I am using Hibernate native query i.e. session.createSQLQuery().

How can I make one thread to wait using Hibernate till my SELECT FOR Update + Update of one thread gets over so that when second thread starts, it will pick up the next record? Currently what is happening is that Query.uniquResult() returns the same object/row to both the threads and not locking the db? I have also tried to see how can use lock() of session or Query. Session.lock is applicable on object itself so which I don't have before executing query so it is of no use. Can I use Query.lock()? But then what should I specify as "alias" as I am using native query?

Appreciate your help :)

Nilesh Soni