From: xrg@linux.gr
To: pgsql-docs@postgresql.org
Subject: Row locking within a SELECT statement
Date: Tue, 16 Aug 2016 06:19:52 +0000
Message-ID: <20160816061952.30234.45751@wrigleys.postgresql.org> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-docs>
The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/9.4/static/explicit-locking.html
Description:
After experiencing frequent deadlocks, I'd like, please, the docs to clarify
the question/situation:
Are FOR UPDATE locks "atomic" within the SELECT that acquires them, or do
they lock rows "on the go", as they are met in the query results?
Scenario: assume I have an `alerts` table which receives rows from random
sources, and then they are processed by severall passes of stored pl/pgsql
procedures.
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.
Otherwise, should advisory locks be used instead?
--
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: xrg@linux.gr
Subject: Re: Row locking within a SELECT statement
In-Reply-To: <20160816061952.30234.45751@wrigleys.postgresql.org>
* 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