Thread: 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