I'm trying to see whether I can use database lock to deal with race conditions. For example

CREATE TABLE T1
(
T1_ID NUMBER PRIMARY KEY,
AMT NUMBER,
STATUS CHAR(1),
UPDATED_BY VARCHAR(25)
);

insert into T1 values (t1_seq.nextval, 1, 'N', 'N', 'U0');

Later two users can update the T1 record at the same time. Requirement is that only one can proceed while the other should NOT. We can certainly use a distributed lock manager (DLM) to do this but I figure database lock may be more efficient.

User 1:
update T1 set status='Y', updated_by='U1' where status='N';

User 2:
update T1 set status='Y', updated_by='U2' where status='N';

Two users are doing these at the same time. Ideally only one should be allowed to proceed. I played using Sql Plus and also wrote a little java test program letting two threads do these simultaneously. I got the same result. Let's say User 1 got the DB row lock first. It returns 1 row updated. The second session will be blocked waiting for the row lock before the 1st session commits or rollbacks. The question is REALLY below.

Update statement with a where clause seems like two operations: first it will do an implicit select based on the where clause to identify the row that will be updated. Since Oracle does not supports READ UNCOMMITTED (dirty read) isolation level, I expect both UPDATE statements will pick the single record in the DB. As a result, I expected both UPDATE statement will eventually return "1 row updated" although one will wait till the other transaction commits. HOWEVER that's not what I saw. The second UPDATE returns "0 row updated" after the first commits. I feel that Oracle actually runs the where clause AGAIN after the first session commits, picking up the update made by the first transaction, which results in "0 row updated" result.

This is strange to me. I thought I would run into the classical "lost update" phenomenon.

can somebody please explain what's going on here? Thanks very much!