Row locking within a SELECT statement

From: xrg(at)linux(dot)gr
To: pgsql-docs(at)postgresql(dot)org
Subject: Row locking within a SELECT statement
Date: 2016-08-16 06:19:52
Message-ID: 20160816061952.30234.45751@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-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?

Responses

Browse pgsql-docs by date

  From Date Subject
Next Message Alexander Law 2016-08-16 07:33:09 Outdated sentence in the pg_am description
Previous Message Barbara Sims 2016-08-13 07:48:30 Broken Link