To avoid two different threads, read the same rows from the database (Hibernate and Oracle 10g)

Question

Suppose I have two different streams: T1 and T2, simultaneously receiving the same database and fetching data from the same table.

Now when I start the stream, I need to extract the data from the table and save the rows in the collection, which I will use to do some work elsewhere. I do not want these two threads to be able to process the same data, because this will lead to duplication (and long) work. To be more specific, this is an enterprise application that must download some entries at startup and store them in a collection to do extra work. The problem is that in a clustered environment this can lead to two different instances loading the same data, and therefore the work can be duplicated. Therefore, I want the rows to be loaded only once by one instance.

How can I avoid this scenario?

I am currently using Hibernate and Oracle 10g. These are my solutions so far:

• Lock the line programmatically. The first one that reads it sets some “locked” column to true, but a dead end is likely to happen if the first thread dies without setting the row as “processed”.

• Using pessimistic blocking. I tried with LockMode.UPGRADE, but this does not seem to help, since I can still read data from both streams 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 clues? What am I doing wrong?

Thanks.

Answer 1

You need to use PESSIMISTIC_WRITE during the request:

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

It is enough to lock the parent objects. Dead ends don't necessarily happen. You may receive a lock detection failure if the thread holding the lock does not release it before another thread waits.

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

SELECT ... FOR UPDATE blocks rows and any associated record index, just as if you issued an UPDATE statement for those rows. Other transactions are blocked from updating these rows, from executing SELECT ... LOCK IN SHARE MODE, or from reading data at specific transaction isolation levels. Consistent reads ignore any locks that are set on records that exist in read mode. (Older versions of a record cannot be locked; they are restored by applying pending logs to an in-memory copy of the record.)

So, if T1 has acquired an exclusive lock on some lines, T2 will not be able to read these records until T1 completes or rolls back. If T2 used the READ_UNCOMMITTED isolation level , then T2 will never block lock records, as it simply uses undo logs to restore data, as if it were when the request started. Unlike the SQL standard, Oracke READ_UNCOMMITTED will be:

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 started ... Effectively, Oracle Database goes around the changed data - it reads around it, restoring it from cancellation (also known as rollback). A consistent and correct response is returned without waiting for the transaction.