Re: Contention preventing locking

On 26.02.2018 17:00, Amit Kapila wrote:
> On Thu, Feb 15, 2018 at 9:30 PM, Konstantin Knizhnik
> <k(dot)knizhnik(at)postgrespro(dot)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?

Yes, sorry. I mean 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.
>

Sorry, I could not say, that I completely understand logic of locking
updated tuples in Postgres, although  I have spent some time inspecting
this code.
It seems to be strange and quite inefficient to me that updating one
tuple requires obtaining three heavy-weight locks.
Did you read this thread:

https://www.postgresql.org/message-id/CANP8%2BjKoMAyXvMO2hUqFzHX8fYSFc82q9MEse2zAEOC8vxwDfA%40mail.gmail.com

In any, the summary of all my last experiments with locking is the
following:

1. My proposal with transaction lock chaining can reduce performance
degradation with increasing number of competing transactions.
But degradation still takes place. Moreover, my recent experiments shows
that lock chaining can cause deadlocks. It seems to me that I know the
reason of such deadlocks,
but right now have no idea how to fix them.
2.  Replacing tuple TID locks with tuple PK lock has positive effect
only if lock is hold until end of transaction. Certainly we can not lock
all updated tuples (it will cause lock hash overflow).
But we can keep until end of transaction just some small number of tuple
locks. Unfortunately it is not the only problem with this solution: it
is relatively simple to implement in case of integer primary key, but
handling all kind of PKs, including compound keys requires more efforts.
Also there can be no PK at all...
3. Alternative to PK lock is root tuple lock (head of  hot update
chain). Unfortunately there is no efficient way (at least I do not know
one) of locating root tuple.
Scanning the whole page seems to be too inefficient. If we perform index
scan, than we actually start with root tuple, so it is possible to
somehow propagate it.
But it doesn't work in case of heap scan. And I suspect that as in case
of PK lock, it can increase performance only of lock is hold until end
of transaction.

So the problem with lock contention really exist. It can be quite easily
reproduced at the computer with large number of core with either pgbench
with zipf distribution,
either with YCSB benchmark, either with my pgrw benchmark... But how to
fix it is unclear.

--
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company