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

On 12/12/14 06:22, Alvaro Herrera wrote:
> 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 ...
>

Yeah, works for me too. I've tried your simplified and also Andrew's
original schema + queries against 9.5devel with deadlock.patch applied
(neither show any deadlocks in that case).

Cheers

Mark