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

datasyst <datasyst_at_ix.netcom.com> wrote in message news:<3B1B1564.E57EFAAE_at_ix.netcom.com>...
> 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

Hi,

  Could u give listing of your process or portion of program which helps me to solve the problem.

Chirag Received on Tue Jun 05 2001 - 00:04:37 CDT