PostgreSQL locking is designed to protect correctness while allowing substantial concurrency. The problem is rarely that a lock exists; it is that a transaction holds one longer than the workload can tolerate, or that a maintenance and application operation collide without a safe schedule.
Start from the blocked request
Identify the waiting backend, the requested lock mode, the object or relation involved and the blocking backend. Then follow the chain until you reach the transaction that can make progress. The visible victim may be a simple select or migration, while the root cause is an earlier session that is idle in transaction, running a bulk update or waiting on its client.
Ask the questions that change the remedy
- Is the blocker actively executing, waiting, or idle in transaction?
- Is the contention from DDL, a migration, a batch job or normal application writes?
- Does an inefficient query hold locks while scanning far more rows than necessary?
- Can the operation be scheduled, batched or made concurrent where PostgreSQL supports it?
Use timeouts as guardrails, not diagnosis
A lock timeout can prevent a request from waiting indefinitely, and an idle-in-transaction timeout can contain application mistakes. They do not decide which operation should win or make a non-idempotent retry safe. Design retries around the business operation and ensure failed work is visible to the caller.
After remediation, validate both contention and correctness. The successful result is not only fewer waits; it is a transaction pattern that completes reliably during real concurrent load.
Find waiting sessions without guessing from query age
A long query is not necessarily a blocker. Use pg_blocking_pids to identify blocking relationships, then inspect the returned process identifiers and their transactions. This gives a more useful first pass than treating every old session as a candidate for termination.
SELECT pid, application_name, state, xact_start,
pg_blocking_pids(pid) AS blocking_pids
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;
Run this selectively during the incident with appropriate monitoring privileges. Current activity can change while you investigate, so confirm the relationship again before acting. For prepared transactions or unusual lock ownership, a simple backend-to-backend chain may not explain everything; escalate the investigation rather than guessing which connection to end.
Example: a migration queues behind a long transaction
A deployment requests a strong table lock while a reporting transaction is still open. The migration waits, and later application requests can queue behind the pending lock. The report may be doing very little at the moment operators notice the outage. Its transaction boundary, rather than the visible current SQL, explains why the lock has not been released.
Investigate which operation is safe to postpone. A short lock timeout for migration sessions can help a deployment fail promptly and retry later, but changing it does not make an incompatible schema operation safe. Plan a maintenance window or an online migration technique appropriate to the actual change. Include connection draining and the old application version in the rollout plan.
Cancellation and termination have different consequences
Cancelling a statement and terminating its session are distinct actions. Coordinate with the owner before either, and verify whether the transaction has ended afterwards. Killing an application connection can trigger reconnects and retries; repeated retries can recreate the same lock queue immediately. Preserve the evidence before the intervention clears the view.
After correcting the transaction scope or migration process, test an overlapping workload that previously caused the queue. Record maximum lock wait, deployment completion time and application error rate. A clean test with no concurrent readers does not exercise the original failure. Keep a bounded rollback or abort condition so the test itself cannot become an extended production incident.
Keep the evidence for the next incident
Mini DBA PostgreSQL monitoring provides query and metric history alongside session, lock, vacuum and WAL diagnostics, helping teams compare an incident with normal operation. Available evidence depends on the monitored version, permissions and configured collection.
References and further reading