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

From: Spencer <spencerp_at_swbell.net>
Date: Mon, 4 Jun 2001 22:55:52 -0500
Message-ID: <lJYS6.125$j77.210856@nnrp2.sbc.net>

if session A is "waiting" for a resource locked by session B, and session B is "waiting" for a resource locked by session A...

session A continues to wait. session B continues to wait. both sessions continue to wait until one of the sessions release its locks. when both sessions continue to wait forever, this is called a "deadlock" condition.

oracle (fortunately) is detecting that both session A and session B are waiting on resources locked by the other, and terminates one of the sessions, which releases the locks, which then allows at least one of sessions to continue. this is a good thing.

if you need to run more than one instance of this process at the same time (i question why you want to do that in the first place) and oracle "deadlock" detection is causing you a problem, then i suggest that you need to re-design the process.

one design option would be for each session to open a read only cursor (i.e. done that oes not obtain exclusive row locks), fetch a row from the read only cursor, and attempt to obtain an exclusive row lock by opening a second cursor FOR UPDATE NOWAIT. and fetching the row.

if a lock is not obtained immediately, oracle raises the ORA-00054 "resource busy NOWAIT specified" exception. if a lock is obtained, then delete the row, close the "lock" cursor, and fetch another row from the "read only" cursor and continue.

here is a PL/SQL example:

DECLARE
  CURSOR lcsr_read IS
  SELECT t.ROWID, t.PK
    FROM MYTABLE t
   ORDER BY t.PK ;
  lrec_read lcsr_read%ROWRTYPE;
   CURSOR lcsr_lock IS
  SELECT t.ROWID
    FROM MYTABLE t
   WHERE t.ROWID = lrec_read.ROWID
     FOR UPDATE NOWAIT;
BEGIN
  OPEN lcsr_read;
  FETCH lcsr_read INTO lrec_read;
  LOOP
    EXIT WHEN lcsr_read%NOTFOUND;
    BEGIN

      OPEN lcsr_lock;
      FETCH lcsr_lock INTO lrec_lock;
      DELETE FROM MYTABLE d
       WHERE d.ROWID = lrec_lock.ROWID;
      CLOSE lcsr_lock;
    EXCEPTION
      WHEN OTHERS THEN
        IF lcsr_lock%ISOPEN THEN
          CLOSE lcsr_lock;
        END IF;

    END;
  END LOOP;
  CLOSE lcsr_read;
  COMMIT;
END; the exception handler can be modified to specifically catch the ORA-00054 exception, rather than catching it with "OTHERS"...

HTH "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
>
>
Received on Mon Jun 04 2001 - 22:55:52 CDT