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
alsoindividual fields or sets of fields. Except that for simplicity, only two sets are supported, which are A) All
fieldsB) 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
obtaina full SHARE lock. Thus we'd always lock at least the fields the user told us to, but sometimes more than those,
forthe 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
someunique 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
couldlock.
.) SHARE locks never conflict with one another.
best regards,
Florian Pflug