Re: foreign key locks, 2nd attempt

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.

--
Álvaro Herrera <alvherre(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support