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

Hi,
 from your code fragment, it looks like you are always processing the entire table.  (you don't have a predicate...)
 If this is the case, it it feasible to do a 'lock table' at the very beginning ?  That would seriously reduce concurrency since all the clients are going to queue  on the table lock. but, maybe this is a shortterm workaround.

Karsten

Madhu Konda <madhu.konda_at_sun.com> wrote in message news:<3B1D662E.933DC1F2_at_sun.com>...
> 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 Wed Jun 06 2001 - 03:51:48 CDT