Re: Contention preventing locking

On 20.02.2018 14:26, Simon Riggs wrote:
> On 15 February 2018 at 16:00, Konstantin Knizhnik
> <k.knizhnik@postgrespro.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.

>
>
>> My idea was that we can avoid such performance degradation if we somehow
>> queue competing transactions so that they will get control one-by-one,
>> without doing useless work.
>> First of all I tried to replace TID  lock with PK (primary key) lock. Unlike
>> TID, PK of record  is not changed during hot update. The second idea is that
>> instead immediate releasing lock after update we can hold it until the end
>> of transaction. And this optimization really provides improve of performance
>> - it corresponds to pg_f1_opt configuration at the attached diagram.
>> For some workloads it provides up to two times improvement comparing with
>> vanilla Postgres. But there are many problems with correct (non-prototype)
>> implementation of this approach:
>> handling different types of PK, including compound keys, PK updates,...
> Try locking the root tid rather than the TID, that is at least unique
> per page for a chain of tuples, just harder to locate.
>

But locking primary key is more efficient, isn't it?
The problem with primary key is that it doesn't always exists or can be 
compound, but if it exists and has integer type, then get be obtained 
easily than root tid.
For pgrw benchmark primary key lock provides even better results than 
transaction lock chaining:

https://docs.google.com/spreadsheets/d/1QOYfUehy8U3sdasMjGnPGQJY8JiRfZmlS64YRBM0YTo/edit?usp=sharing

But for YCSB benchmark xlock optimization is more efficient:

https://docs.google.com/spreadsheets/d/136BfsABXEbzrleZHYoli7XAyDFUAxAXZDHl5EvJR_0Q/edit?usp=sharing




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