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. )


Thu, 24 Jul 2003 20:17:57 -0500
Thomas Swan <tswan@idigx.com>
SELECT FOR UPDATE
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.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Thu, 24 Jul 2003 21:23:25 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
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?



Thu, 24 Jul 2003 20:39:03 -0500
Thomas Swan <tswan@idigx.com>
Re: SELECT FOR UPDATE
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.

			regards, tom lane

Fri, 25 Jul 2003 00:10:15 -0400
Tom Lane <tgl@sss.pgh.pa.us>
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.


Fri, 25 Jul 2003 02:53:47 -0500
Thomas Swan <tswan@idigx.com>
Re: SELECT FOR UPDATE
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.

			regards, tom lane

Fri, 25 Jul 2003 09:10:51 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: SELECT FOR UPDATE 
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.


Fri, 25 Jul 2003 17:24:20 -0500
Thomas Swan <tswan@idigx.com>
Re: SELECT FOR UPDATE