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
___________________________________________________________
Új év - új állás? Mérnöki, értékesítői, asszisztensi,
pénzügyi és IT állások a Jobline.hu-n!
http://ad.adverticum.net/b/cl,1,6022,135079,205796/click.prm
