Re: Contention preventing locking

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

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

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