On 20 February 2018 at 13:19, Konstantin Knizhnik <k.knizh...@postgrespro.ru> wrote: > > > On 20.02.2018 14:26, Simon Riggs wrote: >> >> On 15 February 2018 at 16:00, Konstantin Knizhnik >> <k.knizh...@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.
Hmm, that situation occurs erroneously, as I previously observed https://www.postgresql.org/message-id/CANP8%2BjKoMAyXvMO2hUqFzHX8fYSFc82q9MEse2zAEOC8vxwDfA%40mail.gmail.com So if you apply the patch on the thread above, does performance improve? -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services