From: Martin Haltmayer Newsgroups: comp.databases.oracle.server Subject: Re: Ora-00060 Deadlock Problem. Select for update behavior. Date: Thu, 07 Jun 2001 02:05:00 +0200 Lines: 27 Message-ID: <3B1EC52C.400055D5@0800-einwahl.de> References: <3B1B1564.E57EFAAE@ix.netcom.com> <3B1DEFEF.FD18DE2@0800-einwahl.de> <3B1EC1A8.6E2C7F6@sun.com> NNTP-Posting-Host: 016.dialin.augsburg.net Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Trace: news.augsburg.net 991872548 1849 195.222.98.16 (7 Jun 2001 00:09:08 GMT) X-Complaints-To: usenet@news.augsburg.net NNTP-Posting-Date: Thu, 7 Jun 2001 00:09:08 +0000 (UTC) To: Madhu Konda Disposition-Notification-To: Martin Haltmayer X-Mailer: Mozilla 4.77 [en] (WinNT; U) X-Accept-Language: en,de-DE Comments inline. Madhu Konda wrote: > > ... > > 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. Yes, you are not totally correct. B may not *finish* until A commits. But it may *start* its work. And if both start the sequence they proceed is undefined. If the processing sequence of the rows were always the same no deadlock would occur but one process B would wait for A or vice versa. > > ... > > 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? Yes, the share lock will not keep the second process B from starting. Try with "delete ... where rownum <= 1". Martin