On May 13, 2010, at 23:39 , Tom Lane wrote:
> Florian Pflug <fgp@phlo.org> writes:
>> All in all, I believe that SHARE and UPDATE row-level locks should be
>> changed to cause concurrent UPDATEs to fail with a serialization
>> error.
>
> I don't see an argument for doing that for FOR SHARE locks, and it
> already happens for FOR UPDATE (at least if the row actually gets
> updated).
Yes, actually updating the row is a workaround. A prohibitively expensive one, though.
The arguments are as stated
a) SHARE or UPDATE locking a concurrently updated row *does* cause as serialization error, making the current behavior
asymmetric
b) Locking primitives usually ensure that once you obtain the lock you see the most recent version of the data. This is
currentlytrue for READ COMMITTED transactions but not for SERIALIZABLE ones, and pretty undesirable a behavior for a
lockingprimitive.
c) I fail to see how the current behavior is useful in the presence of SERIALIZABLE transactions. Currently, they could
IMHOcompletely ignore FOR SHARE locks, without making any previously correct algorithm incorrect.
plus a weaker one:
d) Oracle does it for FOR UPDATE locks, and actually has an example of a FK trigger in PL/SQL in their docs.
> AFAICS this proposal mainly breaks things, in pursuit of
> an unnecessary and probably-impossible-anyway goal of making FK locking
> work with only user-level snapshots.
I don't see the breakage this'd cause. For READ COMMITTED transactions nothing changes. For SERIALIZABLE transactions
thebehavior of FOR UPDATE / FOR SHARE becomes much easier to grasp. In both cases a SHARE lock would then say "Only
updatethis row if you have seen the locking transaction's changes".
Why do you think that making FK locking work with only user-level snapshots is probably-impossible-anyway? With the
proposedchanges, simply FOR SHARE locking the parent row on INSERT/UPDATE of the child, plus checking for child rows on
UPDATE/DELETEof the parent gives a 100% correct FK trigger.
I do not have a formal proof for that last assertion, but I'm not aware of any counter-examples either. Would love to
hearof any, though.
best regards,
Florian Pflug