PostgreSQL row-level lock contention: SELECT FOR UPDATE patterns and fixes

P99 latency doubles. pg_stat_activity shows wait_event_type = 'Lock'. Queries are simple, indexes are present, and CPU is idle. The culprit is usually a row-level lock queue: one transaction holds a tuple lock longer than expected, and every subsequent transaction touching the same row waits in FIFO order.

This is not a deadlock. PostgreSQL detects deadlocks automatically and kills one participant. Lock contention is different: a slow or abandoned transaction acts as a head-of-line blocker. Unless you are polling pg_locks and transaction age, the queue is invisible in query logs. The pattern is most common with SELECT ... FOR UPDATE in database-backed job queues, UPDATE statements that silently take stronger locks than intended, and session-level advisory locks that survive rollback.

What this means

PostgreSQL row-level locking has four modes with escalating strength: FOR KEY SHARE, FOR SHARE, FOR NO KEY UPDATE, and FOR UPDATE. Stronger modes block equal or weaker conflicting modes on the same tuple. When a transaction acquires a row lock, subsequent conflicting requests queue until the holder commits, rolls back, or is terminated.

MVCC means readers do not block writers and writers do not block readers. Row-level contention only affects transactions that explicitly request a lock or perform an operation that implicitly requires one. SELECT FOR UPDATE is the most explicit source, but UPDATE is equally important. An UPDATE that modifies a column covered by a unique index usable for foreign keys silently escalates to FOR UPDATE instead of the weaker FOR NO KEY UPDATE. If your application assumes an inventory decrement only blocks other decrements, but it actually blocks readers using FOR SHARE, you have introduced an invisible serialization point.

flowchart TD
    A[Lock waits in pg_stat_activity] --> B[Find oldest xact_age]
    B --> C[Check pg_locks mode]
    C --> D{Lock type}
    D --> E[tuple]
    D --> F[advisory]
    E --> G[Add SKIP LOCKED or queue]
    F --> H[Use xact-scoped locks]
    B --> I[idle in transaction]
    I --> J[Set idle_in_transaction_session_timeout]

Common causes

Quick checks

psql -c "SELECT pid, usename, state, wait_event_type, query_start, query FROM pg_stat_activity WHERE wait_event_type = 'Lock';"

psql -c "SELECT pid, locktype, mode, granted, relation::regclass, page, tuple FROM pg_locks WHERE locktype = 'tuple' AND NOT granted LIMIT 10;"

psql -c "SELECT pid, usename, state, xact_start, now() - xact_start AS xact_age, query FROM pg_stat_activity WHERE xact_start IS NOT NULL ORDER BY xact_start LIMIT 10;"

psql -c "SELECT pid, mode, classid, objid, objsubid FROM pg_locks WHERE locktype = 'advisory';"

psql -c "SELECT datname, deadlocks FROM pg_stat_database WHERE deadlocks > 0;"

psql -c "SELECT schemaname, relname, n_tup_upd, n_tup_hot_upd FROM pg_stat_user_tables ORDER BY n_tup_upd DESC LIMIT 5;"

How to diagnose it

1. Confirm the wait type. Filter pg_stat_activity for wait_event_type = 'Lock'. Waits on LWLock or IO indicate internal contention or disk saturation, not row-level locking.
2. Classify the lock. Query pg_locks for granted = false. locktype = 'tuple' indicates a direct row-level lock; transactionid indicates you are waiting for an uncommitted transaction to end.
3. Find the blocker. For tuple locks, join pg_locks (granted) to pg_locks (not granted) on matching relation, page, and tuple. For transactionid waits, the blocker is the backend whose transaction ID matches the lock.
4. Determine why the blocker is slow. If state is idle in transaction, the application opened a transaction and walked away. If state is active, check query_start. A query running for minutes may indicate a slow plan or a blocked external call.
5. Map the query pattern. If the waiting query is SELECT ... FOR UPDATE without SKIP LOCKED, workers are piling onto the same rows. If the blocker is an UPDATE on a table with foreign-key-related unique indexes, check for silent escalation to FOR UPDATE. If LIMIT is used directly with FOR UPDATE, the planner may lock every candidate row before applying the limit.
6. Check for advisory lock chains. If locktype = 'advisory', verify whether the application uses session-scoped locks that persist across transactions. Count advisory locks and compare them to active transactions.

Metrics and signals to monitor

Fixes

Long-lived transactions holding locks

Move all external work outside the transaction boundary. Fetch and lock the row only when all inputs are ready, and commit immediately. Set idle_in_transaction_session_timeout = '2min' to terminate abandoned transactions, and set lock_timeout = '1s' on queue workers so they fail fast instead of queueing indefinitely. Terminate a stuck blocker with SELECT pg_terminate_backend(pid). Warn the application team that termination rolls back the transaction.

Queue workers without SKIP LOCKED

If workers poll with SELECT ... FOR UPDATE, add SKIP LOCKED so workers skip rows already locked by peers. Isolate LIMIT inside a subquery when using FOR UPDATE; PostgreSQL can lock candidate rows before applying LIMIT, so a query that returns one row may still lock many. Select target IDs first, then join and lock. If the queue table is high-churn, consider partitioning or moving to a purpose-built message broker.

Silent FOR UPDATE escalation

When an UPDATE modifies a column that is part of a unique index usable for foreign keys, PostgreSQL silently takes FOR UPDATE instead of FOR NO KEY UPDATE. If you only need to update non-key columns, select with FOR NO KEY UPDATE explicitly, then update. If you must update the key column, accept that readers using FOR SHARE will block, and design around shorter transactions.

Advisory lock leakage

Replace session-level advisory locks with transaction-scoped locks so that ROLLBACK or COMMIT releases them automatically. If session scope is required, wrap every acquisition in application-level error handling that calls the unlock function in a finally block. Never apply LIMIT directly to a query that acquires advisory locks; wrap the limited set in a subquery first, then lock.

Hot-row updates

Shard updates across rows instead of concentrating them on a single counter or status row. If you must update a single hot row, keep the transaction short and avoid secondary index updates that prevent HOT. Setting fillfactor to 85 or 90 on high-update tables can increase HOT update rates and reduce index-level locking pressure.

Prevention

• Set idle_in_transaction_session_timeout and lock_timeout as guardrails before deploying queue or inventory logic.
• Review every SELECT ... FOR UPDATE to confirm whether SKIP LOCKED or NOWAIT is appropriate.
• Never hold row locks across network boundaries, user confirmations, or external API calls.
• Monitor pg_stat_database.deadlocks and pg_stat_activity lock waits with alerts.
• Prefer transaction-scoped advisory locks unless the application explicitly needs session survival.
• Isolate LIMIT in a subquery before FOR UPDATE to avoid locking more rows than returned.

How Netdata helps

• Netdata collects pg_stat_activity wait events. A sudden cluster of Lock waits appears next to query latency spikes.
• Transaction age per backend is tracked continuously, surfacing the long-running blocker without manual pg_stat_activity polling.
• Deadlock counts from pg_stat_database are shown per database.
• Lock wait metrics are correlated with system CPU and disk I/O to prevent misdiagnosis of contention as storage saturation.
• Table update rates from pg_stat_user_tables appear alongside lock metrics to identify hot rows before queues form.

Related guides

• How PostgreSQL actually works in production: a mental model for operators: /guides/postgres/how-postgres-works-in-production/
• PostgreSQL connection exhaustion: detection, diagnosis, and prevention: /guides/postgres/postgres-connection-exhaustion/
• PostgreSQL connection refused: pg_hba, listen_addresses, and TCP diagnosis: /guides/postgres/postgres-connection-refused/
• PostgreSQL deadlock detected: how to diagnose and prevent deadlocks: /guides/postgres/postgres-deadlock-detected/
• PostgreSQL idle in transaction: detecting and killing zombie sessions: /guides/postgres/postgres-idle-in-transaction/
• PostgreSQL ERROR: could not obtain lock — diagnosis and recovery: /guides/postgres/postgres-lock-not-available/
• PostgreSQL monitoring checklist: the signals every production database needs: /guides/postgres/postgres-monitoring-checklist/
• PostgreSQL monitoring maturity model: from reactive to self-he