Re: Contention preventing locking

On 20 February 2018 at 13:19, Konstantin Knizhnik
<k(dot)knizhnik(at)postgrespro(dot)ru> wrote:
>
>
> On 20.02.2018 14:26, Simon Riggs wrote:
>>
>> On 15 February 2018 at 16:00, Konstantin Knizhnik
>> <k(dot)knizhnik(at)postgrespro(dot)ru> wrote:
>>
>>> So in heap_acquire_tuplock all competing transactions are waiting for TID
>>> of
>>> the updated version. When transaction which changed this tuple is
>>> committed,
>>> one of the competitors will grant this lock and proceed, creating new
>>> version of the tuple. Then all other competitors will be awaken and ...
>>> find
>>> out that locked tuple is not the last version any more.
>>> Then they will locate new version and try to lock it... The more
>>> competitors
>>> we have, then more attempts they all have to perform (quadratic
>>> complexity).
>>
>> What about the tuple lock? You are saying that is ineffective?
>>
>> src/backend/access/heap/README.tuplock
>
>
> In my last mail ni this thread I have mentioned that update of tuple cause
> setting of three heavy weight locks:
>
> 1. Locking of SnapshotDirty.xmax transaction (XactLockTableWait) in
> EvalPlanQualFetch
> 2. Tuple lock (heap_acquire_tuplock) in heap_tuple_update
> 3. Transaction lock (XactLockTableWait) in heap_tuple_update
>
> So what I see in debugger and monitoring pg_locks, is that under high
> contention there are a lot transactions waiting for SnapshotDirty.xmax.
> This lock is obtained before tuple lock, so tuple lock can not help in this
> case.

Hmm, that situation occurs erroneously, as I previously observed
https://www.postgresql.org/message-id/CANP8%2BjKoMAyXvMO2hUqFzHX8fYSFc82q9MEse2zAEOC8vxwDfA%40mail.gmail.com

So if you apply the patch on the thread above, does performance improve?

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