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

>-----Original Message-----
>From: pgsql-general-owner(at)postgresql(dot)org
>[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Panther
>Sent: dinsdag 30 januari 2007 7:07
>To: pgsql-general(at)postgresql(dot)org
>Subject: [GENERAL] 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... :)

I'm wondering: when is the lock actually acquired?
My guess would be (just) before its actually accessed, not after the
result was fetched.

It would probably depend on the selection criteria. Since it could be
that the planner takes the primary_key index scan on some cases, and a
non_unique index scan (or table scan with sort) in other cases.
That's what I would do, choose the index for not_unique_col and sort.
If that's not possible, use the primary key index.
Perhaps the query optimizer is a little bit more clever.

Wouldn't it be safer to disable index usage instead, forcing table
scans? That seems easier to force the database software to use the same
ordering within a query.

You cannot prove that this will always work...
However it MIGHT help in practice, but you cannot be sure...

- Joris