Fix PostgreSQL Table Lock Wait Timeout

What's Actually Happening

PostgreSQL sessions wait for table locks when another session holds an incompatible lock. When the wait exceeds lock_timeout, the waiting session aborts with a timeout error. Long-running operations or uncommitted transactions cause blocking.

The Error You'll See

Query timeout:

```bash $ psql -U app_user -d mydb

ERROR: lock wait timeout exceeded DETAIL: Session 12345 blocked by session 12344 acquiring lock on table "orders" HINT: Increase lock_timeout or resolve blocking session ```

Application error:

PSQLException: ERROR: lock wait timeout exceeded
SQL State: 55P03
Error Code: 0
Query: UPDATE orders SET status = 'completed'

PostgreSQL log:

```bash $ tail /var/log/postgresql/postgresql-14-main.log

2026-04-16 00:05:00 UTC [12345] ERROR: lock wait timeout exceeded 2026-04-16 00:05:00 UTC [12345] DETAIL: Process 12345 blocked by process 12344 2026-04-16 00:05:00 UTC [12345] HINT: Check pg_locks and pg_stat_activity for blocking sessions ```

Why This Happens

1. 1.Long transaction - Uncommitted transaction holding locks
2. 2.Exclusive lock - ALTER TABLE or VACUUM FULL blocking reads
3. 3.Lock order - Sessions acquiring locks in conflicting order
4. 4.Idle transaction - Transaction started but not committed
5. 5.Heavy operations - Bulk updates locking many rows
6. 6.Lock timeout too short - Insufficient wait time allowed

Step 1: Check Current Locks

```bash # View all locks psql -U postgres -c " SELECT pid, locktype, relation::regclass AS table, mode, granted FROM pg_locks WHERE granted = false ORDER BY pid; "

# Check blocking and waiting sessions psql -U postgres -c " SELECT blocked.pid AS blocked_pid, blocked.query AS blocked_query, blocking.pid AS blocking_pid, blocking.query AS blocking_query, blocked.mode AS blocked_mode, blocking.mode AS blocking_mode FROM pg_locks blocked JOIN pg_locks blocking ON blocked.relation = blocking.relation AND blocked.granted = false AND blocking.granted = true AND blocked.pid != blocking.pid JOIN pg_stat_activity blocked_act ON blocked.pid = blocked_act.pid JOIN pg_stat_activity blocking_act ON blocking.pid = blocking_act.pid; "

# Simplified blocking view psql -U postgres -c " SELECT pid, state, query_start, now() - query_start AS duration, query FROM pg_stat_activity WHERE state != 'idle' ORDER BY query_start; " ```

Step 2: Identify Blocking Session

```bash # Find the session holding the lock psql -U postgres -c " SELECT locktype, relation::regclass, mode, pid FROM pg_locks WHERE granted = true AND relation = 'orders'::regclass; "

# Get details of blocking session psql -U postgres -c " SELECT pid, usename, application_name, client_addr, state, query_start, query FROM pg_stat_activity WHERE pid = 12344; "

# Check if session is idle in transaction psql -U postgres -c " SELECT pid, usename, state, query_start, now() - query_start AS idle_duration FROM pg_stat_activity WHERE state = 'idle in transaction' ORDER BY idle_duration DESC; " ```

Step 3: Terminate Blocking Session

```bash # Cancel the blocking query psql -U postgres -c "SELECT pg_cancel_backend(12344);"

# Force terminate if cancel doesn't work psql -U postgres -c "SELECT pg_terminate_backend(12344);"

# Terminate all idle transactions older than 5 minutes psql -U postgres -c " SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE state = 'idle in transaction' AND now() - query_start > interval '5 minutes'; "

# Terminate specific user's sessions psql -U postgres -c " SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE usename = 'problem_user' AND pid != pg_backend_pid(); "

# Check session was terminated psql -U postgres -c " SELECT pid, state FROM pg_stat_activity WHERE pid = 12344; " # Should return empty or state='idle' ```

Step 4: Increase Lock Timeout

```bash # Check current lock_timeout psql -U postgres -c "SHOW lock_timeout;" # Default: '0' (infinite wait)

# Set session-level lock timeout psql -U postgres -c "SET lock_timeout = '30s';"

# Set database-level default psql -U postgres -c "ALTER DATABASE mydb SET lock_timeout = '30s';"

# Set user-level default psql -U postgres -c "ALTER USER app_user SET lock_timeout = '30s';"

# In postgresql.conf for system default sudo vim /etc/postgresql/14/main/postgresql.conf # Add or modify: lock_timeout = 30000 # 30 seconds in milliseconds

# Apply without restart psql -U postgres -c "ALTER SYSTEM SET lock_timeout = '30s';" psql -U postgres -c "SELECT pg_reload_conf();"

# Verify new setting psql -U postgres -c "SHOW lock_timeout;" ```

Step 5: Commit or Rollback Idle Transactions

```bash # Find idle transactions psql -U postgres -c " SELECT pid, usename, query_start, state FROM pg_stat_activity WHERE state = 'idle in transaction'; "

# If you control the session, commit or rollback # From the blocking session: psql -U postgres -c "COMMIT;" # Or psql -U postgres -c "ROLLBACK;"

# Application code fix - always commit/rollback # Python example: conn = psycopg2.connect(dsn) try: cursor = conn.cursor() cursor.execute("UPDATE orders SET status = 'done'") conn.commit() # Always commit! finally: conn.close()

# Java example: try (Connection conn = dataSource.getConnection()) { conn.setAutoCommit(false); // operations conn.commit(); // Explicit commit } catch (SQLException e) { conn.rollback(); // Rollback on error } ```

Step 6: Use Advisory Locks for Coordination

```sql -- Advisory locks don't block table operations -- Use for application-level coordination

-- Acquire advisory lock (session level) SELECT pg_advisory_lock(12345);

-- Check if lock is held SELECT pg_advisory_lock_status(12345);

-- Release lock SELECT pg_advisory_unlock(12345);

-- Transaction-level advisory lock (auto-released on commit/rollback) SELECT pg_advisory_xact_lock(12345);

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

-- Non-blocking pattern: BEGIN; -- Try to acquire lock SELECT pg_try_advisory_xact_lock(12345) AS acquired; -- If acquired = true, proceed -- If acquired = false, skip or wait -- Lock released automatically on COMMIT/ROLLBACK COMMIT; ```

Step 7: Optimize Heavy Operations

```sql -- VACUUM FULL locks the entire table -- Use VACUUM instead (doesn't lock)

-- WRONG: Locks table for entire operation VACUUM FULL orders;

-- CORRECT: Non-blocking vacuum VACUUM orders;

-- For vacuum full, do during maintenance window -- Or use pg_repack extension for online reorganization

-- ALTER TABLE with lock timeout SET lock_timeout = '5s'; ALTER TABLE orders ADD COLUMN new_column TEXT; -- If timeout, retry later

-- Batch updates to avoid long locks -- WRONG: Update all rows in one transaction UPDATE orders SET status = 'completed';

-- CORRECT: Batch updates BEGIN; UPDATE orders SET status = 'completed' WHERE id BETWEEN 1 AND 10000; COMMIT;

BEGIN; UPDATE orders SET status = 'completed' WHERE id BETWEEN 10001 AND 20000; COMMIT;

-- Use smaller transactions -- Process in batches of 10000 rows ```

Step 8: Configure Idle Transaction Timeout

```bash # Set idle_in_transaction_timeout # Sessions idle in transaction for longer are terminated

# In postgresql.conf idle_in_transaction_timeout = 60000 # 60 seconds

# Apply psql -U postgres -c "ALTER SYSTEM SET idle_in_transaction_timeout = '60s';" psql -U postgres -c "SELECT pg_reload_conf();"

# Verify psql -U postgres -c "SHOW idle_in_transaction_timeout;"

# This automatically kills idle transactions # Prevents accidental blocking from forgotten sessions ```

Step 9: Use NOWAIT for Immediate Fail

```sql -- Use NOWAIT to immediately fail if lock not available

-- Try to lock row, fail immediately if locked SELECT * FROM orders WHERE id = 1 FOR UPDATE NOWAIT; -- ERROR: could not obtain lock on row

-- Use SKIP LOCKED to skip locked rows SELECT * FROM orders WHERE id = 1 FOR UPDATE SKIP LOCKED; -- Returns row if not locked, nothing if locked

-- Queue processing pattern: BEGIN; SELECT * FROM tasks WHERE status = 'pending' FOR UPDATE SKIP LOCKED LIMIT 10; -- Process only unlocked tasks UPDATE tasks SET status = 'processing' WHERE id = ...; COMMIT;

-- This pattern prevents lock contention in queues ```

Step 10: Monitor Lock Contention

```bash # Create lock monitoring view psql -U postgres -c " CREATE OR REPLACE VIEW lock_monitor AS SELECT blocked.pid AS blocked_pid, blocked_act.usename AS blocked_user, blocked_act.query AS blocked_query, blocking.pid AS blocking_pid, blocking_act.usename AS blocking_user, blocking_act.query AS blocking_query, blocked.relation::regclass AS table, blocked.mode AS blocked_mode, now() - blocked_act.query_start AS wait_duration FROM pg_locks blocked JOIN pg_locks blocking ON blocked.relation = blocking.relation AND blocked.granted = false AND blocking.granted = true AND blocked.pid != blocking.pid JOIN pg_stat_activity blocked_act ON blocked.pid = blocked_act.pid JOIN pg_stat_activity blocking_act ON blocking.pid = blocking_act.pid; "

# Query the view psql -U postgres -c "SELECT * FROM lock_monitor;"

# Set up periodic monitoring cat << 'EOF' > /usr/local/bin/check_locks.sh #!/bin/bash BLOCKED=$(psql -U postgres -t -c " SELECT count(*) FROM pg_locks WHERE granted = false; ")

if [ "$BLOCKED" -gt 5 ]; then echo "ALERT: $BLOCKED sessions waiting for locks" psql -U postgres -c "SELECT * FROM lock_monitor;" | \ mail -s "PostgreSQL Lock Alert" admin@company.com fi EOF

chmod +x /usr/local/bin/check_locks.sh

# Add to cron echo "*/2 * * * * root /usr/local/bin/check_locks.sh" > /etc/cron.d/pg-locks ```

PostgreSQL Lock Types Reference

Verify the Fix

```bash # After resolving blocking session or timeout

# 1. Check no blocked sessions psql -U postgres -c " SELECT count(*) FROM pg_locks WHERE granted = false; " # Should be 0

# 2. Verify query can proceed psql -U postgres -c "UPDATE orders SET status = 'test' WHERE id = 1;" # Should succeed without timeout

# 3. Check lock_monitor view empty psql -U postgres -c "SELECT * FROM lock_monitor;" # Should return no rows

# 4. Verify lock_timeout setting psql -U postgres -c "SHOW lock_timeout;"

# 5. Check for idle transactions psql -U postgres -c " SELECT pid FROM pg_stat_activity WHERE state = 'idle in transaction' AND now() - query_start > interval '5 minutes'; " # Should be empty

# 6. Test application operations # Application queries should no longer timeout ```

Related Issues

• [Fix PostgreSQL Query Timeout](/articles/fix-postgresql-query-timeout)
• [Fix PostgreSQL Deadlock Detected](/articles/fix-postgresql-deadlock-detected)
• [Fix PostgreSQL Transaction Abort](/articles/fix-postgresql-transaction-abort)