PostgreSQL Advisory Lock Implementation Failure Modes

Summary#

PostgreSQL advisory locks are application-defined locks that PostgreSQL stores and arbitrates, but whose correctness depends on the application’s key design, lock ordering, transaction boundaries, and connection-pool behavior. The core implementation risk is confusing session-scoped locks (pg_advisory_lock) with transaction-scoped locks (pg_advisory_xact_lock).

For most short critical sections in pooled applications, especially behind PgBouncer transaction pooling, prefer transaction-scoped advisory locks. Session-scoped locks can survive COMMIT, ROLLBACK, and logical connection return-to-pool events, creating lock leakage, cross-request interference, or apparent “stuck” jobs. PostgreSQL will release session locks when the actual database session ends, but a pooler may keep that backend session alive long after the application believes it is done.

Deadlocks remain possible. Advisory locks participate in PostgreSQL’s normal lock manager behavior, but the application must impose a deterministic ordering when acquiring multiple advisory locks. Recovery logic should treat deadlock aborts, lock timeouts, and failed try-lock attempts as part of the concurrency contract, not as impossible states.

Key Points#

• Advisory locks are advisory, not integrity constraints
• PostgreSQL does not know the business meaning of an advisory-lock key.
• Correctness requires every competing code path to voluntarily use the same key scheme and locking protocol.

• They are useful when row locks or unique constraints do not directly model the resource, e.g. synthetic job keys, external resource serialization, or cross-row coordination.

• Session-scoped locks

• Acquired with functions such as pg_advisory_lock(...).
• Held until explicitly unlocked or until the PostgreSQL session ends.
• They do not follow transaction semantics:
  • a lock acquired inside a transaction remains held after ROLLBACK;
  • an unlock remains effective even if the surrounding transaction later fails.
• A session can acquire the same advisory lock multiple times; each successful acquisition needs a matching unlock before the lock is fully released.

• Failure mode: if application code returns a pooled connection without unlocking, the lock remains attached to the physical backend session.

• Transaction-scoped locks

• Acquired with functions such as pg_advisory_xact_lock(...).
• Automatically released at transaction end, whether COMMIT or ROLLBACK.
• Cannot be explicitly unlocked early.
• Usually safer for request/job critical sections because the lock lifetime is coupled to database transaction lifetime.

• In PgBouncer transaction pooling, this is generally the compatible advisory-lock style because the server connection is returned after the transaction completes.

• Connection-pool leakage

• PgBouncer transaction pooling assigns a server connection only for the duration of a transaction, then returns it to the pool.
• PgBouncer’s own feature matrix says transaction pooling breaks session-based PostgreSQL features by design.
• Session-level advisory locks are unsafe in this model because the lock belongs to the backend session, not to the logical application request.

• Failure modes:

  • later unrelated request inherits a backend session that still holds a session advisory lock;
  • other workers block indefinitely on a lock whose owning application request has already ended;
  • ROLLBACK does not clean up the leaked session lock;
  • logical connection close in the app does not necessarily close the PostgreSQL backend session.

• Deadlock ordering

• Advisory locks can deadlock when multiple code paths acquire more than one key in inconsistent order.
• Use a deterministic total order:
  • normalize lock keys;
  • sort keys before acquisition;
  • acquire all locks in ascending canonical order;
  • avoid conditional “second pass” acquisitions that violate the order.
• PostgreSQL detects deadlocks and aborts one transaction, but the victim is not predictable.

• Deadlock handling should be explicit: catch the failure, roll back, apply bounded retry with jitter, and ensure the critical section is idempotent.

• Try-lock and timeout patterns

• Blocking lock calls are simple but can wait indefinitely unless bounded by application logic or database timeouts.
• pg_try_advisory_lock / pg_try_advisory_xact_lock variants return immediately with success/failure and are useful for work claiming, leader election attempts, or avoiding request pileups.
• For blocking calls, consider lock_timeout or application-side deadlines where waiting forever is unacceptable.

• Treat “could not acquire lock” as a normal branch, not necessarily as an error.

• Crash and retry recovery

• If the PostgreSQL session ends, session-scoped advisory locks are cleaned up by the server.
• If only the application request fails but the pooled backend session survives, session-scoped locks can remain held.
• Transaction-scoped advisory locks are released on transaction abort/rollback, making them better aligned with retryable units of work.
• Retried critical sections must be idempotent:
  • use durable state transitions;
  • record job ownership/fencing tokens where needed;
  • avoid assuming that lock acquisition means no previous attempt partially completed external side effects.

• Advisory locks do not replace durable recovery records for workflows involving external systems.

• SQL evaluation-order pitfall

• PostgreSQL documentation warns that advisory-locking calls inside queries using LIMIT or ordering can acquire more locks than expected if evaluation order is not controlled.
• Safer pattern: first materialize/select the intended key set in a subquery, then apply the advisory-lock function to that bounded set.

• This matters especially for session-level locks, where accidentally acquired locks may become dangling until session end.

• Operational visibility

• Advisory locks appear in pg_locks with lock information visible to database observers.
• Monitoring should distinguish:
  • expected active transaction locks;
  • long-lived session advisory locks;
  • blocked waiters;
  • locks held by idle sessions.
• Alerts should focus on long-lived advisory locks and blocked sessions, especially in applications using connection pools.

Cautions#

• Do not use session-level advisory locks behind PgBouncer transaction pooling unless the design has been explicitly validated and cleanup is guaranteed. Prefer transaction-scoped advisory locks or PgBouncer session pooling for code that depends on session state.
• Do not assume ROLLBACK releases pg_advisory_lock; it releases transaction-scoped locks, not session-scoped ones.
• Do not assume returning a connection to an application pool closes the PostgreSQL session.
• Do not rely on PostgreSQL to enforce the semantic correctness of advisory-lock keys; the lock manager only sees integers.
• Do not acquire multiple advisory locks in application-dependent or query-plan-dependent order.
• Do not treat deadlock aborts as impossible; implement retry contracts.
• Do not use advisory locks as the only recovery mechanism for workflows with external side effects. They are volatile coordination primitives, not durable workflow logs.
• Exact PgBouncer compatibility can vary by pooling mode and version; the high-confidence statement from public docs is that transaction pooling intentionally breaks some session-based features.

Sources#

• https://www.postgresql.org/docs/17/explicit-locking.html
• https://www.postgresql.org/docs/17/functions-admin.html
• https://www.pgbouncer.org/features.html

Related#

• Transactional Outbox Failure Modes: Commit Ordering, Relay Crash Recovery, Deduplication Keys, and Consumer Idempotency Boundaries
• Transactional Outbox Failure Modes: Commit Ordering, Relay Idempotency, Per-Aggregate Ordering, and Poison-Message Recovery
• Distributed Lock Failure Modes: Redlock Split-Brain, Fencing Tokens, GC-Pause Expiry Races, and Re-entrant Acquire Deadlocks

Sagwan Revalidation 2026-09-17T20:50:18Z#

• verdict: ok
• note: 세션/트랜잭션 락과 PgBouncer 주의점은 현재 관행과 일치함