Re: foreign key locks, 2nd attempt

On Tue, Mar 6, 2012 at 4:27 PM, Alvaro Herrera
<alvherre(at)commandprompt(dot)com> wrote:
> Excerpts from Robert Haas's message of mar mar 06 18:10:16 -0300 2012:
>>
>> Preliminary comment:
>>
>> This README is very helpful.
>
> Thanks.  I feel silly that I didn't write it earlier.
>
>> On Tue, Mar 6, 2012 at 2:39 PM, Alvaro Herrera
>> <alvherre(at)commandprompt(dot)com> wrote:
>> > We provide four levels of tuple locking strength: SELECT FOR KEY UPDATE is
>> > super-exclusive locking (used to delete tuples and more generally to update
>> > tuples modifying the values of the columns that make up the key of the tuple);
>> > SELECT FOR UPDATE is a standards-compliant exclusive lock; SELECT FOR SHARE
>> > implements shared locks; and finally SELECT FOR KEY SHARE is a super-weak mode
>> > that does not conflict with exclusive mode, but conflicts with SELECT FOR KEY
>> > UPDATE.  This last mode implements a mode just strong enough to implement RI
>> > checks, i.e. it ensures that tuples do not go away from under a check, without
>> > blocking when some other transaction that want to update the tuple without
>> > changing its key.
>>
>> I feel like there is a naming problem here.  The semantics that have
>> always been associated with SELECT FOR UPDATE are now attached to
>> SELECT FOR KEY UPDATE; and SELECT FOR UPDATE itself has been weakened.
>>  I think users will be surprised to find that SELECT FOR UPDATE
>> doesn't block all concurrent updates.
>
> I'm not sure why you say that.  Certainly SELECT FOR UPDATE continues to
> block all updates.  It continues to block SELECT FOR SHARE as well.
> The things that it doesn't block are the new SELECT FOR KEY SHARE locks;
> since those didn't exist before, it doesn't seem correct to consider
> that SELECT FOR UPDATE changed in any way.
>
> The main difference in the UPDATE behavior is that an UPDATE is regarded
> as though it might acquire two different lock modes -- it either
> acquires SELECT FOR KEY UPDATE if the key is modified, or SELECT FOR
> UPDATE if not.  Since SELECT FOR KEY UPDATE didn't exist before, we can
> consider that previous to this patch, what UPDATE did was always acquire
> a lock of strength SELECT FOR UPDATE.  So UPDATE also hasn't been
> weakened.

Ah, I see. My mistake.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company