On 7/25/2003 8:10 AM, Tom Lane wrote:
>Thomas Swan <tswan@idigx.com> writes:
>
>
>>Hypothetically, if I knew the relations that contained the tuples I was
>>locking, I could conceivably visit them in, let's say, alphabetical
>>order or oid order or any predictable order, and then select the rows
>>for update (using oid order or primary key order). It would be hard to
>>induce a deadlock condition if the locks being acquired where in a
>>consistently reproducible order by all processes.
>>
>>
>
>Given that this requires programmer discipline anyway, I think it's okay
>to leave the whole thing in the hands of the programmer. My original
>comment that the locks are acquired in an unpredictable order wasn't
>right --- they are acquired by the executor's top level, therefore
>in the order the rows come out of the execution engine, and therefore
>you can make the order predictable if you want to. Just use ORDER BY.
>
What I'm referring to is a single select for update statement (could be
a join, a single table, etc.) If the programmer is going to do their
locks on multiple tables in several steps then that's the programmers
responsibility.
If OIDs are unique per database then sort by them. If OIDs are unique
per table and each table has a unique OID then use a hash of the two
values, sort and then acquire. This would prevent two single queries
from resulting in deadlock mid-execution.
