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

On 12/27/2013 07:11 AM, Peter Geoghegan wrote:
> On Thu, Dec 26, 2013 at 5:58 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>> While mulling this over further, I had an idea about this: suppose we
>> marked the tuple in some fashion that indicates that it's a promise
>> tuple. I imagine an infomask bit, although the concept makes me wince
>> a bit since we don't exactly have bit space coming out of our ears
>> there. Leaving that aside for the moment, whenever somebody looks at
>> the tuple with a mind to calling XactLockTableWait(), they can see
>> that it's a promise tuple and decide to wait on some other heavyweight
>> lock instead. The simplest thing might be for us to acquire a
>> heavyweight lock on the promise tuple before making index entries for
>> it, and then have callers wait on that instead always instead of
>> transitioning from the tuple lock to the xact lock.

Yeah, that seems like it should work. You might not even need an
infomask bit for that; just take the "other heavyweight lock" always
before calling XactLockTableWait(), whether it's a promise tuple or not.
If it's not, acquiring the extra lock is a waste of time but if you're
going to sleep anyway, the overhead of one extra lock acquisition hardly
matters.

> I think the interlocking with buffer locks and heavyweight locks to
> make that work could be complex.

Hmm. Can you elaborate?

The inserter has to acquire the heavyweight lock before releasing the
buffer lock, because otherwise another inserter (or deleter or updater)
might see the tuple, acquire the heavyweight lock, and fall to sleep on
XactLockTableWait(), before the inserter has grabbed the heavyweight
lock. If that race condition happens, you have the original problem
again, ie. the updater unnecessarily waits for the inserting transaction
to finish, even though it already killed the tuple it inserted.

That seems easy to avoid. If the heavyweight lock uses the transaction
id as the key, just like XactLockTableInsert/XactLockTableWait, you can
acquire it before doing the insertion.

Peter, can you give that a try, please?

- Heikki