Re: INSERT ... ON CONFLICT {UPDATE | IGNORE}

On 26/09/14 08:21, Simon Riggs wrote:
> On 25 September 2014 20:11, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>
>>> My approach would be to insert an index tuple for that value into the
>>> index, but with the leaf ituple marked with an xid rather than a ctid.
>>> If someone tries to insert into the index they would see this and wait
>>> for the inserting transaction to end. The inserting transaction would
>>> then resolve what happens in the heap (insert/update) and later
>>> repoint the index tuple to the inserted/updated row version. I don't
>>> see the need for page level locking since it would definitely result
>>> in deadlocks (e.g. SQLServer).
>> I think that something like this might work, but the devil is in the
>> details. Suppose two people try to upsert into the same table at the
>> same time. There's one index. If the transactions search that index
>> for conflicts first, neither sees any conflicting tuples, and both
>> proceed. That's no good. OK, so suppose each transaction inserts the
>> special index tuple which you mention, to lock out concurrent inserts
>> of that value, and then searches for already-existing conflicts. Each
>> sees the other's tuple, and they deadlock. That's no good, either.
> The test index is unique, so our to-be-inserted value exists on only
> one page, hence page locking applies while we insert it. The next
> person to insert waits for the page lock and then sees the test tuple.
>
> The page lock lasts only for the duration of the insertion of the
> ituple, not for the whole operation.
>
>> Also, I think there are other cases where we think we're going to
>> insert, so we put the special index tuple in there, but then we decide
>> to update, so we don't need the promise tuple any more, but other
>> sessions are potentially still waiting for our XID to terminate even
>> though there's no conflict any more. I'm having a hard time bringing
>> the details of those cases to mind ATM, though.
> We make the decision to INSERT or UPDATE based upon what we find in
> the test index. If a value if there already, we assume its an UPDATE
> and go to update the row this points to. If it has been deleted we
> loop back and try again/error. If the value is not present, we insert
> the test tuple and progress as an INSERT, then loop back later to set
> the ctid. There is no case of "don't need promise id anymore". We
> would use the PK, identity or first unique index as the test index.
> There is a case where an UPSERT conflicts with an INSERT causing the
> latter to abort.
>
> Anyway, this is why we need the design more clearly exposed, so you
> can tell me I'm wrong by showing me the URL of it done right.
>
What happens if the new value(s) of the INERT/UPDATE require the page to
be split?

I assume the mechanics of this are catered for, but how does it affect
locking & potential deadlocks?

Cheers,
Gavin