Обсуждение: Locks analyze

> On Oct 12, 2023, at 5:46 PM, Rajesh Kumar <rajeshkumar.dba09@gmail.com> wrote:
>
> How do I analyze locks and resolve it. Is there only one query that helps? This lock issue leading to high
connectionsit seems. 

What lock issue? What do you mean leading to high connections?

On Fri, 2023-10-13 at 05:16 +0530, Rajesh Kumar wrote:
> How do I analyze locks and resolve it. Is there only one query that helps?
> This lock issue leading to high connections it seems.

Once you have identified a session that is blocked, get its process ID
from pg_stat_activity and SELECT pg_blocking_pids(12345);
with that process number as argument.

Then you get all the other sessions that block that session.

Yours,
Laurenz Albe

On Fri, 2023-10-13 at 15:09 +0530, Rajesh Kumar wrote:
> How do I identify a session that is blocked?

See "wait_event_type" in "pg_stat_activity" (or see "pg_locks").

> Can blocking sessions increase idle sessions?

Only within certain limits, unless your applicaton is doing something wrong.

> I see something like waitdurarion in pg_locks..is that helpful anyway?

Yes, that means that the session has been waiting for the lock for a while.

Yours,
Laurenz Albe

> On Oct 13, 2023, at 3:39 AM, Rajesh Kumar <rajeshkumar.dba09@gmail.com> wrote:
>
> I am often facing idle connection spike. That is why I am checking all these.

Do total sessions increase along with idle sessions? Or does the total remain the same, but more are idle?