Re: regression, deadlock in high frequency single-row UPDATE

Mark Kirkwood wrote:

> In the most glaringly obvious (and possibly not at all helpful) analysis -
> this is because we are using "FOR KEY SHARE" instead of "FOR SHARE" in the
> newer code. Reverting this (see attached very basic patch) gets rid of the
> deadlock (tested in 9.5devel). However this is obviously a less than
> desirable solution, as it loses any possible concurrency improvement - that
> we worked hard to gain in the patch that added this new lock mode! We really
> want to figure out how to keep "FOR KEY SHARE" and have it *not* deadlock.

Yeah. The problem is that the INSERT in the fktab table takes a FOR KEY
SHARE lock in the pktab table. This puts the inserting process' Xid
into the tuple's Xmax, but allows concurrent UPDATE while the inserter
transaction is still in progress. If you use a FOR SHARE lock, the
UPDATE is disallowed until the inserter transaction is completed.

Having the inserter be live is what causes the heap_lock_tuple and
heap_update calls in the updater process to take the heavyweight tuple
lock.

I think what we should do here is NOT take the HW lock unless we're
going to sleep in heap_lock_tuple and heap_update. Right now we always
take it if HeapTupleSatisfiesUpdate returns HeapTupleBeingUpdated, even
if we end up not sleeping to wait for something else.

I'm going to experiment with that idea and see if it leads to a
solution. I tried the other idea yesterday (to keep the HW tuple lock
we acquire in heap_lock_tuple until heap_update is done) but aside from
being very complicated and bug-prone, it doesn't solve the problem
anyway.

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services