Table level Lock in Hibernate

Question

I use Hibernate version 4. We have a problem in batch process. Our system works as below

1. Select set of records which are in 'PENDING' state
2. Update immediately to 'IN PROGRESS' state
3. Process it and update to 'COMPLETED' state

The problem when we have two servers and executing at same time, we fear of having concurrency issue. So we would like to implement DB Lock for first two steps. We used query.setLockOptions(), but it seems not working. Is there any other to have table level lock or Row level lock till it completes select and update. Both are in same session.

We have options in JDBC that LOCK TABLE <TABLE_NAME> WRITE. But how do we implement in hibernate or is it possible to implement select..for update in hibernate?

Answer 1

"Select ... for update" is supported in Hibernate via LockMode.UPGRADE which you can set in, for example, a NamedQuery.

But using application/manual table-row locking has several drawbacks (especially when a database connection gets broken half-way a transaction) and your update-procedure can do without it:

• Start transaction.
• update table set state='PENDING', server_id=1 where state='IN PROGRESS';
• Commit transaction
• select from table where state='PENDING' and server_id=1;
• [process records]

Each server must have a unique number for this to work, but it will be less error-prone and you let the DBMS do what it is supposed to be good at: isolation (see ACID).