Re: INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

On Tue, Sep 17, 2013 at 6:20 PM, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
>> I agree that unpredictable deadlocks are bad. I think the fundamental
>> problem with UPSERT, MERGE, and this proposal is what happens when the
>> conflicting tuple is present but not visible to your scan, either
>> because it hasn't committed yet or because it has committed but is not
>> visible to your snapshot. I'm not clear on how you handle that in
>> your approach.
>
> Hm. I think it should be handled exactly the way we handle it for unique
> indexes today. Wait till it's clear whether you can proceed.

That's what I do, although getting those details right has been of
secondary concern for obvious reasons.

> At some point we might to extend that logic to more cases, but that
> should be separate discussion imo.

This is essentially why I went and added a row locking component over
your objections. Value locks (regardless of implementation)
effectively stop an insertion from finishing, but not from starting.
ISTM that locking the row with value locks held can cause deadlock.
So, unfortunately, we cannot really discuss value locking and row
locking separately, even though I see the appeal of trying to. Gaining
an actual representative notion of the expense of releasing and
re-acquiring the locks is too tightly coupled with how this is handled
and how frequently we need to restart. Plus there may well be other
issues in the same vein that we've yet to consider.

--
Peter Geoghegan