Re: Row locking within a SELECT statement

xrg(at)linux(dot)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