Re: Row-level Locks & SERIALIZABLE transactions, postgres vs. Oracle

On May 13, 2010, at 23:39 , Tom Lane wrote:
> Florian Pflug <fgp(at)phlo(dot)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 currently true for READ COMMITTED transactions but not for SERIALIZABLE ones, and pretty undesirable a behavior for a locking primitive.

c) I fail to see how the current behavior is useful in the presence of SERIALIZABLE transactions. Currently, they could IMHO completely 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 the behavior of FOR UPDATE / FOR SHARE becomes much easier to grasp. In both cases a SHARE lock would then say "Only update this 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 proposed changes, simply FOR SHARE locking the parent row on INSERT/UPDATE of the child, plus checking for child rows on UPDATE/DELETE of 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 hear of any, though.

best regards,
Florian Pflug