Read held and requested locks separately
Pg_locks exposes held locks and outstanding lock requests inside PostgreSQL. Several rows involving one table do not automatically indicate a conflict: many lock modes are compatible. Identify the target, requested mode and participating backend, then ask where an incompatible request prevents progress. Granted is true for a held lock and false for a request still waiting. Reducing the total number of locks is not the diagnostic objective. The useful goal is explaining the resource dependency responsible for the application delay being investigated.
Check lock types and targets
Locktype distinguishes relations, transaction identifiers, advisory resources and other objects. Resolve relation identifiers in the correct database and read the mode as well as the object identity. Multiple weak locks on a table are different from a stronger schema-change request waiting for incompatible access to finish. For example, a stream of short readers can coexist with an outstanding DDL request under particular timing and modes; that is a reasoning example, not a measured incident. Joining every lock row with the same relation identifier produces false accusations because object equality alone does not establish conflicting modes or queue ordering.
SELECT pid, locktype, mode, granted, waitstart,
pg_blocking_pids(pid) AS blockers
FROM pg_locks
WHERE NOT granted;Trace dependencies with pg_blocking_pids
Pg_blocking_pids(pid) provides a practical starting point for current blocking relationships. Begin with a waiting backend, then combine returned identifiers with pg_stat_activity to inspect statements and transaction ages. A process ahead in the wait queue can matter as well as a process already holding a lock. Consequently, a home-made join based only on matching granted rows can miss important queue dependencies. Trace a chain when short requests accumulate behind a long wait, and determine which specific dependency must end for the downstream work to proceed. Preserve observation times as the chain can change during investigation.
Account for row-lock visibility and change
Ordinary row-lock ownership is largely recorded in row data rather than exposed as one pg_locks row for every locked tuple. An updater blocked by another transaction can therefore appear as a transaction-ID wait. Do not interpret the absence of a tuple entry as proof that row locking is irrelevant. Lock observations also change while they are being collected, and prepared transactions can retain locks without a normal backend PID. Distinguish statement cancellation from ending a connection and consider rollback effects. A targeted intervention follows an explained dependency; terminating all sessions is neither a lock model nor a substitute for identifying which application transaction should release its resource.