Introduction

PostgreSQL lock wait timeout errors occur when a transaction waits too long to acquire a lock held by another transaction. Unlike MySQL, PostgreSQL doesn't have a built-in innodb_lock_wait_timeout - instead, you configure statement_timeout or lock_timeout to prevent indefinite waiting. When these timeouts are hit, PostgreSQL returns canceling statement due to statement timeout or canceling statement due to lock timeout errors. Lock contention indicates concurrency issues where transactions conflict over table rows, causing queuing, degraded performance, and eventual timeouts. This guide provides production-proven troubleshooting for PostgreSQL lock scenarios including lock analysis from pg_locks and pg_stat_activity, blocking query detection, deadlock prevention, index optimization to reduce lock scope, transaction isolation tuning, and monitoring strategies.

Symptoms

  • Application logs show canceling statement due to statement timeout errors
  • Application logs show canceling statement due to lock timeout errors
  • Database queries that normally complete quickly start hanging or timing out
  • pg_stat_activity shows queries in idle in transaction state for extended periods
  • Multiple transactions waiting on same table/row locks
  • Application reports "could not obtain lock on row" errors
  • Lock wait times increase during peak concurrent write operations
  • pg_locks shows growing number of waiting locks

Common Causes

  • Long-running transactions holding locks while waiting for I/O or user input
  • idle in transaction sessions never committing/rolling back (application bug)
  • Missing indexes causing table scans with excessive row locks
  • Foreign key constraints creating implicit locks on parent tables
  • High concurrency with many transactions updating same hot rows
  • Batch operations locking large portions of tables
  • Transaction isolation level too strict (SERIALIZABLE)
  • Advisory locks held indefinitely by abandoned sessions
  • Table-level locks (ACCESS EXCLUSIVE) from DDL operations
  • Replication conflicts on standby servers

Step-by-Step Fix

### 1. Confirm lock wait timeout diagnosis

Check current locks and waiting queries:

```sql -- Check for blocking queries SELECT blocked_locks.pid AS blocked_pid, blocked_activity.usename AS blocked_user, blocked_activity.query AS blocked_query, blocking_locks.pid AS blocking_pid, blocking_activity.usename AS blocking_user, blocking_activity.query AS blocking_query, EXTRACT(EPOCH FROM (NOW() - blocked_activity.query_start)) AS wait_seconds FROM pg_catalog.pg_locks blocked_locks JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid JOIN pg_catalog.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_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid WHERE NOT blocked_locks.granted ORDER BY wait_seconds DESC;

-- Check all locks in the database SELECT l.locktype, l.mode, l.granted, l.pid, a.usename, a.application_name, a.state, a.query, EXTRACT(EPOCH FROM (NOW() - a.query_start)) AS duration_seconds, CASE WHEN l.relation IS NOT NULL THEN relname ELSE NULL END AS table_name FROM pg_locks l LEFT JOIN pg_stat_activity a ON a.pid = l.pid LEFT JOIN pg_class c ON c.oid = l.relation WHERE l.database = (SELECT oid FROM pg_database WHERE datname = current_database()) ORDER BY duration_seconds DESC;

-- Check lock wait statistics (PostgreSQL 15+) SELECT * FROM pg_lock_timeout; ```

Check timeout configuration:

```sql -- Check current timeout settings SHOW statement_timeout; SHOW lock_timeout; SHOW idle_in_transaction_session_timeout;

-- Default values: -- statement_timeout = 0 (disabled) -- lock_timeout = 0 (disabled) -- idle_in_transaction_session_timeout = 0 (disabled)

-- Check if timeouts are set at database or role level SELECT rolname, rolconfig FROM pg_roles WHERE rolconfig IS NOT NULL; ```

### 2. Identify blocking sessions

Find sessions holding locks and blocking others:

```sql -- Find all blocking sessions (PostgreSQL 9.6+) SELECT bl.pid AS blocking_pid, bl.usename AS blocking_user, bl.application_name AS blocking_app, bl.state AS blocking_state, bl.query AS blocking_query, bl.query_start AS blocking_query_start, EXTRACT(EPOCH FROM (NOW() - bl.query_start)) AS blocking_duration_seconds, array_agg(ba.pid) AS blocked_pids, count(*) AS blocked_count FROM pg_stat_activity bl JOIN pg_locks bl_locks ON bl.pid = bl_locks.pid AND bl_locks.granted JOIN pg_locks wa_locks ON bl_locks.locktype = wa_locks.locktype AND bl_locks.database IS NOT DISTINCT FROM wa_locks.database AND bl_locks.relation IS NOT DISTINCT FROM wa_locks.relation AND bl_locks.page IS NOT DISTINCT FROM wa_locks.page AND bl_locks.tuple IS NOT DISTINCT FROM wa_locks.tuple AND bl_locks.virtualxid IS NOT DISTINCT FROM wa_locks.virtualxid AND bl_locks.transactionid IS NOT DISTINCT FROM wa_locks.transactionid JOIN pg_stat_activity ba ON wa_locks.pid = ba.pid AND NOT wa_locks.granted GROUP BY bl.pid, bl.usename, bl.application_name, bl.state, bl.query, bl.query_start ORDER BY blocked_count DESC;

-- Quick check: how many sessions are waiting? SELECT count(*) FILTER (WHERE NOT granted) AS waiting_locks, count(*) FILTER (WHERE granted) AS granted_locks, count(*) AS total_locks FROM pg_locks WHERE database = (SELECT oid FROM pg_database WHERE datname = current_database()); ```

Identify idle in transaction sessions:

```sql -- Find sessions idle in transaction (common lock culprit) SELECT pid, usename, application_name, client_addr, state, state_change, query_start, EXTRACT(EPOCH FROM (NOW() - query_start)) AS idle_seconds, query FROM pg_stat_activity WHERE state = 'idle in transaction' ORDER BY query_start;

-- Sessions idle in transaction for more than 5 minutes SELECT pid, usename, application_name, EXTRACT(EPOCH FROM (NOW() - query_start)) AS idle_seconds, query FROM pg_stat_activity WHERE state = 'idle in transaction' AND NOW() - query_start > interval '5 minutes'; ```

### 3. Terminate blocking sessions (immediate relief)

Kill sessions causing blocking:

```sql -- Terminate a specific blocking session SELECT pg_terminate_backend(<blocking_pid>);

-- Terminate all sessions idle in transaction for more than 30 minutes SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE state = 'idle in transaction' AND NOW() - query_start > interval '30 minutes';

-- Terminate all sessions holding locks on a specific table SELECT pg_terminate_backend(pid) FROM pg_locks JOIN pg_stat_activity ON pg_locks.pid = pg_stat_activity.pid WHERE relation = 'your_table_name'::regclass AND granted;

-- WARNING: This terminates ALL non-system sessions -- SELECT pg_terminate_backend(pid) -- FROM pg_stat_activity -- WHERE pid != pg_backend_pid() -- AND state != 'active';

-- Check if termination was successful SELECT pid, state, query FROM pg_stat_activity WHERE pid = <blocking_pid>; ```

Graceful termination approach:

```sql -- First, try to cancel the query (allows cleanup) SELECT pg_cancel_backend(<pid>);

-- Wait a few seconds, check if it resolved SELECT pid, state FROM pg_stat_activity WHERE pid = <pid>;

-- If still blocking, terminate the backend SELECT pg_terminate_backend(<pid>); ```

### 4. Configure appropriate timeouts

Set statement and lock timeouts:

```sql -- Set timeout for current session SET statement_timeout = '30s'; SET lock_timeout = '10s'; SET idle_in_transaction_session_timeout = '5min';

-- Set timeout for specific role ALTER ROLE app_user SET statement_timeout = '30s'; ALTER ROLE app_user SET lock_timeout = '10s'; ALTER ROLE app_user SET idle_in_transaction_session_timeout = '5min';

-- Set timeout at database level ALTER DATABASE mydb SET statement_timeout = '30s'; ALTER DATABASE mydb SET lock_timeout = '10s';

-- Or in postgresql.conf (requires reload) # statement_timeout = 30s # lock_timeout = 10s # idle_in_transaction_session_timeout = 5min

-- Timeout guidelines: -- statement_timeout: 30s for OLTP, 5-30min for reports -- lock_timeout: 5-10s for row operations, 30s for DDL -- idle_in_transaction_session_timeout: 5-10min ```

Timeout configuration in connection strings:

```python # Python psycopg2 conn = psycopg2.connect( "dbname=mydb user=app_user options='-c statement_timeout=30000 -c lock_timeout=10000'" )

# Or after connection conn.autocommit = False with conn.cursor() as cur: cur.execute("SET statement_timeout = '30s'") cur.execute("SET lock_timeout = '10s'")

# SQLAlchemy engine = create_engine( 'postgresql://user:pass@host/dbname', connect_args={'options': '-c statement_timeout=30000 -c lock_timeout=10000'} )

# Java JDBC # jdbc:postgresql://host/dbname?options=-c statement_timeout%3D30000 -c lock_timeout%3D10000

# Node.js pg const client = new Client({ connection: { options: '-c statement_timeout=30000 -c lock_timeout=10000' } }); ```

### 5. Identify and add missing indexes

Missing indexes cause table scans with excessive locking:

```sql -- Find queries with most lock wait time (PostgreSQL 13+) SELECT query, calls, total_exec_time, rows, shared_blks_hit, shared_blks_read FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20;

-- Find sequential scans on large tables (may indicate missing indexes) SELECT schemaname, relname, seq_scan, seq_tup_read, idx_scan, idx_tup_fetch, CASE WHEN seq_scan > 0 THEN seq_tup_read / seq_scan ELSE 0 END AS avg_seq_rows FROM pg_stat_user_tables WHERE seq_scan > 0 ORDER BY seq_tup_read DESC LIMIT 20;

-- Check for queries doing full table scans EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM your_table WHERE your_column = 'value';

-- Look for: -- Seq Scan (bad) -> needs index -- Index Scan (good) -- Index Only Scan (best)

-- Add missing index CREATE INDEX idx_your_column ON your_table(your_column);

-- For foreign key columns (common source of locks) CREATE INDEX idx_fk_column ON child_table(foreign_key_column); ```

Foreign key index importance:

```sql -- Find foreign keys without indexes SELECT conname AS constraint_name, conrelid::regclass AS table_name, a.attname AS column_name, 'CREATE INDEX idx_' || conrelid::regclass || '_' || a.attname || ' ON ' || conrelid::regclass || '(' || a.attname || ');' AS create_index FROM pg_constraint c JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = ANY(c.conkey) WHERE c.contype = 'f' AND NOT EXISTS ( SELECT 1 FROM pg_index i WHERE i.indrelid = c.conrelid AND a.attnum = ANY(i.indkey) );

-- Foreign keys without indexes cause table scans during: -- - UPDATE/DELETE on parent table -- - Referential integrity checks -- These scans acquire many row locks, increasing contention ```

### 6. Optimize transaction patterns

Reduce lock contention with better transaction design:

```sql -- WRONG: Long transaction with user interaction BEGIN; UPDATE accounts SET balance = balance - 100 WHERE id = 1; -- ... application waits for user confirmation ... -- ... lock held for 30 seconds ... COMMIT;

-- CORRECT: Short transaction BEGIN; UPDATE accounts SET balance = balance - 100 WHERE id = 1; COMMIT; -- Then ask for user confirmation -- Then start new transaction for next step

-- WRONG: Large batch in single transaction BEGIN; UPDATE orders SET status = 'processed' WHERE status = 'pending'; -- Millions of rows COMMIT;

-- CORRECT: Batch in small chunks DO $$ DECLARE batch_size INTEGER := 1000; processed INTEGER; BEGIN LOOP BEGIN; UPDATE orders SET status = 'processed' WHERE status = 'pending' LIMIT batch_size; GET DIAGNOSTICS processed = ROW_COUNT; COMMIT;

EXIT WHEN processed = 0; PERFORM pg_sleep(0.1); -- Small delay to reduce contention END LOOP; END $$; ```

Use SELECT ... FOR UPDATE SKIP LOCKED:

```sql -- WRONG: Queue processing with lock contention BEGIN; SELECT * FROM queue WHERE processed = false FOR UPDATE; -- Blocks other workers UPDATE queue SET processed = true WHERE id = 1; COMMIT;

-- CORRECT: Skip locked rows (multiple workers can process in parallel) BEGIN; SELECT * FROM queue WHERE processed = false FOR UPDATE SKIP LOCKED -- Skip rows locked by other workers LIMIT 1; UPDATE queue SET processed = true WHERE id = 1; COMMIT;

-- Or using NOWAIT (fail immediately if locked) SELECT * FROM queue WHERE processed = false FOR UPDATE NOWAIT; -- Returns error if row is locked ```

### 7. Check for deadlock patterns

Detect and prevent deadlocks:

```sql -- Check recent deadlocks from server log -- PostgreSQL logs deadlock information: -- LOG: deadlock detected -- DETAIL: Process 12345 waits for ShareLock on transaction 67890 -- Process 54321 waits for ShareLock on transaction 09876

-- Query pg_stat_database for deadlock statistics SELECT datname, deadlocks, conflicts, temp_files, temp_bytes FROM pg_stat_database ORDER BY deadlocks DESC;

-- Common deadlock pattern: AB-BA -- Transaction 1: UPDATE A -> UPDATE B -- Transaction 2: UPDATE B -> UPDATE A

-- Fix: Always access tables in consistent order -- Application code should enforce ordering ```

Deadlock prevention in application code:

```python # Python with consistent ordering def transfer_funds(from_account, to_account, amount): # Sort account IDs to ensure consistent lock order account_ids = sorted([from_account, to_account])

with conn.cursor() as cur: # Always lock accounts in same order cur.execute( "UPDATE accounts SET balance = balance - %s WHERE id = %s", (amount, account_ids[0]) ) cur.execute( "UPDATE accounts SET balance = balance + %s WHERE id = %s", (amount, account_ids[1]) ) conn.commit()

# Java with explicit lock ordering @Transactional public void transferFunds(Long fromAccount, Long toAccount, BigDecimal amount) { List<Long> sortedIds = Arrays.asList(fromAccount, toAccount); Collections.sort(sortedIds);

accountRepository.lockAndDebit(sortedIds.get(0), amount); accountRepository.lockAndCredit(sortedIds.get(1), amount); } ```

### 8. Tune isolation level

Adjust isolation level to reduce locking:

```sql -- Check current isolation level SELECT current_setting('transaction_isolation');

-- Isolation levels and lock behavior: -- READ COMMITTED (default): Row-level locks, no range locks -- REPEATABLE READ: Row-level locks + predicate locks (SERIALIZABLE in PG) -- SERIALIZABLE: Full serialization, most locks, potential serialization failures

-- Set isolation level for session SET transaction_isolation = 'READ COMMITTED';

-- Set at connection level ALTER ROLE app_user SET default_transaction_isolation = 'READ COMMITTED';

-- For specific operations that can tolerate dirty reads -- PostgreSQL doesn't support dirty reads, but READ COMMITTED is closest SET transaction_isolation = 'READ COMMITTED'; BEGIN; -- Your query here COMMIT;

-- When to use each level: -- READ COMMITTED: Default for most OLTP workloads -- REPEATABLE READ: When you need consistent reads within transaction -- SERIALIZABLE: When absolute consistency required (expect serialization failures) ```

### 9. Monitor lock contention proactively

Set up lock monitoring:

```sql -- Create lock monitoring view CREATE OR REPLACE VIEW lock_monitor AS SELECT bl.pid AS blocked_pid, bl.usename AS blocked_user, bl.query AS blocked_query, bl.query_start AS blocked_query_start, EXTRACT(EPOCH FROM (NOW() - bl.query_start)) AS wait_seconds, al.pid AS blocker_pid, al.usename AS blocker_user, al.query AS blocker_query, al.state AS blocker_state, l.locktype, l.mode, CASE WHEN l.relation IS NOT NULL THEN c.relname ELSE NULL END AS table_name FROM pg_stat_activity bl JOIN pg_locks bl_locks ON bl.pid = bl_locks.pid AND NOT bl_locks.granted JOIN pg_locks al_locks ON al_locks.locktype = bl_locks.locktype AND al_locks.database IS NOT DISTINCT FROM bl_locks.database AND al_locks.relation IS NOT DISTINCT FROM bl_locks.relation AND al_locks.page IS NOT DISTINCT FROM bl_locks.page AND al_locks.tuple IS NOT DISTINCT FROM bl_locks.tuple AND al_locks.virtualxid IS NOT DISTINCT FROM bl_locks.virtualxid AND al_locks.transactionid IS NOT DISTINCT FROM bl_locks.transactionid AND al_locks.pid != bl_locks.pid AND al_locks.granted JOIN pg_stat_activity al ON al.pid = al_locks.pid JOIN pg_locks l ON l.pid = bl.pid AND NOT l.granted LEFT JOIN pg_class c ON c.oid = l.relation ORDER BY wait_seconds DESC;

-- Query for monitoring SELECT * FROM lock_monitor WHERE wait_seconds > 5; ```

Prometheus metrics with postgres_exporter:

```yaml # docker-compose.yml version: '3' services: postgres_exporter: image: prometheuscommunity/postgres-exporter:latest ports: - "9187:9187" environment: DATA_SOURCE_NAME: "postgresql://user:pass@postgres:5432/dbname?sslmode=disable"

prometheus: image: prom/prometheus:latest volumes: - ./prometheus.yml:/etc/prometheus/prometheus.yml ports: - "9090:9090" ```

Prometheus alerting rules:

```yaml # alerting_rules.yml groups: - name: postgresql_locks rules: - alert: PostgreSQLLockWaitHigh expr: rate(pg_stat_activity_wait_event_count{wait_event_type="Lock"}[5m]) > 10 for: 5m labels: severity: warning annotations: summary: "PostgreSQL lock wait events occurring frequently" description: "{{ $value }} lock wait events per second"

  • alert: PostgreSQLBlockingQueries
  • expr: pg_locks_waiting > 5
  • for: 5m
  • labels:
  • severity: warning
  • annotations:
  • summary: "PostgreSQL has {{ $value }} blocked queries"
  • description: "Multiple queries waiting for locks on {{ $labels.instance }}"
  • alert: PostgreSQLIdleInTransaction
  • expr: pg_stat_activity_state{state="idleintransaction"} > 0
  • for: 10m
  • labels:
  • severity: warning
  • annotations:
  • summary: "PostgreSQL sessions idle in transaction"
  • description: "{{ $value }} sessions idle in transaction for extended period"
  • alert: PostgreSQLDeadlockDetected
  • expr: increase(pg_stat_database_deadlocks[5m]) > 0
  • for: 5m
  • labels:
  • severity: critical
  • annotations:
  • summary: "PostgreSQL deadlock detected"
  • description: "{{ $value }} deadlocks on {{ $labels.datname }}"
  • `

### 10. Handle DDL lock contention

Table-level locks from DDL operations:

```sql -- Check for table-level locks SELECT l.pid, a.usename, a.application_name, l.mode, l.granted, c.relname AS table_name, EXTRACT(EPOCH FROM (NOW() - a.query_start)) AS duration_seconds, a.query FROM pg_locks l JOIN pg_stat_activity a ON l.pid = a.pid JOIN pg_class c ON l.relation = c.oid WHERE l.locktype = 'relation' ORDER BY duration_seconds DESC;

-- ACCESS EXCLUSIVE lock blocks everything (from ALTER TABLE, DROP TABLE, etc.) -- If DDL is blocking, either: -- 1. Wait for DDL to complete -- 2. Terminate DDL if it's stuck

-- Terminate DDL session (use carefully) SELECT pg_terminate_backend(<ddl_pid>);

-- For schema migrations, use lock-friendly patterns: -- 1. CREATE TABLE new_table (instead of ALTER existing) -- 2. Copy data in batches -- 3. Rename tables atomically -- 4. DROP old table

-- Example: Add column without table lock -- Instead of: ALTER TABLE users ADD COLUMN new_col TEXT;

-- Do: CREATE TABLE users_new (LIKE users INCLUDING ALL); ALTER TABLE users_new ADD COLUMN new_col TEXT DEFAULT ''; INSERT INTO users_new SELECT *, '' FROM users; -- Batch if large ALTER INDEX users_pkey RENAME TO users_old_pkey; ALTER TABLE users RENAME TO users_old; ALTER TABLE users_new RENAME TO users; DROP TABLE users_old; -- Can be done later ```

### 11. Configure autovacuum for lock prevention

Proper autovacuum reduces lock contention:

```sql -- Check autovacuum status SELECT datname, last_autovacuum, last_autoanalyze FROM pg_stat_database ORDER BY last_autovacuum;

-- Check table bloat (high bloat = more locks) SELECT schemaname, relname, n_dead_tup, n_live_tup, CASE WHEN n_live_tup > 0 THEN round(100.0 * n_dead_tup / n_live_tup, 2) ELSE 0 END AS dead_ratio_percent, last_autovacuum, last_autoanalyze FROM pg_stat_user_tables WHERE n_dead_tup > 1000 ORDER BY n_dead_tup DESC;

-- Aggressive autovacuum for high-traffic tables ALTER TABLE high_traffic_table SET ( autovacuum_vacuum_threshold = 50, autovacuum_analyze_threshold = 50, autovacuum_vacuum_scale_factor = 0.01, autovacuum_analyze_scale_factor = 0.01 );

-- Global autovacuum tuning (postgresql.conf) # autovacuum = on # autovacuum_max_workers = 3 # autovacuum_naptime = 1min # autovacuum_vacuum_threshold = 50 # autovacuum_analyze_threshold = 50 # autovacuum_vacuum_scale_factor = 0.1 # autovacuum_analyze_scale_factor = 0.05 ```

Prevention

  • Set statement_timeout and lock_timeout for all database connections
  • Configure idle_in_transaction_session_timeout to catch application bugs
  • Add indexes on foreign key columns to reduce lock scope
  • Keep transactions short - commit as soon as possible
  • Use SELECT ... FOR UPDATE SKIP LOCKED for queue processing
  • Process large updates in small batches with delays
  • Monitor pg_stat_activity for idle in transaction sessions
  • Set up alerting for lock wait events and deadlocks
  • Review and tune autovacuum settings for high-traffic tables
  • Document lock timeout requirements for each application
  • **canceling statement due to statement timeout**: Query exceeded statement_timeout
  • **canceling statement due to lock timeout**: Could not acquire lock within lock_timeout
  • **deadlock detected**: Circular dependency between transactions
  • **could not obtain lock on row in relation**: Specific row lock failed
  • **tuple is already locked**: Row locked by another transaction