Hi,

My 1st post so hoping to get a +ve reply as I have been Googling the whole day trying to solve this issue.

My Setup:
I have a table that is worked on by 3 apps:
1. App1: Inserts records into the table.
2. App2: Selects new data from the table on the basis of a status flag (=1) and updates this flag (=2) in the same transaction.
3. App3: Another instance of App2 doing exactly the same thing!

All these apps are running in different servers / JVMs. I am using Hibernate 3.2.4 and MySQL 5.0.27.

My issue:
I want to configure this so that:
1. App1 and App2 doesnt end up selecting the same records from the table. Scenario: App1 selects 20 records of status = 1, App2 selects the same 20 records before App1 updates the status flag as their status is still 1.
2. App1 works with a set of records and App2 works with a different set of records if newer records with status = 1 are present else sleep, thus the need for record level locking.
3. App3 doesnt get blocked in its inserts.

I tried using LockMode.UPGRADE but seems like App1 and App2 wait for each other to unlock the table (isnt this supposed to be record level locking?) and sometimes falling over giving a 'Lock wait timeout exceeded; try restarting transaction'. Also, with this App3 is unable to insert as well!!

Am I doing anything wrong? Is my interpretation of LockMode.UPGRADE completely incorrect? Plz do explain where I am going wrong! Hope my explaination is understandable ;)

Regards,
Sumit

What you really need is a Pessimistic Offline Lock http://martinfowler.com/eaaCatalog/pessimisticOfflineLock.html
Try googling it for more information.
The LockMode.UPGRADE does not lock the record across transactions. As soon as the transaction ends the Lock is released.
So I do not think this will help you!
I had a similiar functionlaity to implement and I used the following approach.

1. Create a Lock table in the DB with the columns:

* Locked_Resource_Id (can be used as the primary key)

* Owner

* Created_Timestamp

2. Create a LockManager class and implement getLock(Id_to_Lock)

3. Create a Lock DAO class (for the LockManager) to manage the Lock Table (using Hibernate if you wish)

All your getRecords() calls should be routed through the LockManager. If you encounter a record which is locked (Id present in the Lock Table) throw an exception.
Once the record is updated, release the lock by deleting the lock table entry.
In this way you can prohibit concurrent reads.
If you do not want an Application to Lock the records endlessly you may include a logic to override a lock based on the time it was created.
Hope this helps you.

Hi mazhar,

Thank you for your response.

Yes, one of the work-arounds in my head was to implement something that you explained in your 3 steps but I actually wanted to see if there is some feature within Hibernate or MySQL that I might be able to use, ideally getting locks on selected rows rather than the whole table so that selects from transactions dont find these already selected rows and only work on rows that the particular transaction locked while also letting the inserts to occur without any waiting for unlocking. My Queries will be like:
1. BEGIN TRANSACTION
2. SELECT * FROM object WHERE status = 1;
3. UPDATE object SET status = 2 WHERE id IN (result from 1.)
4. COMMIT TRANSACTION

So I need a lock on all records selected by query 2 until commit so that another transaction executing 2 doesnt select these records. Also, allowing inserts without waiting is a very important requirement.

Just trying to be as explainatory here as possible incase someone is aware of a better solution to achieve this.

Also, can you also plz explain what exactly LockMode.UPGRADE do?

Regards,
Sumit

LockMode.Upgrade bypass all the caches, does a version check and acquires a pessimistic lock. If the there is a lock already in place it waits.

Hi jravindra,

Thanks for your explaination. Short and simple! :) Surely explains why App2 was waiting for App1 to finish before continuing.

So I am guessing there's no inbuilt feature in Hibernate (or MySQL) to achieve what I 'tried' to explain above! Anyone done anything like this before? Plz do let me know if you have any ideas on how to implement the same. Currently, the only way out that I can think of (& suggested here) is what mazhar explained. Thanks again mazhar.. :)

Regards,
Sumit