Re: Problems with approach #2 to value locking (INSERT ... ON CONFLICT UPDATE/IGNORE patch)

On Sat, Jan 3, 2015 at 1:29 AM, Heikki Linnakangas
<hlinnakangas(at)vmware(dot)com> wrote:
> Please explain in words of one syllable how the deadlock arises. Then,
> please find a way to fix it.

I believe that the deadlock arises because there is no choke point.
Exclusion constraint insertions always insert first, then check for a
conflict later. Whereas with B-Trees, we check *while* inserting, at
the critical point when an exclusive buffer lock is held on the first
leaf page a value could be on.

Two concurrent exclusion constraints inserters can easily insert at
exactly the same time, and then wait on each other's xact, and then
deadlock. That can't happen with B-Tree inserts because the checking
and insertion happen at the same time, when that exclusive buffer lock
is held. Some inserter establishes the right to insert, and then
actually inserts atomically, and when it releases the buffer lock
every other inserter will see for itself that it has inserted (and
established the right to do so).

I'm sorry, but I honestly don't see a way to fix this one. It would
take a very novel approach, since exclusion constraints can work with
any amgettuple AM. I briefly though about doing something crazy with
the deadlock detector, but apart from anything else I think that might
introduce livelock risks.
--
Peter Geoghegan