Advisory Lock Leakage (Dangling Locks)

Scenario

An application uses pg_advisory_lock() for distributed job coordination — each worker acquires a lock keyed to its job type before processing. After a new deployment, a bug causes workers to never call pg_advisory_unlock(). The advisory locks persist on the idle sessions. New workers attempt pg_advisory_lock() on the same keys but block indefinitely. The job processing system grinds to a halt while the database itself is healthy and under no load.

How to Identify

Conditions:

• pg_locks shows rows with locktype = 'advisory' held by sessions that have no active query
• The PIDs owning the advisory locks appear in pg_stat_activity as idle or idle in transaction
• New workers attempting pg_advisory_lock() on the same key show wait_event_type = 'Lock'
• Advisory lock count in pg_locks grows over time with each deployment or worker restart
• No corresponding pg_advisory_unlock() calls appear in application logs

Analysis Steps

-- 1. List all advisory locks currently held
SELECT
    l.pid,
    l.classid,
    l.objid,
    l.objsubid,
    l.mode,
    l.granted,
    a.usename,
    a.state,
    a.application_name,
    now() - a.state_change   AS state_duration,
    left(a.query, 80)        AS last_query
FROM   pg_locks l
JOIN   pg_stat_activity a ON a.pid = l.pid
WHERE  l.locktype = 'advisory'
ORDER  BY l.granted DESC, a.state_change;

-- 2. Find advisory locks held by idle sessions (dangling locks)
SELECT
    l.pid,
    l.classid,
    l.objid,
    l.mode,
    a.state,
    a.client_addr,
    now() - a.state_change  AS idle_duration
FROM   pg_locks l
JOIN   pg_stat_activity a ON a.pid = l.pid
WHERE  l.locktype = 'advisory'
  AND  a.state IN ('idle', 'idle in transaction');

-- 3. Find sessions blocked trying to acquire an advisory lock
SELECT
    blocked.pid,
    blocked.usename,
    left(blocked.query, 80)     AS blocked_query,
    now() - blocked.query_start AS wait_duration
FROM   pg_stat_activity blocked
WHERE  blocked.wait_event_type = 'Lock'
  AND  EXISTS (
      SELECT 1 FROM pg_locks
      WHERE  pid = blocked.pid
        AND  locktype = 'advisory'
        AND  granted = false
  );

-- 4. Count advisory locks per lock key to spot accumulation
SELECT classid, objid, count(*) AS lock_count
FROM   pg_locks
WHERE  locktype = 'advisory'
GROUP  BY classid, objid
ORDER  BY lock_count DESC;

Pitfalls

• pg_advisory_lock() is session-level — it does NOT auto-release at transaction end. The lock persists until pg_advisory_unlock() is explicitly called or the session disconnects. This is the most common source of lock leakage.
• pg_advisory_xact_lock() is transaction-level — it auto-releases when the transaction ends (commit or rollback). Prefer it unless session-level semantics are explicitly required.
• pg_advisory_lock() used inside a query with LIMIT can acquire the lock without ever releasing it if the LIMIT clause stops the scan before the unlock row is reached. This is a classic silent bug.
• Never confuse session-level and transaction-level advisory locks — pg_advisory_unlock() cannot release a transaction-level lock; it must be called for session-level locks only.
• Session idle timeout does not help if the session is still alive (just idle). The locks survive as long as the connection exists.

Resolution Approach

Immediately release dangling advisory locks by terminating the sessions that hold them using pg_terminate_backend(). Then audit the application code to replace all pg_advisory_lock() calls with pg_advisory_xact_lock() so locks auto-release with the transaction. Where session-level locks are genuinely needed, add explicit pg_advisory_unlock() in error handlers and FINALLY blocks. Set idle_in_transaction_session_timeout and connection-level timeouts to auto-close stale sessions.