From: Martin Haltmayer Newsgroups: comp.databases.oracle.server Subject: Re: Ora-00060 Deadlock Problem. Select for update behavior. Date: Wed, 06 Jun 2001 10:55:11 +0200 Lines: 13 Message-ID: <3B1DEFEF.FD18DE2@0800-einwahl.de> References: <3B1B1564.E57EFAAE@ix.netcom.com> NNTP-Posting-Host: f-235-252.munchen.ipgprs.viaginterkom.de Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Trace: news.augsburg.net 991817977 7903 62.180.235.252 (6 Jun 2001 08:59:37 GMT) X-Complaints-To: usenet@news.augsburg.net NNTP-Posting-Date: Wed, 6 Jun 2001 08:59:37 +0000 (UTC) To: datasyst Disposition-Notification-To: Martin Haltmayer X-Mailer: Mozilla 4.77 [en] (WinNT; U) X-Accept-Language: en,de-DE 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. 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". Martin