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