Subject: lwlock:LockManager wait_events

From: James Pang <[email-masked]>
Date: Fri, 25 Oct 2024 14:36:26 +0800

experts,
    we faced into a lot of  lwlock:LockManager wait-events , all of these
queries are "select ..." ,  there are other several session are doing DML,
insert/update/delete on same table.   Did these DML transactions holding
"transactionid" and "tuple" lock blocking "select" on lwlock:LockManager ?

Thanks,

James

---

From: Laurenz Albe <[email-masked]>
Date: Fri, 25 Oct 2024 10:36:45 +0300

On Fri, 2024-10-25 at 14:36 +0800, James Pang wrote:
> we faced into a lot of  lwlock:LockManager wait-events , all of these queries are "select ..." ,
> there are other several session are doing DML, insert/update/delete on same table.
> Did these DML transactions holding "transactionid" and "tuple" lock blocking "select" on lwlock:LockManager ?

This is not about waiting for a lock.  Rather, it means that very many sessions are trying
to take a lock.  They have to grab the lock manager to take a lock, and the competition for
that resource is the bottleneck.

It is hard to say what is the root cause without further analysis, but very often the
cause is that you have too many connections to the database.  Using an effective connection
pool *might* solve that particular problem.

Yours,
Laurenz Albe

---

From: Sean Massey <[email-masked]>
Date: Fri, 25 Oct 2024 19:37:25 +1100

You may find this helpful, the advice is not specific to RDS.

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/wait-event.lw-lock-manager.html

HTH.

On Fri, 25 Oct 2024 at 18:36, Laurenz Albe <[email-masked]> wrote:

> queries are "select ..." ,
> > Did these DML transactions holding "transactionid" and "tuple" lock
> blocking "select" on lwlock:LockManager ?
>

---

From: SAMEER KUMAR <[email-masked]>
Date: Fri, 25 Oct 2024 20:09:45 +0800

> "transactionid" and "tuple" lock blocking "select" on lwlock:LockManager ?

Which version of PostgreSQL?
Are these all same query or variant of same query (with different
parameters)?

What's the schema (you can redact column names) for the tables involved in
the select query which is blocked/waiting on lock_manager?

---

From: kyle Hailey <[email-masked]>
Date: Sat, 8 Feb 2025 11:41:18 -0800

late to the game here but this might be of interest:

https://ardentperf.com/2024/03/03/postgres-indexes-partitioning-and-lwlocklockmanager-scalability/