How to fix Canceling statement due to lock timeout in PostgreSQL

PostgreSQLINTERMEDIATEMEDIUMUpdated Dec 29, 2025· 313 views

PostgreSQL cancels statements that exceed the lock_timeout waiting for locks on database objects. This error indicates lock contention—adjust the timeout, resolve blocking queries, or optimize transaction isolation to fix it.

Quick fix

Fixes Canceling statement due to lock timeout

SHOW lock_timeout;
  1. Verify lock_timeout is enabled
  2. Identify blocking queries with pg_locks
  3. Terminate blocking transactions if safe
  4. Adjust lock_timeout at session level
postgresql — error
SHOW lock_timeout;
Canceling statement due to lock timeout

What this error means

The "Canceling statement due to lock timeout" error occurs when a PostgreSQL statement fails to acquire a required lock (on a table, row, index, or other object) within the time specified by the lock_timeout parameter. When enabled, lock_timeout aborts any statement that waits longer than the configured duration attempting to acquire a lock, returning this error to the client. This is a protective mechanism to prevent statements from blocking indefinitely on locked resources and creating application hangs or cascading failures.

Typical symptoms

  • Statements fail with "Canceling statement due to lock timeout" error message
  • Database operations timeout while waiting for locks to be released
  • DML operations (INSERT, UPDATE, DELETE) fail under concurrent load
  • Application receives cancellation errors during concurrent database modifications
  • Lock contention spikes correlate with error occurrences
  • Some queries succeed while others timeout depending on timing and concurrency

Common causes

  1. lock_timeout parameter is set too low relative to lock acquisition time in your workload
  2. Long-running transactions hold locks on heavily-accessed tables
  3. DDL operations (ALTER TABLE, CREATE INDEX) blocking concurrent DML
  4. High concurrency with overlapping transactions modifying same rows or tables
  5. Missing indexes forcing full table scans that hold locks longer
  6. Row-level locking conflicts in heavily-updated tables
  7. SELECT FOR UPDATE queries waiting for rows held by other transactions

How to fix "Canceling statement due to lock timeout"

1Verify lock_timeout is enabled

Check if lock_timeout is set on your connection:

sql
SHOW lock_timeout;

If the output is 0 or 0ms, lock_timeout is disabled and this error shouldn't occur. If it's set to a specific value (e.g., 5s), that's your configured lock wait limit.

2Identify blocking queries with pg_locks

Find which queries are holding locks and blocking others:

sql
SELECT
  blocked_locks.pid AS blocked_pid,
  blocked_activity.usename AS blocked_user,
  blocked_activity.application_name AS blocked_app,
  blocking_locks.pid AS blocking_pid,
  blocking_activity.usename AS blocking_user,
  blocking_activity.application_name AS blocking_app,
  blocked_activity.query AS blocked_query,
  blocking_activity.query AS blocking_query,
  blocked_activity.state AS blocked_state,
  blocking_activity.state AS blocking_state
FROM pg_locks blocked_locks
JOIN pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype
  AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
  AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
  AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
  AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
  AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
  AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
  AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
  AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
  AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
  AND blocking_locks.pid != blocked_locks.pid
JOIN pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted
ORDER BY blocked_activity.query_start;

This shows you exactly which transaction is blocking which, what they're doing, and how long they've been running.

3Terminate blocking transactions if safe

If blocking transactions are long-running and no longer needed, terminate them:

sql
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE pid != pg_backend_pid()
  AND query_start < NOW() - INTERVAL '10 minutes'
  AND state != 'idle';

Be cautious: terminating a transaction in the middle of a modification can leave data in an inconsistent state if not properly handled. Only terminate queries you own or have explicit permission to terminate.

4Adjust lock_timeout at session level

If your lock_timeout is too aggressive for legitimate blocking scenarios, increase it at the session level:

sql
SET lock_timeout = '30s';

Or set it for a specific application user/role:

sql
ALTER ROLE myapp SET lock_timeout = '30s';

Typical values are 5-30 seconds. Never set lock_timeout globally in postgresql.conf—always use session or role-level configuration so different workloads can have appropriate timeouts.

5Optimize transaction isolation and locking

Restructure transactions to minimize lock duration and contention:

1. Use READ COMMITTED isolation (default):

sql
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ COMMITTED;

2. Lock rows only when needed:

sql
-- Explicit locking for rows you will modify
SELECT * FROM users WHERE id = 42 FOR UPDATE;
UPDATE users SET balance = balance - 100 WHERE id = 42;
COMMIT;

3. Minimize transaction duration:
- Move non-critical logic outside transactions
- Fetch data before entering a transaction
- Commit frequently in loops

4. Lock in consistent order across transactions:
If multiple transactions lock the same tables, always lock them in the same order to prevent deadlock chains.

6Add indexes on frequently locked columns

Indexes reduce lock duration by enabling efficient queries:

sql
-- If you're doing SELECT FOR UPDATE on frequently-queried columns
CREATE INDEX idx_users_status ON users(status) WHERE status = 'active';

-- If you're updating rows with WHERE conditions
CREATE INDEX idx_orders_customer_date ON orders(customer_id, created_at);

Full table scans hold locks longer because they must scan every row. Targeted indexes allow the query to find and lock only the necessary rows.

Advanced notes

The "Canceling statement due to lock timeout" error differs from "Lock timeout exceeded"—the latter is part of PostgreSQL's error code standard (ERRCODE_LOCK_NOT_AVAILABLE, SQLSTATE 55P03), while the former is the user-facing message when lock_timeout fires. Unlike deadlock_timeout (which detects circular lock dependencies, default 1s) and statement_timeout (which limits total query execution time), lock_timeout specifically limits time spent waiting for lock acquisition. If both lock_timeout and statement_timeout are configured, whichever fires first will cancel the statement. On heavily concurrent systems, monitor with SELECT COUNT(*) FROM pg_locks WHERE NOT granted to detect rising lock contention before timeouts occur. For DDL operations requiring ACCESS EXCLUSIVE locks, set a shorter lock_timeout (2-10s) to prevent a single blocked ALTER TABLE from queuing dozens of transactions and causing cascading timeouts. In high-throughput OLTP systems, consider using connection poolers (pgBouncer, PgPool) configured with TRANSACTION mode to reduce idle connections and overall lock contention.

Frequently asked questions

What does "Canceling statement due to lock timeout" mean?
The "Canceling statement due to lock timeout" error occurs when a PostgreSQL statement fails to acquire a required lock (on a table, row, index, or other object) within the time specified by the lock_timeout parameter. When enabled, lock_timeout aborts any statement that waits longer than the configured duration attempting to acquire a lock, returning this error to the client. This is a protective mechanism to prevent statements from blocking indefinitely on locked resources and creating application hangs or cascading failures.
How do I fix "Canceling statement due to lock timeout"?
PostgreSQL cancels statements that exceed the lock_timeout waiting for locks on database objects. This error indicates lock contention—adjust the timeout, resolve blocking queries, or optimize transaction isolation to fix it.
What causes "Canceling statement due to lock timeout"?
lock_timeout parameter is set too low relative to lock acquisition time in your workload Long-running transactions hold locks on heavily-accessed tables DDL operations (ALTER TABLE, CREATE INDEX) blocking concurrent DML
Is the "Canceling statement due to lock timeout" error serious?
It is a medium-severity error — more of a blocker than a risk. The steps above resolve it.

Official resources & further reading