PostgreSQL ERROR: could not obtain lock — diagnosis and recovery

ERROR: could not obtain lock on row in relation and ERROR: canceling statement due to lock timeout mean a query requested a lock but PostgreSQL refused to wait. The database is not down; a session is holding a resource another transaction needs.

Three variants produce these errors:

• SELECT ... FOR UPDATE NOWAIT fails immediately if the row is locked.
• A statement that exceeds lock_timeout fails after waiting.
• DDL such as ALTER TABLE or CREATE INDEX requires AccessExclusiveLock and will wait or fail depending on session configuration.

These often cascade: one long-running query blocks a schema change, the schema change queues behind it, and subsequent queries queue behind the DDL until the connection pool exhausts.

What this means

PostgreSQL’s MVCC keeps ordinary DML from blocking reads or writes. Explicit row-level locking (FOR UPDATE, FOR SHARE), DDL, and maintenance commands use heavyweight locks outside MVCC. When a session requests a conflicting lock, it enters a wait queue.

NOWAIT raises an error immediately instead of queuing. lock_timeout cancels the waiter after the set duration. Without either, the session waits indefinitely, tying up a connection and often blocking operations behind it.

DDL such as ALTER TABLE, TRUNCATE, and DROP TABLE requires AccessExclusiveLock, which conflicts with all other access. Once DDL queues for a table, every new conflicting lock request queues behind it in FIFO order. A single long-running SELECT becomes a table-wide jam.

Row-level contention is subtle. Because row locks are stored on disk, pg_locks often shows a waiting process blocked on the holder’s transactionid rather than on the tuple itself. This confuses naive lock diagnosis queries.

flowchart LR
    A[Lock error] --> B{NOWAIT or timeout?}
    B -->|NOWAIT| C[Check pg_stat_activity for row lock holder]
    B -->|Timeout| D[Check pg_locks.waitstart]
    D --> E[Use pg_blocking_pids to find root blocker]
    C --> E
    E --> F{Blocker state}
    F -->|Active query| G[Evaluate terminate]
    F -->|Idle in transaction| H[Kill immediately]
    F -->|DDL queue| I[Cancel DDL or wait]

Common causes

Quick checks

Run these in a psql session as a superuser or a user with pg_monitor privileges.

-- Check for sessions currently waiting on locks
SELECT pid, usename, state, wait_event_type, wait_event, query_start, query
FROM pg_stat_activity
WHERE wait_event_type = 'Lock';

-- Find blockers for current lock waiters
SELECT pid, pg_blocking_pids(pid) AS blockers, query
FROM pg_stat_activity
WHERE wait_event_type = 'Lock';

-- List ungranted locks with query details
SELECT l.pid, l.locktype, l.relation::regclass, l.mode, l.granted,
       a.query_start, a.query
FROM pg_locks l
JOIN pg_stat_activity a ON l.pid = a.pid
WHERE NOT l.granted;

-- Find long-running active queries that may block DDL
SELECT pid, query_start, query
FROM pg_stat_activity
WHERE state = 'active'
  AND query_start < NOW() - INTERVAL '5 minutes';

-- Check if lock wait logging is enabled
SHOW log_lock_waits;

-- Review lock timeout and related settings
SELECT name, setting, unit FROM pg_settings
WHERE name IN ('lock_timeout', 'statement_timeout', 'deadlock_timeout', 'idle_in_transaction_session_timeout');

How to diagnose it

1. Identify the variant from the error message. “Could not obtain lock on row in relation” indicates NOWAIT. “Canceling statement due to lock timeout” indicates a timeout. DDL failures indicate AccessExclusiveLock contention.

2. Find the session in pg_stat_activity. Note the PID and error time. If still connected, wait_event_type is Lock.

3. Walk the blocker chain with pg_blocking_pids(). The immediate blocker may itself be waiting; recurse until you reach the granted lock holder.

4. Inspect the blocker in pg_stat_activity. Check state, xact_start, and query. idle in transaction means it holds locks while doing no work. active with a long runtime may be a legitimate query.

5. Determine lock type from pg_locks. Filter by the waiting PID and check locktype and relation::regclass. Row-level waits often surface as transactionid waits, not tuple locks.

6. Calculate wait duration. On PostgreSQL 16+, use pg_locks.waitstart. On earlier versions, infer from pg_stat_activity.query_start, remembering that query_start is the query start time, not necessarily the lock wait start.

7. Decide: terminate the blocker or cancel the waiter. Abandoned idle in transaction sessions are usually safe to terminate. Critical long-running reports may require canceling the DDL or NOWAIT query instead. In a DDL queue cascade, canceling the DDL usually unblocks the queue fastest.

8. Verify. Re-run the quick checks. Confirm pg_locks shows no ungranted locks on the affected relation and pg_stat_activity.wait_event_type has cleared.

Metrics and signals to monitor

Fixes

NOWAIT row-level lock failures

If the business logic tolerates waiting, remove NOWAIT and rely on lock_timeout. For queue-like workloads, use FOR UPDATE SKIP LOCKED, which skips locked rows instead of failing.

Tradeoff: SKIP LOCKED skips rows. Use it for work queues, not for operations that must process every row in order.

lock_timeout cancellations

Do not retry with a longer timeout until you identify and resolve the blocker.

Set application lock_timeout lower than statement_timeout so lock waits fail fast while runaway queries are still caught. A production-safe starting point is lock_timeout = '10s' and statement_timeout = '30s'.

For DDL, set a brief lock_timeout (1 to 5 seconds) before ALTER TABLE. If the lock is unavailable, the statement fails instead of queuing and blocking traffic.

DDL blocked by long queries

Identify the long query on the target table. Terminate it only if non-critical. If the query is critical, cancel the DDL and reschedule it. If the DDL has already acquired its lock and queries queue behind it, canceling the DDL frees the queue immediately.

Use CREATE INDEX CONCURRENTLY instead of plain CREATE INDEX during traffic. It takes longer and cannot run inside a transaction, but it avoids blocking reads and writes.

For table reorganization, prefer pg_repack over VACUUM FULL. pg_repack rewrites the table online and only needs a brief ACCESS EXCLUSIVE lock at the final swap.

Tradeoff: pg_repack requires a primary key or unique NOT NULL index and roughly twice the table’s size in free disk space.

Idle in transaction blockers

WARNING: pg_terminate_backend() forcibly closes the connection and rolls back its transaction. Confirm the PID and its impact before running.

If pg_stat_activity shows idle in transaction as the blocker:

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND state_change < NOW() - INTERVAL '5 minutes';

Then set idle_in_transaction_session_timeout between 60 seconds and 5 minutes to prevent abandoned transactions from holding locks indefinitely.

Prevention

• Set short lock_timeout for DDL. Run migrations with a brief timeout so they fail fast instead of queuing and serializing traffic.
• Set idle_in_transaction_session_timeout to '2min' or '5min'.
• Keep transactions short. Do not open a transaction, call an external service, then commit. Hold locks only for database work.
• Use CREATE INDEX CONCURRENTLY. Never run plain CREATE INDEX on a production table during traffic.
• Schedule heavy DDL in maintenance windows. Even with timeouts, some DDL requires exclusive locks. Run them when long queries are unlikely.
• Review long-running queries before they block maintenance. A 30-minute report is a blocker waiting to happen.
• Monitor pg_stat_database.deadlocks. Rising counts indicate inconsistent lock ordering or locks held too long.

How Netdata helps

• Correlates PostgreSQL connection states with system metrics to distinguish lock contention from CPU or disk saturation.
• Surfaces pg_stat_activity state distributions (active, idle, idle in transaction) without manual polling.
• Alerts on sustained idle in transaction durations that indicate abandoned lock holders.
• Tracks query latency percentiles alongside lock wait events to confirm whether a latency spike is caused by contention or plan regression.
• Visualizes replication lag on replicas to rule out streaming delays when diagnosing primary-side lock issues.

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 idle in transaction: detecting and killing zombie sessions: /guides/postgres/postgres-idle-in-transaction/
• PostgreSQL monitoring checklist: the signals every production database needs: /guides/postgres/postgres-monitoring-checklist/
• PostgreSQL monitoring maturity model: from reactive to self-healing: /guides/postgres/postgres-monitoring-maturity-model/
• PgBouncer pool exhausted: how to diagnose and fix client waits: /guides/postgres/postgres-pgbouncer-pool-exhausted/
• PostgreSQL FATAL: too many connections - causes and fixes: /guides/postgres/postgres-too-many-connections/