Re: Ora-00060 Deadlock Problem. Select for update behavior.

Hi!

I think it is because although T3 is the time that you _issue_ your statement, the database finds that it cannot execute it because rows in the potential resultset are locked. The execution is therefore postponed until no rows in the potential resultsset are locked. The resultset is not constructed in sessionB by T3 because the database is not able to execute statement.
At T5 the locked are removed from the potential resultset for SessionB and the statement performs; it now builds the resultset based on all currently commited transaction in the database by T5 (that is eg. T3 and T4). Only id=4 is visible then.

In short: the time the statement in SessionB builds its resultset is T5 and not T3.
Im not sure what you mean by "deadlock"; this is a lock that is ok because the logic we run are able to perform because we commit when appropriate. A deadlock, occures when two sessions asks for one resource each, and they "coinsidally" must wait for eachothers commit(rollback) to get it. Something along the lines of:

SessionA:T1
UPDATE table
SET col=lkjlkj
WHERE ID=7 SessionB:T2
UPDATE table
SET col=lkjlkj
WHERE ID=8 SessionA:T3
UPDATE table
SET col=lkjlkj
WHERE ID=8 SessionB:T4
UPDATE table
SET col=lkjlkj
WHERE ID=7 After T3 SessionA waits for SessionB (because of T2), and after T4 SessionB waits for SessionA (because of T1).
Frank Received on Mon Jun 11 2001 - 10:43:40 CDT