Re: can a blocked transaction affect the performance of one that is blocking it? - Mailing list pgsql-performance

From Nikolay Samokhvalov
Subject Re: can a blocked transaction affect the performance of one that is blocking it?
Date
Msg-id CAM527d_JP20hZ9bLKWUmMXx_bQms4B=W4321HApfcQ98cx_1HA@mail.gmail.com
Whole thread Raw
In response to can a blocked transaction affect the performance of one that is blocking it?  (Eric Schwarzenbach <subscriber@blackbrook.org>)
List pgsql-performance
On Mon, Dec 9, 2024 at 13:16 Eric Schwarzenbach <subscriber@blackbrook.org> wrote:
Hi,

Could one transaction (one that should be relatively simple and short)
cause another complex, long running transaction (involving INSERTS, on a
table the first transaction may be reading from) to take many orders of
magnitude longer than it would normally? (short of competing for system
resources, like CPU time etc, of course)

I don't believe my scenario involved a deadlock but I expect my short
transaction was probably blocked by my long one. Does it make any sense
that this could very significantly affect the performance of the
non-blocked transaction?

Thanks,

Eric

Have you tried wait event analysis (looking at wait_event_type, wait_event, state, query samples from pg_stat_activity)?

pgsql-performance by date:

Previous
From: Eric Schwarzenbach
Date:
Subject: can a blocked transaction affect the performance of one that is blocking it?
Next
From: Lars Aksel Opsahl
Date:
Subject: Re: PostgreSQL and a Catch-22 Issue related to dead rows