Lock Monitoring Gaps: Deadlocks Not Logged

Scenario

An application reports intermittent ERROR: deadlock detected errors in its logs, but the DBA finds no corresponding entries in the PostgreSQL server log. The log_lock_waits setting is off, so long lock waits are invisible. The deadlock_timeout is set to 10 seconds (default), meaning PostgreSQL only detects deadlocks after 10 seconds — long enough for requests to time out first. The team has no visibility into which queries are blocking each other.

How to Identify

Conditions:

• Application logs show deadlock detected errors but PostgreSQL log has no lock-related entries
• log_lock_waits = off in pg_settings
• deadlock_timeout = 10s (default) — applications time out before deadlock is detected
• pg_locks shows granted = false entries (waiting for locks) but no alerting
• pg_stat_activity shows sessions with wait_event_type = 'Lock'

Analysis Steps

-- Check lock-related settings:
SELECT name, setting, unit, context
FROM pg_settings
WHERE name IN ('log_lock_waits', 'deadlock_timeout', 'lock_timeout', 'statement_timeout')
ORDER BY name;

-- Find currently waiting sessions:
SELECT pid, usename, wait_event_type, wait_event, left(query, 80) AS query
FROM pg_stat_activity
WHERE wait_event_type = 'Lock';

-- Find active locks and which are waiting:
SELECT
    locktype, relation::regclass, mode, granted,
    pid, transactionid
FROM pg_locks
WHERE NOT granted
ORDER BY pid;

-- Join pg_locks with pg_stat_activity to see full picture:
SELECT
    l.pid,
    a.usename,
    l.locktype,
    l.relation::regclass AS table_name,
    l.mode,
    l.granted,
    left(a.query, 80) AS query
FROM pg_locks l
JOIN pg_stat_activity a USING (pid)
WHERE NOT l.granted
ORDER BY l.pid;

Pitfalls

• log_lock_waits = off (default) means PostgreSQL never logs lock waits — the DBA is flying blind during locking incidents. Always enable this in production.
• deadlock_timeout = 1s is recommended for most production workloads — the default 10s means deadlocks linger for 10 seconds before being detected.
• lock_timeout (per session/statement) is different from deadlock_timeout (cluster-wide deadlock detection interval). Setting lock_timeout = '5s' causes a statement to error immediately if it waits more than 5s for a lock.
• pg_locks only shows current locks — historical data requires log_lock_waits = on which writes to server logs.
• Advisory locks (locktype = 'advisory') appear in pg_locks and can block just like relation locks. Often forgotten in application lock analysis.
• Row-level locks (from SELECT FOR UPDATE) appear as relation locks in pg_locks but the specific row isn’t shown — use pg_blocking_pids() to trace them.

Resolution Approach

Enable log_lock_waits = on and reduce deadlock_timeout to 1 second. Set lock_timeout at the application or role level to prevent indefinite lock waits. Use pg_blocking_pids() for real-time lock chain diagnosis.