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

Alvaro Herrera wrote:

> 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.

Here's a preliminary patch. It does solve the deadlock in my simplified
test case. If Andrew can confirm that it fixes his original problem
too, that'd be good.

Before this can be committed I need an isolationtester spec file that
reproduces the problem. Now that I understand why it happens it should
be easy to produce: just have a transaction that does BEGIN, then the
insert, and keeps the transaction open; enough other sessions run the
UPDATE until the problem pops up. (Also, comments on
Would_MultiXactIdWait_Block need work.)

FWIW this code should also have slightly better performance than the
original coding, since the heavyweight tuple lock acquisition is skipped
in some cases. Not sure if that is measurable, though. Maybe in
extreme cases such as the one in #8470 ...

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