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

On Fri, Dec 20, 2013 at 12:39 PM, Heikki Linnakangas
<hlinnakangas(at)vmware(dot)com> wrote:
> Hmm. If I understand the problem correctly, it's that as soon as another
> backend sees the tuple you've inserted and calls XactLockTableWait(), it
> will not stop waiting even if we later decide to kill the already-inserted
> tuple.

Forgive me for being pedantic, but I wouldn't describe it that way.
Quite simply, the tuples speculatively inserted (and possibly later
deleted) are functionally value locks, that presently cannot be easily
released (so my point is it doesn't matter if you're currently waiting
on XactLockTableWait() or are just about to). I have to wonder about
the performance implications of fixing this, even if we suppose the
fix is itself inexpensive. The current approach probably benefits from
not having to re-acquire value locks from previous attempts, since
everyone still has to care about "value locks" from our previous
attempts.

The more I think about it, the more opposed I am to letting this
slide, which is an notion I had considered last night, if only because
MySQL did so for many years. This is qualitatively different from
other cases where we deadlock. Even back when we exclusive locked rows
as part of foreign key enforcement, I think it was more or less always
possible to do an analysis of the dependencies that existed, to ensure
that locks were acquired in a predictable order so that deadlocking
could not occur. Now, maybe that isn't practical for an entire app,
but it is practical to do in a localized way as problems emerge. In
contrast, if we allowed unprincipled deadlocking, the only advice we
could give is "stop doing so much upserting".

--
Peter Geoghegan