Uncovering Hidden PostgreSQL Lock Requests: Why Implicit Locks Cause Unexpected Waits

Background

When using a database, lock waits are common; for example, updating a row blocks another session that tries to update the same row. Some lock waits are less obvious because they stem from implicit lock requests made by internal PostgreSQL functions.

Implicit lock requests in PostgreSQL

Consider two sessions, A and B. Session A holds a lock on a row. Session B runs the query select * from pg_get_indexdef('test_pkey'::regclass); to retrieve an index definition. If the index is locked, Session B will wait.

To observe these hidden waits, enable lock debugging as described in the referenced blog post, then run the sessions and capture lock information using the script

https://github.com/digoal/pgsql_admin_script/blob/master/generate_report.sh

. The following images illustrate the lock states before and after releasing Session A:

Other implicit lock scenarios

Functions that retrieve object definitions, such as rule definitions or view definitions, also request AccessShareLock on the underlying tables. The article shows examples where querying rule definitions or view definitions leads to similar lock waits, illustrated by the following images:

Potential deadlock with waiting locks

If a long-running query holds a lock on table A and another session attempts DDL on A, the DDL will wait for an exclusive lock. While the DDL is waiting, any additional queries on A become blocked by the waiting DDL, potentially causing a cascade of blocked requests.

Recommendations

When performing DDL, set a lock timeout to avoid indefinite waiting: set lock_timeout='1s'; In autocommit scenarios, set a statement timeout for statements that may hold large locks: set statement_timeout='1s'; Detailed lock‑debug information is available in the PostgreSQL source file src/include/storage/lock.h.