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

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