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

On Tue, Jan 14, 2014 at 2:16 PM, Heikki Linnakangas
<hlinnakangas(at)vmware(dot)com> wrote:
>> I don't think it's a mundane issue. But in any case, you haven't
>> addressed why you think your proposal is more or less better than my
>> proposal, which is the pertinent question.
>
> 1. It's simpler.
>
> 2. Works for exclusion constraints.

Thank you for clarifying where you're coming from.

> I guess that simplicity is in the eye of the beholder, but please take a
> look at git diff --stat:
>
> 41 files changed, 1224 insertions(+), 107 deletions(-)
>
> vs.
>
> 50 files changed, 2215 insertions(+), 240 deletions(-)
>
> Admittedly, some of the difference comes from the fact that you've spent a
> lot more time commenting and polishing the btreelock patch. But mostly I
> dislike additional complexity required in b-tree for this.

It's very much down to differences in how well commented and
documented each patch is. I have a fully formed amendment to the AM
interface, complete with documentation of the AM and btree aspects,
and detailed comments around how the parts fit together. But you've
already explored doing something similar to what I do, to similarly
avoid having to refind the page (less the heavyweight locking), which
seems almost equivalent to what I propose in terms of its impact on
btree, before we consider anything else.

> I don't think B-tree locking is more conservative. The insert-and-then-check
> approach is already used by exclusion constraints, I'm just extending it to
> not abort on conflict, but do something else.

If you examine what I actually do, you'll see that it's pretty much
equivalent to how the extant value locking of unique btree indexes has
always worked. It's just that the process is staggered at an exact
point, the point where traditionally we hold no buffer locks, only a
buffer pin (although we do additionally verify that the index gives
the go-ahead before getting to later indexes, to get consensus to
proceed with insertion).

The suggestion that mine is the conservative approach is also based on
the fact that database systems have made use of page level exclusive
locks on indexes, managed by the lock manager, persisting over complex
operations in many different contexts for many years. This includes
Postgres, where for many years relcache takes precautions again
deadlocking in such AMs by ordering the list of indexes associated
with a relation by pg_index.indexrelid. Currently this may not be
necessary, but the principle stands.

The insert-then-check approach of exclusion constraints is quite
different to what is proposed here, because exclusion constraints only
ever have to abort the xact if things don't work out. There is no
value locking. That's far easier to pin down. You definitely don't
have to do anything new with visibility.

--
Peter Geoghegan