SELECT FOR UPDATE with ORDER BY to avoid row-level deadlock?
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-general/2004-11/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,135082,205798/click.prm
=?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
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,135082,205798/click.prm