Re: INSERT ... ON CONFLICT IGNORE (and UPDATE) 3.0

On Mon, Mar 30, 2015 at 9:20 AM, Peter Geoghegan <pg(at)heroku(dot)com> wrote:
>> * The code uses LockTupleExclusive to lock rows. That means the fkey key
>> locking doesn't work; That's annoying. This means that using upsert
>> will potentially cause deadlocks in other sessions :(. I think you'll
>> have to determine what lock to acquire by fetching the tuple, doing
>> the key comparison, and acquire the appropriate lock. That should be
>> fine.
>
> Looking into the implementation of this. As we discussed, the
> difficulty about avoiding a lock escalation within ExecUpdate() is
> that we must fetch the row, run EvalPlanQual() to check if the new row
> version generated by updating will require a LockTupleExclusive or
> LockTupleNoKeyExclusive, and then lock the row using the right
> lockmode, and only then call ExecUpdate(). Right now, UPSERT benefits
> from fetching and locking the row together, so going this way imposes
> a little additional complexity. It's probably worth it, though.

Why do you think deadlocks will be a particular concern? Sure, it
could make the difference between deadlocking and not deadlocking,
which is bad, but it's not obvious to me that the risk would be any
worse than the risk of deadlocking with FKs in 9.2. Is that really all
that bad?

--
Peter Geoghegan