Re: Contention preventing locking

On Thu, Feb 15, 2018 at 9:30 PM, Konstantin Knizhnik
<k.knizhnik@postgrespro.ru> wrote:
> Hi,
>
> PostgreSQL performance degrades signficantly in case of high contention.
> You can look at the attached YCSB results (ycsb-zipf-pool.png) to estimate
> the level of this degradation.
>
> Postgres is acquiring two kind of heavy weight locks during update: it locks
> TID of the updated tuple and XID of transaction created this version.
> In debugger I see the following picture: if several transactions are trying
> to update the same record, then first of all they compete for
> SnapshotDirty.xmax transaction lock in EvalPlanQualFetch.
> Then in heap_tuple_update they are trying to lock TID of the updated tuple:
> heap_acquire_tuplock in heapam.c
>

There is no function named heap_tuple_update.  Do you mean to say heap_update?

> After that transactions wait completion of the transaction updated the
> tuple: XactLockTableWait in heapam.c
>
> 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).
>

The attempts are controlled by marking the tuple as locked by me after
waiting on SnapshotDirty.xmax.  So, the backend which marks the tuple
as locked must get the tuple to update and I think it is ensured in
code that only one backend will be allowed to do so.  I can see some
serialization in this logic, but I think we should first try to get
the theory behind the contention problem you are seeing before trying
to find the solution for it.

-- 
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com