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?
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 |