Hi,

My problem is that if I try to update more than one row in a table like
> UPDATE mytable SET something = 84 WHERE not_unique_col = 41;
in two concurrent transactions, it can result in a deadlock if the two
UPDATEs visit the rows in a different order.
The same applies, if I try to
> SELECT * FROM mytable WHERE not_unique_col = 41 FOR UPDATE;

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)?

I'm not quite sure (though I tested it on a small table and it looked
fine), because I (or should I say Google) could not find even one page
on postgresql.org where this row-level deadlock situation had been
solved... I could only find Tom Lane's post, where he admitted that this
can lead to a deadlock:
http://archives.postgresql.org/pgsql...1/msg01372.php
I don't believe that no one thought of this solution before, so there
must be something wrong with it...

Regards,
Panther

PS: Sorry if this will be a double-post, but it looks like my previous mail
was lost somewhere...

__________________________________________________ _____________
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...5798/click.prm



---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

=?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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Daniel Denes <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


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...5798/click.prm



---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings