PostgreSQL pg_locks exposes information about locks held or awaited in the database cluster. It is useful when a query is waiting, but a large number of locks is not automatically an incident. Normal transactions hold locks as part of correct concurrency behavior.
A dependable investigation identifies the waiting session, the actual blocking relationship, and the transaction’s operational owner. It avoids treating every lock on the same relation as proof of conflict or terminating sessions without understanding their work.
Start with a visible symptom
Identify the affected operation, observed delay, and relevant request or job identity. Determine whether the session is actually waiting on a lock rather than slow I/O, CPU, client behavior, or another condition.
Record the time and database context. Live lock evidence changes quickly, so a later observation may describe a different state even if the application symptom looks similar.
Keep the investigation scoped. Collecting every query text from every database session can expose private data and create a report too noisy to explain the incident.
Distinguish held locks from awaited locks
The granted field separates a held lock from an awaited lock. Mode and lock type describe the relevant resource and requested authority within PostgreSQL’s lock model.
A waiting row should be interpreted alongside the session and blocker evidence. Do not assume that another row with the same relation identifier is the blocker without checking lock compatibility and queue behavior.
Wait-start information can help establish duration, but documented edge cases include brief periods where a wait exists and the timestamp is not yet populated. Treat missing information honestly rather than inventing a duration.
Use supported blocker identification
PostgreSQL recommends pg_blocking_pids rather than trying to reconstruct all blocking relationships through a simplistic self-join of pg_locks.
An illustrative read-only activity query is:
SELECT pid, state, wait_event_type, wait_event,
pg_blocking_pids(pid) AS blocking_pids
FROM pg_stat_activity
WHERE datname = current_database();Run diagnostics only under an approved monitoring role. Visibility can depend on permissions, and this example does not automatically establish every transaction’s business owner.
Review returned blockers and relevant session context before deciding on an intervention.
Correlate activity without copying secrets
Session state, transaction age, application identity, and approved query context can explain why a lock remains held. Query text may contain private values or sensitive operational details.
Select only the fields needed and protect the diagnostic result. Do not paste a complete live activity dump into a public issue or an unrestricted chat.
A session waiting for client input can still hold transaction locks. Identify whether the application left a transaction open rather than assuming only actively executing queries can block other work.
Respect database scope when naming objects
Pg_locks provides a cluster-wide view, not only the current database. Resolving a relation identifier through the current database’s catalog is only valid for the documented applicable database scope.
Do not attach a plausible table name to an object identifier from another database. That can lead operators to intervene in the wrong application.
Keep database identity with the lock record and perform object resolution in the correct context. A readable table name is useful only when its relationship to the recorded identifier is actually valid.
Interpret transaction and special lock types
Not every lock concerns a simple table row or relation. Transaction identifiers, advisory locks, and other resource classes have different meanings.
Prepared transactions can also hold locks without an ordinary current session PID. A missing PID should not automatically be treated as a harmless stale row that can be ignored.
Review the supported lock type and associated transaction workflow. If two-phase commit or advisory coordination is involved, the responsible application and recovery policy are part of the diagnosis.
Follow the blocking chain rather than one count
A session may wait behind another session that is itself waiting elsewhere. The practical cause can be farther up the chain than the first blocker displayed.
Correlate the chain with transaction age and application ownership. A short-lived normal writer may not be the source of the prolonged incident if an older transaction prevents it from progressing.
Do not repeatedly query the entire cluster at excessive frequency. Diagnostic work should remain bounded and should not add unnecessary load during a performance incident.
Keep cancellation and termination distinct
Interrupting a statement and terminating a session have different consequences. Both can affect application behavior, retries, and transaction cleanup.
Use the approved operational procedure and establish ownership before acting. An unfamiliar long transaction may be a critical maintenance or business operation rather than an abandoned query.
Preserve useful evidence before intervention and define the expected recovery behavior. Do not apply a blanket kill command to every session holding locks just to make a dashboard look clear.
Verify application recovery after intervention
A blocker disappearing does not prove the business workflow recovered correctly. Waiting operations can fail, retry, or encounter a changed state once they proceed.
Inspect the affected request or job outcome and any duplicate-effect protections. Recovery can expose application assumptions about transaction retries that a lock-only review misses.
Also confirm that the application no longer leaves transactions open unnecessarily. Otherwise the same pattern can return shortly after the immediate incident is cleared.
Turn findings into a narrow prevention plan
Possible fixes include shorter transaction ownership, appropriate timeouts, consistent update order, and avoiding long external work inside a transaction. Choose based on the observed cause.
Do not increase every timeout to hide waiting or remove every concurrency control to reduce lock counts. Locks can be protecting required data invariants.
Test the proposed change under representative concurrent workload and retain the incident’s evidence. Pg_locks is useful when it supports a specific causal explanation, not when its rows become a vague measure of database health.
Frequently asked questions
Are many locks automatically bad?
No. Normal transactions require locks; investigate waits and operational impact in context.
Is a self-join enough to identify blockers reliably?
It is difficult to implement correctly. Use supported blocker functions and correlate the result with activity evidence.
Does removing a blocker prove business success?
No. Verify the affected application’s actual outcomes and recovery behavior.
See the pg_locks documentation for fields, cluster scope, and blocker-identification guidance.
For a complementary workflow, read PostgreSQL Deadlocks: Diagnose and Retry Safely.
