Ora-00060 Deadlock Problem. Select for update behavior.

We are using oracle 8.1.6.3 on Solaris 2.6 using JDBC. We have a table in which different users are inserting. This table is used to process the inserted records and delete them once processed. We have a process which issues "select for update with order by pk which is a sequence column" on these tables, and deletes the records from the result set and commits at the end. When multiple instances of these processes are run from different sessions, my understanding is that the sessions with select for update should wait till the current session which holds the lock on active set is committed.

The problem is once that oracle issues ora-60 deadlock detected and rollsback one of the select for update statements.

From the trace files it says that process A(session A) acquired X lock on record 1 and process B(session B) acquired X lock on record 2. Then process A is waiting on record 2 for X lock, and Process B trying to acquire X lock on Record 1. Since these processes are using select for update shouldn't the process B wait till Process A is commited before deleting the records held by Process A.

Our intention is to avoid deadlock by issuing select for update. Any ideas as to why oracle is detecting a deadlock. There are no FKs, and we are using dedicated connections.

TIA,
Madhu Received on Sun Jun 03 2001 - 23:58:12 CDT