Thread: Row locking within a SELECT statement

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?

On 2016-08-16 08:19, xrg@linux.gr wrote:
> 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).

Yeah, but FOR UPDATEs are not ordered either unless you use ORDER BY,
which you didn't.


.m

Στις Τρι 16 Αυγ 2016 11:23:59 π.μ. Marko Tiikkaja έγραψε:
> On 2016-08-16 08:19, xrg@linux.gr wrote:
> > 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).
>
> Yeah, but FOR UPDATEs are not ordered either unless you use ORDER BY,
> which you didn't.

So, the case is that these two statements:
    SELECT id FROM alerts ORDER BY id FOR UPDATE;
and
    SELECT id FROM alerts ORDER BY id DESC FOR UPDATE;
are guarranteed to deadlock [1], right?

Well, in my opinion, this anti-pattern deserves to be documented.

Thank you for the quick response.

[1] because each of those statements will begin locking rows, one at a time,
and then reach the ones of the other statement and wait.

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