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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
