Locking & Deadlocks

PostgreSQL uses locks where concurrent operations cannot safely proceed at the same time. MVCC lets plain readers use older visible row versions, but two writers still cannot independently replace the same row, a schema change may need exclusive access to a table, and an application can explicitly reserve rows before making a decision.

The key distinctions are what object is locked, which mode is held, which modes conflict, how long the lock lives, and whether waiting is acceptable. “The table is locked” is not a useful diagnosis without those details.

Lock families have different jobs #

Locks are normally held until the transaction ends. An application cannot “unlock the row” midway through a transaction while keeping its earlier effects uncommitted. Rolling back to a savepoint releases locks acquired after that savepoint, not those acquired before it.

Every statement also takes a table lock #

The phrase “row-level query” does not mean no table lock exists. PostgreSQL automatically acquires a table mode that protects the relation from conflicting schema or maintenance operations.

Selected modes:

Only ACCESS EXCLUSIVE blocks a plain SELECT. This explains why a seemingly small migration can freeze application traffic: the DDL may be waiting for old transactions while newer queries queue behind its requested lock.

Explicit table locks use the same matrix:

LOCK TABLE accounts IN SHARE ROW EXCLUSIVE MODE NOWAIT;

Use the weakest sufficient mode, but acquire the mode actually needed at the start. Lock upgrades later in the transaction make deadlock reasoning harder.

Row-lock modes #

PostgreSQL exposes four row-lock strengths. Stronger is not automatically better: it rejects more compatible work.

UPDATE automatically takes FOR UPDATE when it changes columns covered by a qualifying unique index usable by a foreign key; otherwise it takes FOR NO KEY UPDATE. Row locks do not block a snapshot-only SELECT.

Row locks are represented in tuple metadata and can cause a disk write. They are not capped by max_locks_per_transaction; that setting sizes the shared lock table for heavyweight lockable objects, not a maximum row count.

SELECT ... FOR UPDATE, step by step #

The correct syntax is SELECT ... FOR UPDATE, not “select on update.” It does two things:

1. selects rows using the transaction's visibility rules;
2. obtains an exclusive row lock on every selected row before returning it.

BEGIN;

SELECT account_id, balance_cents
FROM accounts
WHERE account_id = 17
FOR UPDATE;

-- Application computes from the locked current row.
UPDATE accounts
SET balance_cents = balance_cents - 5_000
WHERE account_id = 17;

COMMIT;

The lock lasts until commit or rollback. A concurrent UPDATE, DELETE, or conflicting locking SELECT on that row waits; a plain SELECT reads the visible version without waiting.

At Read Committed, if another writer already owns the row, the locking query waits. After the writer finishes, PostgreSQL follows the updated version and rechecks the WHERE condition. The row may disappear from the result if it no longer matches.

What it does not guarantee #

FOR UPDATE does not:

• lock a row that does not exist;
• protect rows that a later query might discover but the locking query did not select;
• make an external API call transactional;
• prevent deadlock when several transactions lock resources in different orders;
• make a long user interaction safe—locks remain held while the user thinks;
• replace a database constraint for a durable invariant.

If absence itself must remain protected, use a unique constraint, a stable parent or coordination row, or Serializable isolation. An empty result has no tuple to lock.

Lock only the intended joined table #

Without OF, a locking clause can apply to all lockable relations contributing rows to the result. In a join, name the target:

SELECT o.order_id, c.risk_class
FROM orders AS o
JOIN customers AS c USING (customer_id)
WHERE o.order_id = $1
FOR UPDATE OF o;

This reserves the order without unnecessarily locking the customer row.

NOWAIT: fail instead of queue #

SELECT *
FROM seats
WHERE seat_id = $1
FOR UPDATE NOWAIT;

If the row cannot be locked immediately, PostgreSQL raises an error. NOWAIT applies to row locks; the required ROW SHARE table lock is still acquired in the normal way. Use LOCK TABLE ... NOWAIT separately if table-lock waiting must also fail fast.

NOWAIT is useful when the product can immediately report “resource busy” or try a different resource. It shifts waiting into an explicit application decision; it does not remove contention.

SKIP LOCKED: partition work, not general reads #

WITH next_job AS (
    SELECT job_id
    FROM jobs
    WHERE state = 'ready'
      AND run_after <= clock_timestamp()
    ORDER BY priority DESC, job_id
    FOR UPDATE SKIP LOCKED
    LIMIT 1
)
UPDATE jobs AS j
SET state = 'claimed',
    worker_id = $worker_id,
    claimed_at = clock_timestamp()
FROM next_job
WHERE j.job_id = next_job.job_id
RETURNING j.*;

Workers skip rows another transaction has locked and claim different work. The result is intentionally an inconsistent view of the predicate, so PostgreSQL documentation recommends it for queue-like access, not financial queries, pagination, or reports.

Commit the short claim transaction before doing slow external work. Keep a lease, heartbeat, or recovery policy for workers that die after claiming. If a worker holds the row lock during the entire job, it increases contention, retains a transaction snapshot, and makes recovery depend on connection death.

SKIP LOCKED is also not fairness: a frequently locked early row can be skipped many times. Monitor oldest-ready age, retries, and lease expiry—not only queue length.

Pessimistic and optimistic concurrency #

Do not lock a row while calling another service or waiting for human input. Optimistic versioning is usually better for editor workflows; pessimistic locking is often better for a short inventory allocation or state transition.

Deadlock is a cycle, not merely a long wait #

Two sessions can wait forever if each owns a lock the other needs:

T1: UPDATE accounts SET ... WHERE account_id = 1; -- owns row 1
T2: UPDATE accounts SET ... WHERE account_id = 2; -- owns row 2
T1: UPDATE accounts SET ... WHERE account_id = 2; -- waits for T2
T2: UPDATE accounts SET ... WHERE account_id = 1; -- waits for T1

PostgreSQL detects the cycle and aborts one unpredictable participant with SQLSTATE 40P01 deadlock_detected. The other can continue. The aborted transaction has not partially committed; the application should roll it back and may retry the whole operation.

Prevent cycles with one global order #

Every path that locks the same resource class should use the same deterministic order:

SELECT account_id
FROM accounts
WHERE account_id IN ($from_id, $to_id)
ORDER BY account_id
FOR UPDATE;

If all transfers lock the smaller account ID first, two-account cycles cannot form. For heterogeneous resources, define and document a hierarchy such as:

tenant → account → order → order_item

Acquire the most restrictive mode that will be needed when the resource is first locked. Keep transactions short. These practices reduce deadlocks; retry handling is still required because database features, foreign keys, indexes, and less obvious paths can add locks.

Deadlock timeout is not a query timeout #

PostgreSQL waits deadlock_timeout before running the relatively expensive deadlock check; the default is one second. This does not cancel ordinary lock waits. Separate controls serve separate purposes:

BEGIN;
SET LOCAL lock_timeout = '2s';
SET LOCAL statement_timeout = '5s';
-- transactional work
COMMIT;

• lock_timeout cancels a statement that spends too long acquiring a lock, separately for each acquisition attempt;
• statement_timeout limits total statement execution time;
• transaction_timeout limits the transaction's total duration;
• idle_in_transaction_session_timeout terminates a session left idle while a transaction is open;
• deadlock_timeout controls when the server checks for a wait cycle and is also the threshold used by log_lock_waits.

Apply user/workload-specific timeouts with role, database, or transaction-local settings. One global value rarely fits both interactive requests and batch maintenance.

Diagnose blockers from the waiter's perspective #

Start with pg_stat_activity and PostgreSQL's blocker function:

SELECT
    a.pid AS waiting_pid,
    a.application_name,
    a.usename,
    a.xact_start,
    a.query_start,
    a.wait_event_type,
    a.wait_event,
    pg_blocking_pids(a.pid) AS blocking_pids,
    a.query
FROM pg_stat_activity AS a
WHERE cardinality(pg_blocking_pids(a.pid)) > 0
ORDER BY a.query_start;

Then inspect the blocker sessions, their transaction age, application identity, state, and last query. pg_blocking_pids() understands PostgreSQL's lock queues and is preferred to attempting to reconstruct blockers with a simplistic self-join on pg_locks.

pg_locks still answers detailed questions about lock type, object, mode, whether it is granted, and when waiting began:

SELECT
    l.pid,
    l.locktype,
    l.mode,
    l.granted,
    l.waitstart,
    l.relation::regclass AS relation,
    a.state,
    a.xact_start,
    a.query
FROM pg_locks AS l
LEFT JOIN pg_stat_activity AS a USING (pid)
ORDER BY l.granted, l.waitstart NULLS LAST;

Row locks normally do not appear as tuple rows in pg_locks. A backend waiting for a row often appears as waiting on the owning transaction's transaction ID. Do not conclude “there is no row lock” merely because no tuple lock row is visible.

Useful production configuration:

• log_lock_waits = on logs waits exceeding deadlock_timeout;
• log_lock_failures = on can log failed NOWAIT acquisitions;
• a meaningful application_name lets operators map PIDs to services and jobs;
• transaction-age and “idle in transaction” alerts find sessions that retain locks and snapshots.

Never terminate a blocker from one query alone. Identify whether it is doing valid work, whether it is the root blocker or merely another waiter, and what rollback consequences termination will have.

Advisory locks are cooperative namespaces #

Advisory locks attach application meaning to one bigint key or a pair of 32-bit integers. PostgreSQL does not know what the key represents and does not automatically acquire the same lock when a row changes. Every cooperating code path must follow the convention.

Transaction-level form:

SELECT pg_advisory_xact_lock(hashtextextended($tenant_and_job_key, 0));

It is released automatically at transaction end. Session-level advisory locks survive transaction rollback and remain until explicitly unlocked or the session ends; that makes them easy to leak through connection pools. Prefer the transaction-level form unless ownership genuinely spans transactions.

Advisory locks are useful for coarse application resources, singleton jobs, or coordination where no natural row exists. They are not constraints, and key derivation must avoid accidental collisions or inconsistent ordering.

Locking failure patterns #

Senior and architect interview checks #

• Does SELECT FOR UPDATE block reads? Not ordinary MVCC reads; it blocks conflicting writers and row lockers.
• When is its lock released? At transaction end, except locks acquired after a savepoint can be released by rollback to that savepoint.
• What happens after it waits at Read Committed? PostgreSQL locks the current version and rechecks the predicate.
• Can it lock a range or missing key like a gap lock? PostgreSQL row locking locks selected existing rows. Use constraints, a coordination row, or Serializable predicate dependency tracking for absence/range invariants.
• Difference between NOWAIT and SKIP LOCKED? One fails immediately; the other omits locked rows and deliberately returns an inconsistent set.
• Deadlock versus lock timeout? A deadlock is a dependency cycle detected and broken by aborting a participant; a timeout is a local waiting budget and can fire without a cycle.
• Why can DDL block plain reads? Operations requesting ACCESS EXCLUSIVE conflict with ACCESS SHARE, which every plain SELECT holds.
• Why are row locks absent from pg_locks? PostgreSQL usually stores them in tuple state; a waiter is commonly represented by a transaction-ID wait.
• What is the safest deadlock response? Roll back and retry the whole transaction, while fixing inconsistent acquisition order where possible.
• Are advisory locks enough to enforce uniqueness? No. They work only when every writer cooperates; a unique constraint is authoritative.

Primary PostgreSQL references #

• Explicit locking
• SELECT locking clauses, NOWAIT, and SKIP LOCKED
• pg_locks
• System information functions, including pg_blocking_pids
• Lock management configuration
• Client connection timeouts
• Error reporting and lock-wait logging