Path: csiph.com!x330-a1.tempe.blueboxinc.net!usenet.pasdenom.info!gegeweb.org!de-l.enfer-du-nord.net!feeder1.enfer-du-nord.net!feeder.erje.net!newsfeed.utanet.at!newscore.univie.ac.at!aconews-feed.univie.ac.at!aconews.univie.ac.at!not-for-mail From: "Laurenz Albe" Newsgroups: comp.databases.postgresql References: Subject: Re: Deadlock on the same select for update Date: Tue, 3 May 2011 09:53:28 +0200 X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2900.5931 X-RFC2646: Format=Flowed; Original X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.6090 Organization: dienste.wien.at ISP Message-ID: <1304409229.248884@proxy.dienste.wien.at> X-Cache: nntpcache 2.3.3 (see http://www.nntpcache.org/) Lines: 25 NNTP-Posting-Host: 141.203.254.23 X-Trace: 1304409230 aconews.univie.ac.at 11354 141.203.254.23 X-Complaints-To: abuse@univie.ac.at Xref: x330-a1.tempe.blueboxinc.net comp.databases.postgresql:109 Matthew Woodcraft wrote: > What I don't see a firm guarantee for is that ORDER BY on a SELECT FOR > UPDATE controls the order in which the locks are taken, as opposed to > just controlling the order of the result rows. There's nothing in the documentation, but read Tom Lane's commit message for a change introduced in 9.0: http://archives.postgresql.org/pgsql-committers/2009-10/msg00127.php Instead, keep the present semantics of applying FOR UPDATE after ORDER BY within a single query level; but allow the user to specify the other way by writing FOR UPDATE in a sub-select. So the locks will be taken in the order specified in ORDER BY in a simple query, but a) that is not a documented feature and b) the commit message suggests that that is not written in stone. So I wouldn't rely on it. Yours, Laurenz Albe