Re: Row locking within a SELECT statement

Στις Τρι 16 Αυγ 2016 11:23:59 π.μ. Marko Tiikkaja έγραψε:
> On 2016-08-16 08:19, xrg(at)linux(dot)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.