Thread: can a blocked transaction affect the performance of one that is blocking it?
can a blocked transaction affect the performance of one that is blocking it?
From
Eric Schwarzenbach
Date:
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
Re: can a blocked transaction affect the performance of one that is blocking it?
From
Nikolay Samokhvalov
Date:
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)?
Re: can a blocked transaction affect the performance of one that is blocking it?
From
David Mullineux
Date:
Yes.
Also, are you sure you mean deadlock ? It sounded like this is just a simple lock conflict and not a deadlock.
The first transaction could be blocked on something else (even tho it's not ally short and quick ).
Lock requests are in a queue
On Mon, 9 Dec 2024, 21: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