Re: SELECT FOR UPDATE

Tom Lane wrote:

>Thomas Swan <tswan@idigx.com> writes:
>  
>
>>>>When a SELECT FOR UPDATE query is executed, are the row level locks on a 
>>>>table acquired in any specific order 
>>>>        
>>>>
>
>Nope, just whatever order the chosen plan happens to visit the tuples
>in.
>
>  
>
>>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.
>>    
>>
>
>Hmmm .... this would only help for situations where all the locks of
>interest are grabbed in a single scan.  I suppose that has some
>usefulness, but it can hardly be said to eliminate deadlocks.  I kinda
>doubt it's worth the trouble.
>
If you sort the locks before acquiring them, then you could prevent a 
deadlock conditions.  Proper planning from the programmer can help to 
alleviate most of the rest.

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. 

Perhaps it's just an academic discussion, but I think it could work in 
terms of performance.  I'm just not sure how much work is necessary to 
sort the locks prior to acquiring them.