Re: improving foreign key locks

On Nov25, 2010, at 23:01 , Alvaro Herrera wrote:
> So I've been working on improving locks for foreign key checks, as
> discussed in a thread started by Joel Jacobson a while ago. I've posted
> about this:
> http://www.commandprompt.com/blogs/alvaro_herrera/2010/11/fixing_foreign_key_deadlocks/
> http://www.commandprompt.com/blogs/alvaro_herrera/2010/11/fixing_foreign_key_deadlocks_part_2/

To me, the whole thing seems to be special case of allowing to not only lock whole tuples FOR UPDATE or FOR SHARE, but also individual fields or sets of fields. Except that for simplicity, only two sets are supported, which are
A) All fields
B) All fields which are included in some unique constraint, including primary keys.

I'd therefore suggest to extend the FOR SHARE / FOR UPDATE syntax to be
SELECT FOR { SHARE | UPDATE } [ OF <table1>[.<field1>], ... ]
and obtain what you call a "KEY LOCK" if (for a particular table) the set of fields is a subset of (B). Otherwise, we'd obtain a full SHARE lock. Thus we'd always lock at least the fields the user told us to, but sometimes more than those, for the sake of a more efficient implementation.

This leads quite naturally to the following behaviour regarding lock conflicts
.) An UPDATE conflicts with a SHARE or UPDATE lock if the update touches fields locked by the SHARE or UPDATE lock. Thus, in case (A) they always conflict while in case (B) they only conflict if the UPDATE touches a field contained in some unique index
.) A DELETE conflicts always conflicts with a SHARE or UPDATE lock since it, in a way, touches all fields
.) A UPDATE lock always conflicts with a SHARE lock, since there are no two non-overlapping sets of fields that we could lock.
.) SHARE locks never conflict with one another.

best regards,
Florian Pflug