From: Madhu Konda Newsgroups: comp.databases.oracle.server Subject: Re: Ora-00060 Deadlock Problem. Select for update behavior. Date: Wed, 06 Jun 2001 16:50:00 -0700 Organization: Netscape Communications Corporation Lines: 34 Message-ID: <3B1EC1A8.6E2C7F6@sun.com> References: <3B1B1564.E57EFAAE@ix.netcom.com> <3B1DEFEF.FD18DE2@0800-einwahl.de> NNTP-Posting-Host: 192.18.151.174 Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Mailer: Mozilla 4.7 [en]C-AOLNSCP (WinNT; U) X-Accept-Language: en To: Martin Haltmayer Thanks for your response. I want to make sure I understand you correctly. see my responses below. Martin Haltmayer wrote: > The crucial point is that "select ... for update" always locks the *entire* > resultset. After retrieving all result rows Oracle tries to lock, then it sorts > them. > > The sequence in which they are retrieved is undefined. So you cannot assume that > process A and process B try to retrieve and to lock their respective rows in the > same order. Therefore a deadlock may occur. > When using "select ... for update" should not processB wait till processA commits. So technically, they should not see the same rows at all because there is only one process working on the table at any time. Please correct me if I am wrong. > > Easiest solution is to issue a "lock table in exclusive mode", then delete > (without selecting for update), then commit. Another option would be to limit > the resultset to one row by the condition "and rownum <= 1". > Since I cannot issue exclusive lock because inserts are happening, I can issue "lock table in row share mode" which is the same lock as select .. for update. Is there a difference in one vs the other? > > Martin Thanks Madhu