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
Date: Tue, 16 Aug 2016 11:44:53 -0400
Message-ID: <12784.1471362293@sss.pgh.pa.us> (raw)
In-Reply-To: <20160816061952.30234.45751@wrigleys.postgresql.org>
References: <20160816061952.30234.45751@wrigleys.postgresql.org>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-docs>
xrg@linux.gr writes:
> In order to avoid concurrent manipulation of rows, I do issue a "SELECT ..
> FOR UPDATE" on the sets of rows, before UPDATEing them (because UPDATEs
> cannot be ordered).
> transaction A {
> SELECT .. FROM alerts WHERE <clauseA> FOR UPDATE;
> ... decide ...
> UPDATE alerts ...
> }
> transaction B {
> SELECT .. FROM alerts WHERE <clauseB> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-docs@postgresql.org
Cc: tgl@sss.pgh.pa.us, xrg@linux.gr
Subject: Re: Row locking within a SELECT statement
In-Reply-To: <12784.1471362293@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox