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

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 indexentries, the same as if you issued an UPDATE statement for those rows.Other transactions are blocked from updating those rows, from doingSELECT ... LOCK IN SHARE MODE, or from reading the data in certaintransaction isolation levels. Consistent reads ignore any locks set onthe records that exist in the read view. (Old versions of a recordcannot be locked; they are reconstructed by applying undo logs on anin-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 willcreate a copy of the block containing this row as it existed when thequery began ... Effectively, Oracle Database takes a detour around themodified data—it reads around it, reconstructing it from the undo(also known as a rollback ) segment. A consistent and correct answercomes back without waiting for the transaction to commit.