Re: SELECT FOR UPDATE

Bruce Momjian wrote:

>Thomas Swan wrote:
>
>
>>When a SELECT FOR UPDATE query is executed, are the row level locks on a
>>table acquired in any specific order such as to enhance deadlock
>>prevention? ( primary key, oid, etc. )
>>
>>
>
>Interesting question, because in a join, you could have multiple tables
>involved. Sorry, I don't know the answer.
>
>
>
I had remembered several readings on ordered locking as a method to
prevent deadlocks, and associated that with select for update
methodology. In theory if you aquired locks in the following order, for
each table/relation (in oid order) get rows/tuples (in oid order), you
could help avoid deadlock by never gaining a lock ahead of someone
else. Locks could be released in the same order. The system should be
predictable even with oid wrap arounds.

I'm quite sure that someone has done something like this for postgres
though....

Perhaps table/row oids are a good idea?