PostgreSQL lock not available: distinguish NOWAIT from deadlock

The symptoms

• •Application log shows "ERROR: could not obtain lock on relation ...", "lock not available", or SQLSTATE 55P03, frequently preceded by SELECT ... FOR UPDATE NOWAIT or SELECT ... FOR UPDATE SKIP LOCKED.
• •Application log shows "ERROR: deadlock detected", SQLSTATE 40P01, and PostgreSQL aborts one transaction in the cycle with a "Process N waits for ..." detail block.
• •Statement returns immediately rather than blocking, which is itself the diagnostic clue: a NOWAIT/SKIP LOCKED failure is non-blocking by definition.
• •Same workload passes when run sequentially with psql but fails when concurrent workers contend for the same rows or tables, indicating lock contention rather than a logical bug.
• •pg_stat_activity shows multiple sessions in wait_event_type = Lock, wait_event = relation, transactionid, tuple, or extend, while the failing session is in active state (it returned rather than waited).
• •Performance schema or application metrics show rising lock_wait_time alongside retries, but query latency does not show blocking waits, consistent with NOWAIT-style fail-fast.

Likely causes

• •SELECT/UPDATE/DELETE/INSERT ... FOR UPDATE NOWAIT used against rows or tables already locked by another transaction in a conflicting mode (e.g., trying for an exclusive row lock while another holds ExclusiveLock on the relation).
• •SELECT/UPDATE ... SKIP LOCKED used in a queue-style worker loop where the workers race past an empty window and exit cleanly, but the application treats the empty result as an error.
• •Two transactions hold locks the other needs in a cycle, producing a real deadlock detected by PostgreSQL's deadlock detector (SQLSTATE 40P01), unrelated to NOWAIT.
• •Lock_timeout or statement_timeout set too aggressively on a session that performs multi-row updates on hot rows; the timeout returns SQLSTATE 55P03 with "canceling statement due to lock timeout".
• •DDL contention: an ALTER TABLE, VACUUM FULL, REINDEX, or CREATE INDEX CONCURRENTLY holds an AccessExclusiveLock or ShareUpdateExclusiveLock that blocks ordinary writers; NOWAIT converts the wait into an instant failure.
• •Foreign key validation or trigger-driven secondary writes that acquire locks on a table the caller did not realize was in scope.
• •Connection pooler (e.g., PgBouncer in transaction mode) keeping a session idle while locks are held, so a "new" application request reuses a session whose prior transaction is still uncommitted.

First ten minutes

1. 01Capture the exact SQLSTATE from the error: 55P03 implies NOWAIT/SKIP LOCKED or lock_timeout; 40P01 implies a true deadlock. The two require different triage.
2. 02Identify the wait policy that fired by re-reading the statement and the session settings: search the application code path for FOR UPDATE NOWAIT, FOR SHARE NOWAIT, SKIP LOCKED, and SET lock_timeout / SET statement_timeout.
3. 03Snapshot pg_stat_activity filtered to wait_event_type = 'Lock', noting the wait_event, xact_start, and query, to see who is currently blocking on locks.
4. 04Snapshot pg_locks joined with pg_stat_activity and pg_class to map granted = false rows to the relation and transaction id they are waiting on, separated by lock mode.
5. 05If SQLSTATE is 40P01, copy the "Process N waits for ...; Process M waits for ... blocked by process K" detail block verbatim; it is your cycle map.
6. 06Decide the audience before changing code: 55P03 is a retry/scheduling problem, 40P01 is a lock-ordering or scope problem. Do not apply NOWAIT to a deadlock, and do not reorder statements against a NOWAIT miss.

Evidence to collect

• •SQLSTATE returned to the client (55P03 vs 40P01) and the full message text including relation name, tuple, or transaction id when present.
• •The wait policy of the failing statement: presence of NOWAIT, SKIP LOCKED, lock_timeout, and statement_timeout for the session.
• •Concurrent sessions in pg_stat_activity with wait_event_type = 'Lock', their wait_event names, xact_start, query, and state.
• •pg_locks rows where granted = false, joined to pg_class to name the relation and to pg_stat_activity to name the blocker.
• •For 40P01 only: the deadlock detail block naming the two or more processes and the lock types they each hold and request.
• •Application-side timing around the failure: whether latency was near-zero (NOWAIT) or matched the lock_timeout window, and whether retries succeeded.

Where to look

• •Application logs and database driver logs at the precise timestamp of the error, to capture SQLSTATE, message, and the originating statement.
• •PostgreSQL server log only if log_min_duration_statement, log_lock_waits, or log_statement is enabled; otherwise the server log carries only the error code, not the lock graph.
• •Catalog view pg_stat_activity (wait_event_type, wait_event, state, xact_start, query) for live blockers and waiters on the same database.
• •Catalog view pg_locks joined with pg_class and pg_stat_activity to translate locktype, mode, granted, and relation into a human-readable contention map.
• •Catalog view pg_stat_database (conflicts, deadlocks counters) for a long-run view of whether deadlocks are systemic or a one-off.
• •DDL change log and migration history if the failure coincides with a recent schema change, since DDL takes strong locks that ordinary DML does not.

Diagnostic steps

1. 01Confirm SQLSTATE: 55P03 narrows the cause to NOWAIT, SKIP LOCKED, or lock_timeout; 40P01 indicates a deadlock cycle detected by the server. Treat them as separate paths.
2. 02For 55P03, read the message text: "lock not available" with no time phrase points to NOWAIT/SKIP LOCKED; "canceling statement due to lock timeout" points to lock_timeout (which is also a 55P03 per PostgreSQL's code path).
3. 03For 55P03 with NOWAIT/SKIP LOCKED, query pg_stat_activity for blockers on the same relation and run pg_locks to confirm the conflicting lock mode; decide whether to retry with backoff, lower concurrency, or change the lock scope.
4. 04For 55P03 with lock_timeout, check whether the session parameter lock_timeout is set too low for the workload's hot rows; raising it or moving the work outside contended windows are the conditional fixes.
5. 05For 40P01, reconstruct the cycle from the error detail block: each process, the lock mode it holds, and the conflicting mode it requests. Look for the classic A→B→A shape across tables or row ranges.
6. 06For 40P01, verify lock ordering by inspecting the statements of each involved transaction in pg_stat_activity; inconsistent acquisition order across two code paths is the common root cause.
7. 07Cross-check pg_stat_database.deadlocks against application error counts to determine whether the deadlock is recurring or a one-time scheduling coincidence.
8. 08Decide the fix branch: NOWAIT path needs scheduling or scope changes; deadlock path needs ordering or scope changes. Mixing them produces regressions.

Common mistakes

• •Treating SQLSTATE 55P03 as a deadlock and trying to "reorder locks" when the statement never blocked; NOWAIT means there was no wait to reorder, and the fix is retry, backoff, or scope.
• •Adding NOWAIT to a statement that is already a deadlock victim; NOWAIT does not prevent deadlocks, it only shortens waits, so the cycle still terminates with 40P01 when detected.
• •Reading "lock not available" as if every lock failure means the same thing, ignoring the lock_timeout variant which produces the same SQLSTATE but a different message and a different remediation.
• •Retrying NOWAIT failures on a tight loop without jitter or backoff, amplifying contention rather than smoothing it; this turns a one-time miss into a thundering herd.
• •Assuming SKIP LOCKED returning zero rows is an error; SKIP LOCKED is a normal empty-result outcome when no rows are unlocked, and the application should treat it as "no work" rather than a fault.
• •Conflating connection-pool reuse with logical sessions: a pooler handing back a session whose prior transaction is still open can produce lock failures unrelated to the current request.
• •Disabling log_lock_waits or log_statement and then trying to diagnose from the server log alone; without those, the log only carries the error code, not the lock graph.

Safe fixes

• •For NOWAIT misses on hot rows, introduce bounded exponential backoff with jitter in the application retry path, capped at a budget; this is conditional on observing repeated 55P03 with wait_event_type = 'Lock' on the same relation.
• •For SKIP LOCKED used in a worker queue, treat an empty result set as the legitimate "no job" signal and exit the loop, rather than raising an error or retrying immediately.
• •For lock_timeout-induced 55P03, raise lock_timeout only for the specific session or transaction scope that performs multi-row hot updates, and only after confirming the contention pattern in pg_locks.
• •For true deadlocks (40P01), enforce a global lock acquisition order across the involved tables and row ranges in application code, so that every transaction takes locks in the same sequence. This is conditional on identifying the cycle from the error detail block.
• •For deadlocks caused by FK validation or trigger side effects, narrow the transaction so the secondary table is updated before the primary, or defer constraints with DEFERRABLE INITIALLY DEFERRED where the schema permits.
• •For DDL-driven contention, schedule migrations and maintenance (VACUUM FULL, REINDEX, CREATE INDEX CONCURRENTLY) outside the application's contended window, and use lock_timeout or NOWAIT as a guardrail on the migration runner.
• •For pooler-induced stale locks, ensure the application commits or rolls back before returning a session to the pool, and verify the pooler is in a mode that does not preserve open transactions across requests.

Prove the fix

1. 01Reproduce the original failure on a staging clone under the same concurrency; the change must remove the original SQLSTATE for the same workload shape (no 55P03 for the NOWAIT fix, no 40P01 for the ordering fix).
2. 02Run the workload for a fixed window (for example 30 minutes) with the same client count; observe pg_stat_database.deadlocks remains at its prior baseline for the deadlock path, and 55P03 counts in application logs drop to expected noise for the NOWAIT path.
3. 03Confirm via pg_stat_activity that wait_event_type = 'Lock' duration on the targeted relation drops or shifts to expected maintenance windows, while no new wait_event values appear that were not present before the change.
4. 04For the retry/backoff fix, verify that retry attempts are spread in time by inspecting application metrics or logs of the retry timestamps; absence of synchronized retry spikes is the regression check.
5. 05For the lock-ordering fix, run a deterministic concurrency test (two transactions interleaved) that previously reproduced the cycle and confirm the new ordering yields no 40P01 and identical final state.
6. 06Roll back the change if any of the above checks fail or if a new SQLSTATE or wait_event pattern emerges; the proof is observable in the same views and logs used for diagnosis.

Prevention and next steps

• •Adopt a documented global lock acquisition order for the application's top tables, and review new code paths against it before merge; this prevents 40P01 cycles by construction.
• •Default to bounded retries with exponential backoff and jitter for any code path that uses FOR UPDATE NOWAIT or SKIP LOCKED, and cap total retry budget to avoid hidden latency.
• •Keep transactions short: acquire locks as late as possible and release them by committing quickly, reducing the window in which another worker can race for the same rows.
• •Treat SKIP LOCKED zero-row results as normal queue-empty signals in worker code; document this so on-call engineers do not page on it.
• •Enable log_lock_waits and a moderate log_min_duration_statement on non-production mirrors so lock contention shows up before it reaches production.
• •Schedule DDL and maintenance (VACUUM FULL, REINDEX, CREATE INDEX CONCURRENTLY) outside peak contended windows, and use lock_timeout on the migration runner as a guardrail.
• •Verify the connection pooler is in a mode that does not preserve open transactions across client checkouts; mismatched pooler modes are a recurring source of phantom locks.

Safe commands and checks

SELECT pid, usename, application_name, state, wait_event_type, wait_event, xact_start, query FROM pg_stat_activity WHERE datname = current_database() AND wait_event_type = 'Lock' ORDER BY xact_start;
SELECT pg_locks.locktype, pg_locks.mode, pg_locks.granted, pg_locks.pid, pg_class.relname FROM pg_locks LEFT JOIN pg_class ON pg_locks.relation = pg_class.oid WHERE NOT pg_locks.granted ORDER BY pg_locks.pid;
SELECT pg_stat_activity.pid AS waiter_pid, blocked_locks.pid AS blocked_pid, blocking_locks.pid AS blocking_pid, pg_class.relname FROM pg_locks blocked_locks JOIN pg_stat_activity ON blocked_locks.pid = pg_stat_activity.pid JOIN pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype AND blocking_locks.pid != blocked_locks.pid WHERE NOT blocked_locks.granted;
SELECT datname, deadlocks, conflicts FROM pg_stat_database WHERE datname = current_database();
SHOW lock_timeout; SHOW statement_timeout; -- run in the failing session to confirm which timeout is active
SELECT pg_backend_pid(); -- capture the current session pid for correlating with pg_locks rows