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

On Tue, Jan 14, 2014 at 2:43 AM, Heikki Linnakangas
<hlinnakangas@vmware.com> wrote:
> Hmm. So the scenario would be that a process inserts a tuple, but kills it
> again later in the transaction, and then re-inserts the same value. The
> expectation is that because it inserted the value once already, inserting it
> again will not block. Ie. inserting and deleting a tuple effectively
> acquires a value-lock on the inserted values.

Right.

> Yeah, it would be bad if updates start failing because of this. We could add
> a check for that, and return true if the tuple was updated rather than
> deleted.

Why would you fix it that way?

>> I have already described how we can take
>> advantage of deleting tuples while still holding on to their "value
>> locks" [1] to Andres. I think it'll be very important for multi-master
>> conflict resolution. I've already described this useful property of
>> dirty snapshots numerous times on this thread in relation to different
>> aspects, as it happens. It's essential.
>
> I didn't understand that description.

I was describing how deleting existing locked rows, and re-inserting,
could deal with multiple conflicts for multi-master replication
use-cases. It hardly matters much though, because it's not as if the
usefulness and necessity of this property of dirty snapshots is in
question.

>> Anyway, I guess you're going to need an infomask bit to fix this, so
>> you can differentiate between 'promise' tuples and 'proper' tuples.
>
> Yeah, that's one way. Or you could set xmin to invalid, to make the killed
> tuple look thoroughly dead to everyone.

I'm think you'll have to use an infomask bit so everyone knows that
this is a promise tuple from the start. Otherwise, I suspect that
there are race conditions. The problem was that
inserted-then-deleted-in-same-xact tuples (both regular and promise)
were invisible to all xacts' dirty snapshots, when they should have
only been invisible to the deleting xact's dirty snapshot. So it isn't
obvious to me how you interlock things such that another xact doesn't
incorrectly decide that it has to wait on what is really a promise
tuple's xact for the full duration of that xact, having found no
speculative insertion token to ShareLock (which implies unprincipled
deadlocking), while simultaneously having other sessions not fail to
see as dirty-visible a same-xact-inserted-deleted non-promise tuple
(thereby ensuring those other sessions correctly conclude that it is
necessary to wait for the end of the xmin/xmax xact). If you set the
xmin to invalid too late, it doesn't help any existing waiters.

Even if setting xmin to invalid is workable, it's a strike against the
performance of your approach, because it's another heap buffer
exclusive lock.

> You have suspected that many times throughout this thread, and every time
> there's been a relatively simple solutions to the issues you've raised. I
> suspect that's also going to be true for whatever mundane next issue you
> come up with.

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. You haven't given me so
much as a high level summary of whatever misgivings you may have about
it, even though I've asked you to comment on my approach to value
locking several times. You haven't pointed out that it has any
specific bug (which is not to suppose that that's because there are
none). The point is that it is not my contention that what you're
proposing is totally unworkable. Rather, I think that the original
proposal will probably ultimately perform better in all cases, is
easier to reason about and is certainly far more modular. It appears
to me to be the more conservative of the two proposals. In all
sincerity, I simply don't know what factors you're weighing here. In
saying that, I really don't mean to imply that you're assigning weight
to things in a way that I am in disagreement with. I simply don't
understand what is important to you here, and why your proposal
preserves or enhances the things that you believe are important. Would
you please explain your position along those lines?

Now, I'll concede that it will be harder to make the IGNORE syntax
work with exclusion constraints with what I've done, which would be
nice. However, in my opinion that should be given far less weight than
these other issues. It's ON DUPLICATE KEY...; no one could reasonably
assume that exclusion constraints were covered. Also, upserting with
exclusion constraints is a non-starter. It's only applicable to the
case where you're using exclusion constraints exactly as you would use
unique constraints, which has to be very rare. It will cause much more
confusion than anything else.

INSERT IGNORE in MySQL works with NOT NULL constraints, unique
constraints, and all other constraints. FWIW I think that it would be
kind of arbitrary to make IGNORE work with exclusion constraints and
not other types of constraints, whereas when it's specifically ON
DUPLICATE KEY, that seems far less surprising.

-- 
Peter Geoghegan