From: groups@karsten-schmidt.com (Karsten Schmidt) Newsgroups: comp.databases.oracle.server Subject: Re: Ora-00060 Deadlock Problem. Select for update behavior. Date: 6 Jun 2001 01:51:48 -0700 Organization: http://groups.google.com/ Lines: 146 Message-ID: References: <3B1B1564.E57EFAAE@ix.netcom.com> <3B1D662E.933DC1F2@sun.com> NNTP-Posting-Host: 212.111.232.146 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit X-Trace: posting.google.com 991817508 16490 127.0.0.1 (6 Jun 2001 08:51:48 GMT) X-Complaints-To: groups-support@google.com NNTP-Posting-Date: 6 Jun 2001 08:51:48 GMT 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 wrote in message news:<3B1D662E.933DC1F2@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" wrote in message > > news:3B1B1564.E57EFAAE@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 > > > > > >