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?