How to avoid two different threads read the same rows from DB (Hibernate and Oracle 10g)

Question

Let's suppose I got two different threads, T1 and T2, accessing concurrently the same database and fetching data from the same table.

Now on thread startup I need to fetch data from the table and store the rows into a collection, that I will then use to perform some work elsewhere. I don't want the two threads to be able to process the same data, because it will result in duplicated (and long) work. To be more concrete, this is an enterprise application that needs to load some records at startup and store it in a collection to do some extra work. The problem is that in a clustered environment, this can cause two different instances to load the same data and so the work could be duplicated. So I want the rows to be loaded only once by a single instance.

How can I avoid that scenario?

I'm currently using Hibernate and Oracle 10g. These are my solutions up to now:

Lock the row programmatically. The first one that reads it sets some "locked" column to true, but deadlock is very likely to occur if the first thread dies without setting the row as "processed".
Using Pessimistic Locking. I tried with LockMode.UPGRADE but that doesn't seem to help, as I'm still able to read the data from both thread at the same time.

public List<MyObject> getAllNtfFromDb() {
      Session session = HibernateUtil.getOraclesessionfactory().openSession();
      Query q = session.createQuery(
              "from MyObject n where n.state = 'NEW'");
    List<MyObject> list = (List<MyObject>) q.list();
      for (int i=0; i<list.size(); i++)
          session.lock(list.get(i), LockMode.UPGRADE);
return list;
}

Any other hints? What am I doing wrong?

Thanks.

Can you please provide the Hibernate imports used for this line: Session session = HibernateUtil.getOraclesessionfactory().openSession(); — Orby
– Orby, Commented May 9, 2018 at 13:07

Vlad Mihalcea · Accepted Answer · 2021-01-09 17:01:46Z

9

You need to use PESSIMISTIC_WRITE at query time:

Query q = session
    .createQuery("from MyObject n where n.state = 'NEW'")
    .setLockOptions(new LockOptions(LockMode.PESSIMISTIC_WRITE));
List<MyObject> list = (List<MyObject>) q.list();

Locking the parent objects is sufficient. Deadlocks won't necessarily occur. You might get a lock acquisition failure if the thread holding the lock doesn't release it prior to another thread from timing out waiting.

Since you are using Oracle, this is how SELECT FOR UPDATE works:

SELECT ... FOR UPDATE locks the rows and any associated index entries, the same as if you issued an UPDATE statement for those rows. Other transactions are blocked from updating those rows, from doing SELECT ... LOCK IN SHARE MODE, or from reading the data in certain transaction isolation levels. Consistent reads ignore any locks set on the records that exist in the read view. (Old versions of a record cannot be locked; they are reconstructed by applying undo logs on an in-memory copy of the record.)

So if T1 acquired an exclusive lock on some rows, T2 won't be able to read those records until T1 commits or roll-backs. If T2 used a READ_UNCOMMITTED isolation level, then T2 won't ever block on lock records, as it simply uses undo logs to reconstruct data as if it were when the query began. As opposed to the SQL standard, the Oracle READ_UNCOMMITTED will:

To provide a consistent, or correct, answer, Oracle Database will create a copy of the block containing this row as it existed when the query began ... Effectively, Oracle Database takes a detour around the modified data—it reads around it, reconstructing it from the undo (also known as a rollback ) segment. A consistent and correct answer comes back without waiting for the transaction to commit.

edited Jan 9, 2021 at 17:01

answered Jan 15, 2015 at 22:17

Vlad Mihalcea

156k85 gold badges599 silver badges984 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user2799534 Over a year ago

Ok thanks, that does work but only if the two threads are trying to read the same data very close in time. If T1 reads the data and after 1 minute T2 reads the same data (which T1 is very likely to be still processing), then T2 is able to see the rows. I want to lock the row until they are updated (e.g. the state column set to "OK", or "PROCESSED"). Also, does updating one entity automatically release the row? And if the session is closed or the thread crashes are the rows released after some timeout? Thank you very much.

Vlad Mihalcea Over a year ago

It probably depends on the underlying database exclusive lock support.

Collectives™ on Stack Overflow

How to avoid two different threads read the same rows from DB (Hibernate and Oracle 10g)

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related