Reading PostgreSQL Locks: Blocking, Deadlocks and Long Transactions

When client applications experience sudden latency spikes, connection pool exhaustion, or cascading HTTP 504 Gateway Timeouts, the default operational assumption is frequently hardware resource saturation—high CPU, memory pressure, or disk I/O bottlenecks. However, in relational database engines governed by Multiversion Concurrency Control (MVCC) such as PostgreSQL, some of the most catastrophic throughput collapses occur while CPU utilization remains below 15% and disk I/O is virtually idle.

To triage the incident accurately before executing intrusive commands, three distinct failure profiles must be distinguished:

1. 1CPU/Disk Starvation: All server CPU cores are pinned in user or system mode by unindexed sequential scans, nested loop cartesian joins, or heavy sorting spills to disk (temporary files).
2. 2Connection Starvation without Locks: Clients open connections faster than PostgreSQL can fork backend worker processes or poolers can dispatch them, with active queries running quickly but connections queuing at the ingress.
3. 3Lock Wait Contention: Queries are dispatched into backend workers, but their execution state transitions from active processing to wait_event_type = 'Lock'. Here, workers sleep while waiting for conflicting row, table, or transaction locks held by another session.

A core design promise of PostgreSQL's MVCC is often summarized as: *"Readers do not block writers, and writers do not block readers."* While this rule holds true for read-only queries (SELECT) reading committed row versions against concurrent UPDATE, DELETE, or INSERT operations, it creates a dangerous misconception that locking issues cannot occur under standard transaction isolation levels (READ COMMITTED).

MVCC does not prevent:

• •Concurrent Write-Write Conflicts: When two or more concurrent transactions attempt to modify the same physical table tuple (row) via UPDATE or DELETE, or acquire an explicit row lock via SELECT ... FOR UPDATE, only the first transaction is granted the row lock. Subsequent transactions must wait on the transaction ID of the first transaction until it issues an explicit COMMIT or ROLLBACK.
• •DDL vs DML Collisions: Any schema modification (ALTER TABLE, CREATE INDEX without CONCURRENTLY, DROP TABLE, or VACUUM FULL) requires an AccessExclusiveLock. This lock conflicts with every other lock mode, including AccessShareLock (held by mundane SELECT queries). A single long-running read query can queue a pending DDL change, which in turn queues every subsequent read query behind it, creating a catastrophic pile-up.
• •Transaction Lifespan Rule: Row-level locks acquired during a transaction are never released statement-by-statement. Regardless of how quickly an UPDATE statement finishes executing on the wire, the underlying tuple lock remains held until the transaction is definitively resolved by COMMIT or ROLLBACK.

When lock contention occurs, running naive queries against pg_locks joined with multiple self-referential relations often causes confusion because pg_locks exposes low-level virtual and physical lock handles without reflecting PostgreSQL's internal priority and wait-queue semantics.

Starting in PostgreSQL 9.6 and fully standardized across modern engines (PostgreSQL 14–18), the database engine provides the built-in system information function pg_blocking_pids(pid). This function interrogates the engine's internal lock wait queue and returns an integer array containing the exact PIDs of backend processes that directly or indirectly block the target process.

A prevalent operational mistake during incidents is sorting pg_stat_activity purely by query_start or xact_start and assuming the top session is the root cause.

• •xact_start: Timestamp when the current transaction block began (BEGIN).
• •query_start: Timestamp when the currently active SQL statement was dispatched to the server.
• •state_change: Timestamp when the session transitioned between states (e.g. from active to idle in transaction or entering wait_event_type = 'Lock').

A long-running analytical query may have an xact_start of 30 minutes ago, yet hold only non-blocking read locks. Conversely, a microservice transaction that executed an UPDATE 15 seconds ago and then went idle in transaction because an unhandled application exception skipped conn.close() can silently block 500 downstream web requests. Always navigate from the blocked waiters via pg_blocking_pids() back to the root blocker.

The operational divergence between blocking and deadlocks is fundamental:

1. 1Linear Blocking: Session A holds Lock 1; Session B waits for Lock 1. If Session A never terminates, Session B waits indefinitely (until bounded by lock_timeout or manual intervention). The database engine will not intervene automatically.
2. 2Cyclic Deadlocks: Session A holds Row 1 and requests Row 2. Simultaneously, Session B holds Row 2 and requests Row 1. A circular dependency is established where neither session can advance.

• •PostgreSQL runs a background deadlock detection algorithm. Whenever a lock wait exceeds the configured deadlock_timeout (default: 1000ms), the server traverses the lock wait-for graph.
• •Upon detecting a cycle, PostgreSQL arbitrarily chooses one session as the victim, terminates its active query with SQLSTATE 40P01, and rolls back its transaction block.
• •Crucially, the live lock graph cannot capture past deadlocks. Once the victim is aborted, the cycle collapses immediately. Deadlock forensics rely strictly on the PostgreSQL server logs containing the deadlock graph.

All empirical assertions in this document were verified in an isolated, synthetic test environment running on clean, non-production containers.

Execution Environment:

• •Engine: PostgreSQL 17.11 (Debian 17.11-1.pgdg13+2), 64-bit
• •Image Digest: sha256:67f41722b7a8cbdb868a44a4995c846eddfdc2973bccb291ce937dce88ad5675
• •Container Configuration: log_lock_waits = on, deadlock_timeout = 1000ms, log_line_prefix = '%m [%p] %q%u@%d app=%a '
• •Fixture: Two account tuples: id=1 ($1000.00), id=2 ($500.00).

• •Setup: Session A (app_checkout_a, PID 113) updated id=1. Session B (app_batch_b, PID 120) attempted concurrent update on id=1.
• •Observed Telemetry:

• •Resolution: Session A issued ROLLBACK;. Session B unblocked within 0.04s, completed its update, and issued COMMIT;. Final balance for id=1 verified as $1050.00.

• •Setup: Session A (PID 127) locked id=1 and requested id=2. Session B (PID 134) locked id=2 and requested id=1.
• •Engine Behavior: After exactly 1000ms (deadlock_timeout), PostgreSQL detected the dependency loop:

• •Observation: Session A was chosen as victim and aborted; Session B unblocked immediately and committed successfully. The victim cannot be hardcoded in application logic—both sessions must handle SQLSTATE 40P01.

Both scenarios produce error class 55P03 (lock_not_available), but their timing and causal semantics differ sharply:

• •Subcase 3a (`NOWAIT`): SELECT ... FOR UPDATE NOWAIT returned immediately (measured duration: 0.0507s) with:

ERROR: could not obtain lock on row in relation "accounts".

• •Subcase 3b (`lock_timeout`): SET lock_timeout = '400ms' waited for the boundary (measured duration: 0.4048s) before aborting with:

ERROR: canceling statement due to lock timeout.

• •Conclusion: NOWAIT provides fail-fast behavior with 0 wait time; lock_timeout bounds worst-case queuing latency.

Error class 57014 (query_canceled) is shared between automated timeouts and manual DBA interventions:

• •Subcase 4a: Bounded by statement_timeout = '500ms'. Aborted at 0.5215s with:

ERROR: canceling statement due to statement timeout.

• •Subcase 4b: Bounded by explicit SELECT pg_cancel_backend(pid). Query aborted with:

ERROR: canceling statement due to user request.

• •Conclusion: Monitoring systems cannot distinguish query timeouts from operational kills by error code alone; parsing the log message context is mandatory.

This experiment verified the exact operational danger of connection pool leaks:

• •Session A (PID 162) executed UPDATE accounts SET balance = balance - 50 WHERE id = 1 and remained idle in transaction.
• •Attempted Cancel: Observer issued SELECT pg_cancel_backend(162);.
• •Returned Result: cancel_returned_bool = true (boolean success).
• •Engine Reality: Telemetry revealed PID 162 remained in state: idle in transaction, and RowExclusiveLock on accounts remained fully granted and held. pg_cancel_backend interrupts active SQL execution; it has zero effect on an idle transaction!
• •Targeted Termination: Observer issued SELECT pg_terminate_backend(162);.
• •The PostgreSQL backend process was terminated immediately.
• •The server rolled back the uncommitted transaction and released the row locks, restoring system throughput.

When live lock contention threatens availability, follow a disciplined escalation ladder:

1. 1Identify the Root Blocker: Run the pg_blocking_pids inspection query. Never kill arbitrary waiters.
2. 2Evaluate Blocker State:

• •If state = 'active': The blocker is actively computing a query. Attempt non-destructive cancellation:

• •If state = 'idle in transaction': pg_cancel_backend will fail to release locks. Coordinate with the application team and execute targeted termination:

1. 1Verify Lock Release: Confirm through pg_stat_activity that the blocked processes transition to state = 'active' and complete execution.

For detailed operational procedures, refer to our verified production runbooks:

• •Root Blocker Identification & Triage
• •Managing Long and Idle-in-Transaction Sessions
• •Post-Mortem Deadlock Investigation
• •Live Diagnostic Script: Who is Blocking Whom?

Eliminating lock contention requires systematic architectural hygiene across database and application boundaries:

1. 1Deterministic Access Ordering: If Transaction A updates Account 1 then Account 2, while Transaction B updates Account 2 then Account 1, deadlocks are mathematically guaranteed under high concurrency. Always enforce deterministic ordering in application code (SELECT ... FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE).
2. 2Transaction Scope Hygiene: Never perform network I/O, external REST API calls, or email dispatch inside a database transaction block. If an external service hangs for 10 seconds, your database transaction holds exclusive locks for 10 seconds.
3. 3Tiered Timeout Hierarchy:

• •idle_in_transaction_session_timeout = '15s': Prevents abandoned connection pool leaks from holding table locks indefinitely.
• •lock_timeout = '2s': Rejects lock acquisition attempts before connection pools saturate.
• •statement_timeout = '15s': Guards against rogue computational queries.

1. 1Idempotency & Retry Jitter: When handling transient concurrency failures (SQLSTATE 40P01 or 55P03), do not retry immediately. Immediate retries by dozens of concurrent workers create a *retry storm*, re-triggering the deadlock cycle. Implement randomized exponential backoff with full jitter.

To complete your operational framework, explore our companion perspectives:

• •Tool Selection (dataxtools): Choosing PostgreSQL Lock Observability: From Live Sessions to Historical Evidence
• •Incident Leadership (dataopsphere): Running a Lock-Wait Incident: Ownership, Intervention Decisions and Recovery Evidence
• •Core Internals: PostgreSQL Concurrency & MVCC Deep Dive