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.

Answer 1

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.