PostgreSQL Lock Wait Timeout Debugger

Threat/Impact Level: HIGH | Exploitability/Downtime Risk: HIGH | Time to Fix: 15–45 mins

TL;DR

• What broke: A PostgreSQL statement hit lock_timeout waiting for another transaction to release a conflicting lock — common during long-running migrations, bulk updates, or connection pool pile-ups.
• How to fix it: Identify the blocking PID via pg_locks + pg_stat_activity, kill or wait out the blocker, then harden your queries with lock_timeout, SKIP LOCKED, or non-blocking DDL patterns.
• Use our Client-Side Sandbox above to paste your failing migration or query and auto-generate the refactored, lock-safe version.

ERROR:  canceling statement due to lock timeout
CONTEXT:  while updating tuple (0,42) in relation "orders"
SQL state: 55P03

How to Fix It

Step 1: Identify the Blocker Right Now

Run this immediately on the primary:

SELECT
  blocked.pid                AS blocked_pid,
  blocked.query              AS blocked_query,
  blocking.pid               AS blocking_pid,
  blocking.query             AS blocking_query,
  blocking.state             AS blocking_state,
  now() - blocking.query_start AS blocking_duration
FROM pg_stat_activity AS blocked
JOIN pg_stat_activity AS blocking
  ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE cardinality(pg_blocking_pids(blocked.pid)) > 0;

If blocking_duration is in minutes and blocking_state is idle in transaction — that process leaked an open transaction. Terminate it:

SELECT pg_terminate_backend(<blocking_pid>);

Basic Fix: Set `lock_timeout` and `statement_timeout` Explicitly

Never rely on the server default (0 = infinite wait). Set at the session or transaction level:

- -- No timeout configured; statement waits forever
- UPDATE orders SET status = 'processed' WHERE id = $1;

+ -- Fail fast; let the application retry with backoff
+ SET LOCAL lock_timeout = '2s';
+ SET LOCAL statement_timeout = '10s';
+ UPDATE orders SET status = 'processed' WHERE id = $1;

Set globally in postgresql.conf or via your RDS/Aurora parameter group as a baseline:

- lock_timeout = 0
- statement_timeout = 0

+ lock_timeout = '5s'
+ statement_timeout = '30s'

Enterprise Best Practice: Non-Blocking Patterns

Pattern 1 — Queue workers with SKIP LOCKED (job queues, order processing):

- -- Causes thundering herd: all workers fight for the same rows
- SELECT * FROM job_queue
-   WHERE status = 'pending'
-   ORDER BY created_at
-   LIMIT 10
-   FOR UPDATE;

+ -- Each worker atomically claims non-contended rows only
+ SELECT * FROM job_queue
+   WHERE status = 'pending'
+   ORDER BY created_at
+   LIMIT 10
+   FOR UPDATE SKIP LOCKED;

Pattern 2 — Zero-downtime DDL migrations (the #1 cause of AccessExclusiveLock incidents):

- -- Blocks ALL reads and writes for the duration of the index build
- ALTER TABLE orders ADD COLUMN processed_at TIMESTAMPTZ;
- CREATE INDEX idx_orders_status ON orders(status);

+ -- Non-blocking index creation; table remains fully live
+ ALTER TABLE orders ADD COLUMN processed_at TIMESTAMPTZ; -- fast, metadata-only in PG 11+
+ CREATE INDEX CONCURRENTLY idx_orders_status ON orders(status);
+ -- Note: CONCURRENTLY cannot run inside a transaction block

Pattern 3 — Advisory locks for application-level mutual exclusion:

- -- Two cron jobs updating the same resource, causing row lock contention
- UPDATE account_balances SET balance = balance - $1 WHERE account_id = $2;

+ -- Acquire advisory lock scoped to the account ID before touching the row
+ SELECT pg_advisory_xact_lock($2);  -- blocks at app level, not row level
+ UPDATE account_balances SET balance = balance - $1 WHERE account_id = $2;
+ -- Advisory lock auto-releases at transaction end

💡 Tired of pasting proprietary infrastructure configs into public LLMs? Generic AI interfaces log your company's ARNs, connection strings, and private keys. StackEngine enforces client-side zero-trust boundaries—the sandbox above executes 100% locally in your browser. Drop your broken manifest above to redact secrets locally and synthesize deterministic patch drafts with your own API key.

(For CI/CD pipeline automation, install via npm i -g stackengine).

# .github/workflows/db-migrate.yml
- name: Lint migration for lock hazards
  run: |
    pip install squawk-cli
    squawk migrations/$(date +%Y%m%d)_*.sql
  # Fails CI on: ADD COLUMN with DEFAULT (PG<11), CREATE INDEX without CONCURRENTLY,
  # ALTER TABLE rewrites, DROP COLUMN, etc.

- DATABASE_URL=postgresql://user:pass@host/db

+ DATABASE_URL=postgresql://user:pass@host/db?options=-c%20lock_timeout%3D3s%20-c%20statement_timeout%3D20s

-- Prometheus query (postgres_exporter) or scheduled alert
SELECT count(*) FROM pg_stat_activity
WHERE wait_event_type = 'Lock'
  AND now() - query_start > interval '5 seconds';
-- Alert threshold: > 3 sessions waiting on locks for > 5s = PagerDuty page

- # No lock_timeout enforced at infrastructure level
- resource "aws_db_parameter_group" "pg" {
-   family = "postgres15"
- }

+ resource "aws_db_parameter_group" "pg" {
+   family = "postgres15"
+   parameter {
+     name  = "lock_timeout"
+     value = "5000"  # milliseconds
+   }
+   parameter {
+     name  = "statement_timeout"
+     value = "30000"
+   }
+ }