Re: SELECT FOR UPDATE with ORDER BY to avoid row-level deadlock?

=?ISO-8859-2?Q?D=E1niel_D=E9nes?= <panther-d@freemail.hu> writes:
> But what if I try like
>> SELECT * FROM mytable
>> WHERE not_unique_col = 41 ORDER BY pri_key ASC FOR UPDATE;
> and do the UPDATE after this? It should never lead to a deadlock,
> assuming the rows selected FOR UPDATE are locked in the order as
> they are returned.
> But is that true? Are the rows selected FOR UPDATE locked in the same
> order as they are returned (as specified in ORDER BY)?

Should be all right --- the FOR UPDATE locking is always the last step
in the SELECT pipeline.  There's been some talk of pushing it down below
a Limit step if any, to get rid of the rather unfortunate interaction of
those two options ... but I don't see that we'd ever consider pushing it
below a Sort.

            regards, tom lane