How to Fix PostgreSQL Error: Canceling Statement Due to Lock Timeout

The "Canceling statement due to lock timeout" error occurs when a query waits longer than the configured lock_timeout period to acquire a lock on a database resource. This prevents queries from waiting indefinitely for locks to be released.

Impact

This error causes query failures when lock contention is high, affecting application performance and user experience. It's particularly common during maintenance operations, long-running transactions, or high-concurrency scenarios.

Common Causes

Long-running transactions holding locks
Table-level locks from ALTER TABLE or VACUUM FULL
Concurrent UPDATE/DELETE operations on same rows
Schema changes during high traffic
Lock timeout set too low
Missing indexes causing slow queries that hold locks longer
Large batch operations locking many rows

Troubleshooting and Resolution Steps

Check current lock_timeout setting:

-- Check current value
SHOW lock_timeout;

-- Check default (0 = disabled)
SELECT setting, unit FROM pg_settings WHERE name = 'lock_timeout';

Adjust lock_timeout as needed:

-- Set for current session
SET lock_timeout = '5s';
SET lock_timeout = '500ms';
SET lock_timeout = 0;  -- Disable (wait indefinitely)

-- Set for specific transaction
BEGIN;
SET LOCAL lock_timeout = '10s';
-- Your queries here
COMMIT;

-- Set for user
ALTER USER myuser SET lock_timeout = '5s';

-- Set globally (requires reload)
ALTER SYSTEM SET lock_timeout = '5s';
SELECT pg_reload_conf();

Identify blocking queries:

-- Find blocking and blocked queries
SELECT
    blocked_activity.pid AS blocked_pid,
    blocked_activity.query AS blocked_query,
    blocking_activity.pid AS blocking_pid,
    blocking_activity.query AS blocking_query,
    blocking_activity.state,
    blocking_activity.query_start
FROM pg_stat_activity blocked_activity
JOIN pg_locks blocked_locks 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.pid != blocked_locks.pid
JOIN pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

Kill blocking queries if necessary:

-- View active connections
SELECT pid, usename, state, query_start, query
FROM pg_stat_activity
WHERE state = 'active';

-- Terminate specific query
SELECT pg_terminate_backend(12345);  -- Replace with actual PID

-- Cancel query (more graceful)
SELECT pg_cancel_backend(12345);

Use NOWAIT to fail immediately:

-- Instead of waiting, fail fast if lock unavailable
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE NOWAIT;
-- Error immediately if row is locked

-- Or use SKIP LOCKED to process unlocked rows
SELECT * FROM queue_items
WHERE processed = false
FOR UPDATE SKIP LOCKED
LIMIT 10;

Optimize transaction scope:

-- BAD: Long transaction holding locks
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
-- ... application processing ...
-- ... external API calls ...
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

-- GOOD: Minimize lock duration
-- Do processing first
-- ... application logic ...

BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

Use advisory locks for custom coordination:

-- Try to acquire lock, fail if unavailable
SELECT pg_try_advisory_lock(12345);  -- Returns true if acquired, false if not

-- Perform work if lock acquired
-- ...

-- Release lock
SELECT pg_advisory_unlock(12345);

Handle lock timeouts in application:

# Python with psycopg2
from psycopg2 import OperationalError
import time

def update_with_retry(account_id, amount, max_retries=3):
    for attempt in range(max_retries):
        try:
            conn = get_connection()
            cursor = conn.cursor()
            cursor.execute("SET lock_timeout = '2s'")

            cursor.execute(
                "UPDATE accounts SET balance = balance + %s WHERE id = %s",
                (amount, account_id)
            )

            conn.commit()
            return True

        except OperationalError as e:
            conn.rollback()
            if 'lock timeout' in str(e) or 'canceling statement' in str(e):
                if attempt < max_retries - 1:
                    time.sleep(0.5 * (2 ** attempt))  # Exponential backoff
                    continue
            raise

    return False

Concurrent index creation to avoid locks:

-- Regular index creation locks table
CREATE INDEX idx_name ON table_name(column);  -- Locks table

-- Concurrent index doesn't lock table for writes
CREATE INDEX CONCURRENTLY idx_name ON table_name(column);

Monitor lock waits:

-- Enable lock wait logging
ALTER SYSTEM SET log_lock_waits = on;
ALTER SYSTEM SET deadlock_timeout = '1s';
SELECT pg_reload_conf();

-- Query to see lock waits
SELECT
    pid,
    usename,
    pg_blocking_pids(pid) AS blocked_by,
    query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

Additional Information

Lock timeout only affects waiting for locks, not query execution time (use statement_timeout for that)
Default lock_timeout is 0 (disabled), meaning queries wait indefinitely
Lock timeouts help prevent cascading performance issues
Combine with statement_timeout for comprehensive timeout protection
Monitor lock wait events to identify bottlenecks
Consider connection pooling to manage concurrent access

Frequently Asked Questions

Q: What's the difference between lock_timeout and statement_timeout?
A: lock_timeout limits how long to wait for a lock, while statement_timeout limits total query execution time.

Q: What's a reasonable lock_timeout value?
A: It depends on your application. Start with 5-10 seconds for web applications. Longer for batch jobs.

Q: Can I set different lock_timeout for different queries?
A: Yes, use SET LOCAL lock_timeout = '5s' within a transaction for query-specific timeouts.

Q: Does lock_timeout apply to all lock types?
A: Yes, it applies to row locks, table locks, and other database locks.

Q: Should I always use NOWAIT?
A: NOWAIT is useful when you want immediate feedback, but it can increase retry logic complexity. Use for queue processing or optional operations.

Q: How do I debug frequent lock timeouts?
A: Enable log_lock_waits, identify blocking queries, analyze query patterns, add indexes, and optimize transaction duration.

Q: What happens if both lock_timeout and statement_timeout are set?
A: Whichever timeout is reached first will cancel the query.