🚨 PostgreSQL Error Guide: 'canceling statement due to lock timeout' Lock Acquisition Failures

Exact Error Message

ERROR:  canceling statement due to lock timeout
CONTEXT:  while updating tuple (0,12) in relation "orders"
STATEMENT:  UPDATE orders SET status = 'shipped' WHERE id = 4821;

The SQLSTATE for this condition is 55P03 (lock_not_available).

What the Error Means

PostgreSQL raises this error when a statement waits longer than the configured lock_timeout to acquire a lock and gives up. The lock_timeout parameter caps how long any single statement will block while trying to obtain a table or row lock. If the wait exceeds that threshold, PostgreSQL cancels the statement rather than letting it block indefinitely.

This is different from statement_timeout (which limits total statement run time) and from a deadlock (which is a true lock cycle). A lock timeout simply means the lock you needed was held by someone else for too long. The statement is aborted, the surrounding transaction is left in a failed state, and the client must roll back. It is a protective measure: rather than have a migration or update hang for minutes behind a long transaction, you fail fast and react.

Common Causes

• A long-running transaction holding a conflicting lock. A session that updated the same row or holds an ACCESS EXCLUSIVE lock (for example, a slow ALTER TABLE) blocks others until it commits.
• Idle-in-transaction sessions. A connection that ran an UPDATE and then sat idle without committing keeps row locks held, blocking later writers until lock_timeout fires.
• Schema migrations behind traffic. DDL like ALTER TABLE ... ADD COLUMN needs an ACCESS EXCLUSIVE lock; if any open transaction is reading the table, the DDL waits and times out.
• Aggressive lock_timeout settings. A deliberately low lock_timeout (common in safe-migration tooling) trades availability for fast failure, so even brief contention trips it.
• Hot-row contention. Many writers updating the same row serialize on its lock; under load some exceed the timeout.

How to Reproduce the Error

Session 1 acquires and holds a row lock without committing:

BEGIN;
UPDATE orders SET status = 'processing' WHERE id = 4821;
-- leave this transaction open (do not COMMIT)

Session 2 sets a short timeout and tries to update the same row:

SET lock_timeout = '2s';
UPDATE orders SET status = 'shipped' WHERE id = 4821;
-- after 2 seconds:
-- ERROR:  canceling statement due to lock timeout

Session 2 waited two seconds for the row lock held by Session 1, then was canceled.

Diagnostic Commands

Check the current lock_timeout setting:

psql -c "SHOW lock_timeout;"

Identify which sessions are blocked and who is blocking them (read-only):

psql -c "SELECT blocked.pid AS blocked_pid, pg_blocking_pids(blocked.pid) AS blocked_by, \
left(blocked.query, 60) AS blocked_query \
FROM pg_stat_activity blocked \
WHERE cardinality(pg_blocking_pids(blocked.pid)) > 0;"

Find long-running and idle-in-transaction sessions holding locks:

psql -c "SELECT pid, state, now() - xact_start AS xact_age, left(query,50) AS query \
FROM pg_stat_activity WHERE state IN ('active','idle in transaction') \
ORDER BY xact_start LIMIT 10;"

Inspect locks held and waited on a specific relation:

psql -c "SELECT l.pid, c.relname, l.mode, l.granted FROM pg_locks l \
JOIN pg_class c ON c.oid = l.relation WHERE c.relname = 'orders' ORDER BY l.granted;"

Look for the error and its context in the logs:

sudo journalctl -u postgresql --no-pager | grep -B1 -A2 "lock timeout" | tail -40

Step-by-Step Resolution

1. Identify the blocker. Run the pg_blocking_pids query above to find the PID holding the lock your statement needs. Note its state and xact_age.

2. Decide whether to wait or clear it. If the blocker is a legitimate long transaction nearing completion, simply retry once it commits. If it is idle in transaction, it is stuck and should be cleared.

3. Cancel or terminate a stuck holder (with care). Use pg_cancel_backend(pid) to stop the current statement gracefully, or pg_terminate_backend(pid) to end the connection if it is idle in transaction. Confirm the PID and its query first.

4. Reduce the blocking window. Ensure application transactions commit promptly and set idle_in_transaction_session_timeout so stuck transactions are reaped automatically.

5. Tune lock_timeout appropriately. For interactive OLTP, a few seconds is reasonable. For online migrations, keep it low (for example 5s) and retry the DDL, so the migration never blocks production traffic for long.

6. Schedule heavy DDL for low-traffic windows. Operations needing ACCESS EXCLUSIVE locks are far less likely to time out when there is little concurrent activity on the table.

Prevention and Best Practices

• Set idle_in_transaction_session_timeout (for example 60s) cluster-wide so abandoned transactions cannot hold locks indefinitely.
• Keep transactions short; never hold an open transaction across user input or slow external calls.
• Use a deliberately low lock_timeout in migration tooling and retry DDL with backoff rather than blocking traffic.
• Run schema changes during off-peak windows and split large DDL into smaller, lock-light steps where possible.
• Monitor pg_stat_activity for idle in transaction sessions and alert when any exceeds a threshold.
• For ad-hoc triage, the free incident assistant can turn a lock-timeout log block into the likely blocking session and a suggested action.

Related Errors

• canceling statement due to statement timeout — total run time exceeded statement_timeout, not a lock wait.
• deadlock detected (SQLSTATE 40P01) — a lock cycle, broken automatically by PostgreSQL.
• canceling statement due to user request — the statement was canceled via pg_cancel_backend.
• terminating connection due to idle-in-transaction timeout — the holder side of the same contention.

Frequently Asked Questions

What is the difference between lock_timeout and statement_timeout? lock_timeout caps only the time spent waiting to acquire a lock; once the lock is granted, the statement can run as long as it wants. statement_timeout caps the total execution time of the statement regardless of locks. They are independent and can both be set.

Why did my ALTER TABLE time out when the table looked idle? DDL needs an ACCESS EXCLUSIVE lock, which conflicts even with plain SELECT queries holding an ACCESS SHARE lock. A single long-running read or an idle-in-transaction session that touched the table is enough to block the DDL until lock_timeout fires.

Is it safe to terminate the blocking backend? Terminating with pg_terminate_backend rolls back the blocker’s transaction, which is safe for the database but loses that transaction’s uncommitted work. Prefer it only for sessions that are stuck idle in transaction; otherwise let legitimate transactions finish.

Should I just raise lock_timeout to avoid the error? Raising it trades fast failure for longer blocking. For migrations, a low value plus retry is usually better because it protects production traffic. For OLTP statements, a modest value avoids cascading pile-ups behind one slow holder.

Does a lock timeout roll back my whole transaction? The statement is aborted and the transaction enters a failed state; you must issue ROLLBACK (or your driver does it automatically). Any earlier statements in that transaction are discarded.