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

On Thu, Sep 26, 2013 at 12:33 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> I think one thing that's pretty clear at this point is that almost any
> version of this feature could be optimized for either the insert case
> or the update case. For example, my proposal could be modified to
> search for a conflicting tuple first, potentially wasting an index
> probes (or multiple index probes, if you want to search for potential
> conflicts in multiple indexes) if we're inserting, but winning heavily
> in the update case.

I don't think that's really the case.

In what sense could my design really be said to prioritize either the
INSERT or the UPDATE case? I'm pretty sure that it's still necessary
to get all the value locks per unique index needed up until the first
one with a conflict even if you know that you're going to UPDATE for
*some* reason, in order for things to be well defined (which is
important, because there might be more than one conflict, and which
one is locked matters - maybe we could add DDL to let unique indexes
have a checking priority or something like that).

The only appreciable downside of my design for updates that I can
think of is that there has to be another index scan, to find the
locked-for-update row to update. However, that's probably worth it,
since it is at least relatively rare, and allows the user the
flexibility of using a more complex UPDATE predicate than "apply to
conflicter", which is something that the MySQL syntax effectively
limits users to.

--
Peter Geoghegan