From: Tom Lane <tgl@sss.pgh.pa.us>
To: xrg@linux.gr
cc: pgsql-docs@postgresql.org
Subject: Re: Row locking within a SELECT statement
In-reply-to: <20160816061952.30234.45751@wrigleys.postgresql.org>
References: <20160816061952.30234.45751@wrigleys.postgresql.org>
Comments: In-reply-to xrg@linux.gr
	message dated "Tue, 16 Aug 2016 06:19:52 -0000"
Date: Tue, 16 Aug 2016 11:44:53 -0400
Message-ID: <12784.1471362293@sss.pgh.pa.us>
Precedence: bulk
Sender: pgsql-docs-owner@postgresql.org

xrg@linux.gr writes:
> In order to avoid concurrent manipulation of rows, I do issue a &quot;SELECT ..
> FOR UPDATE&quot; on the sets of rows, before UPDATEing them (because UPDATEs
> cannot be ordered).

> transaction A {
> SELECT .. FROM alerts WHERE &lt;clauseA&gt; FOR UPDATE;
> ... decide ...
> UPDATE alerts ...
> }

> transaction B {
> SELECT .. FROM alerts WHERE &lt;clauseB&gt; FOR UPDATE;
> ... decide, sort, filter ...
> UPDATE / DELETE alerts
> }

> Still, those 2 transactions *do*  deadlock.

It's hard to comment on that without any specifics.  My first guess is
that clauseA and clauseB are sufficiently different that different plan
types are chosen for the two SELECTs, and those plans visit (some of)
the same rows in different orders, leading to deadlock in their row lock
acquisition attempts.  You could probably alleviate that by using
ORDER BY in the SELECTs; but read the "The Locking Clause" section of the
SELECT reference page for some caveats about combining ORDER BY with
FOR UPDATE.

			regards, tom lane


-- 
Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs