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

Spencer:

Thanks for your reply. The developers do not want to code no wait because the program would have to come back anyway since it is run every 5 seconds. They would rather want it to wait.

I agree with the way oracle locks but this is what we are doing. Do you see any problem here. Our program logic is to 1. select * from tableA order by message_id_seq for update; 2. start loop.. Process the result set and do your stuff and delete the record... end loop...
3.. close jdbc statement
4. commit

Our design is Each JVM is running the above process every 5 seconds and we have 4 JVMs. This translates to running the process every 5 seconds in 4 oracle sessions lets say sessoin A, B, C, D. I know the potential for deadlock, but statement1 above should make the B, C, and D wait while Session A has lock on the record which is ordered by PK sequence. So if A has locked all records (say 5 records), the B,C and D should wait. When A finishes and commits it has removed those 5 records. Meanwhile if there are other commited inserts (say 10 more) then B should pick up, select for update lock and process where as C, D and A are waiting if A is back after 5 seconds. So at any point we have 4 sessions continuously processing the table.

Do you see a design problem here. Appreciate your help very much. Oracle Customer Support is looking into it. Wanted to see if other DBAs have any insight.

Thanks,
Madhu
Oracle DBA

Spencer wrote:

> 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 Tue Jun 05 2001 - 18:07:26 CDT