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

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Daniel Denes <panther-d(at)freemail(dot)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

Yeah, I read that FOR UPDATE + LIMIT problem too (in the manual and
on the lists), but fortunately I don't have anything to do with that. By
the way, should not the manual have some information regarding this
question I asked? I think it would be useful.
And if this is the solution to row-level deadlocks caused by different
row visiting orders, how did no one think of this before? :)

Regards,
Denes Daniel

_______________________________________________________________
Ne csak a lakást nézze, hanem a környéket is! Válogasson több
ezer ingatlanból légifotós-kereső segítségével!
http://ad.adverticum.net/b/cl,1,6022,135082,205798/click.prm