PgBouncer advisory locks in transaction mode: orphaned locks and mysterious contention

Your application takes an advisory lock with pg_advisory_lock(), does its work, releases it, and moves on. Except under load, other parts of the application start blocking on that same lock, or timing out waiting for it. The application is sure it released the lock. PostgreSQL’s lock views, looked at from the wrong place, show nothing. PgBouncer’s metrics are completely healthy: no waiting clients, no queue, normal wait times.

This is not a saturation problem and not a bug in PgBouncer. It is a semantic mismatch between how advisory locks work in PostgreSQL and how transaction pooling reassigns server connections.

What this means

PostgreSQL has two families of advisory locks. Session-level locks (pg_advisory_lock()) are held until they are explicitly unlocked or the session ends, and they survive transaction boundaries. Transaction-level locks (pg_advisory_xact_lock()) are released automatically when the transaction commits or rolls back.

In transaction pooling mode, PgBouncer returns the server connection to the pool the moment a transaction finishes. The next transaction from the same application client will, in general, land on a different server connection. PgBouncer’s own feature compatibility table marks session-level advisory locks as incompatible with transaction pooling, for exactly this reason.

So when application code runs SELECT pg_advisory_lock(42) inside a transaction:

1. PgBouncer assigns server connection C1 to the client.
2. The lock is acquired on the PostgreSQL session behind C1.
3. The transaction commits. PgBouncer returns C1 to the idle pool.
4. The lock is still held on C1. The application believes it is free to proceed, or believes its later pg_advisory_unlock() released it.
5. The next transaction lands on C2. C2 does not hold the lock. Other clients that try to take the lock on their own connections block, because C1 still holds it.

The lock is orphaned: held on a pooled connection that nobody is currently using, invisible to the application that acquired it. Reusing C1 for other transactions does not release it; only the session ending (connection recycled or closed) or an unlock executed on that exact session does. Other clients block on it indefinitely. This is the “mysterious contention”: lock waits with no apparent holder, intermittent and hard to reproduce, with every infrastructure metric green.

flowchart TD
  A[App tx1: pg_advisory_lock] --> B[PgBouncer assigns conn C1]
  B --> C[Lock acquired on C1 session]
  C --> D[COMMIT: C1 returns to idle pool]
  D --> E[Lock still held on C1. App believes it released it]
  F[App tx2: lock or unlock attempt] --> G[PgBouncer assigns conn C2]
  G --> H[C2 does not own the lock: blocks or warns]
  E -->|held until C1 session ends| H

The single-user trap makes this worse. In testing, with one client and low concurrency, the same server connection tends to be reused, so acquire and unlock land on the same session and everything works. The failure only appears under production concurrency, when the pool actually multiplexes.

Common causes

A note on frameworks: some libraries acquire session-level advisory locks behind your back. Rails’ ActiveRecord, for example, uses a session-level advisory lock by default to serialize migrations, which breaks under transaction pooling; Rails lets you disable it with advisory_locks: false in database.yml. Distributed-lock libraries have the same exposure unless they offer a transaction-scoped lock mode. If you see advisory lock contention but your own code never calls pg_advisory_lock, audit the frameworks.

Quick checks

All read-only and safe to run during an incident.

# Confirm the pooling mode per database
psql -h 127.0.0.1 -p 6432 -U pgbouncer pgbouncer -c "SHOW DATABASES;"
# or globally:
psql -h 127.0.0.1 -p 6432 -U pgbouncer pgbouncer -Atc "SHOW CONFIG;" | grep pool_mode

# Confirm PgBouncer looks healthy (this is the trap: it will)
psql -h 127.0.0.1 -p 6432 -U pgbouncer pgbouncer -Atc "SHOW POOLS;"
# cl_waiting = 0, maxwait = 0, normal sv_active: consistent with this failure mode

The decisive check must run against PostgreSQL directly, bypassing PgBouncer. The orphaned lock lives on a pooled server-side session, so query pg_locks on the backend itself:

-- Advisory locks currently held anywhere on the server
SELECT pid, locktype, classid, objid, mode, granted
FROM pg_locks
WHERE locktype = 'advisory';

-- Sessions currently blocked, and what is blocking them
SELECT pid, state, wait_event_type, wait_event,
       now() - query_start AS blocked_for, query
FROM pg_stat_activity
WHERE wait_event IS NOT NULL
ORDER BY blocked_for DESC;

If sessions are waiting on an advisory lock while pg_locks shows it granted to a backend whose state in pg_stat_activity is idle (a pooled connection sitting in PgBouncer’s idle list), you have found the orphan.

# The smoking gun in PostgreSQL logs
grep -i "you don't own a lock" /var/log/postgresql/*.log

How to diagnose it

1. Reproduce the shape of the symptom. Confirm the contention is on an advisory lock, not a row or table lock. In pg_locks, orphaned entries have locktype = 'advisory' and granted = true on a backend that is doing nothing.
2. Check the pooling mode. SHOW DATABASES and SHOW CONFIG on the PgBouncer admin console. If the affected database runs in transaction or statement mode and anything in the request path uses session-level advisory locks, the mechanism matches.
3. Find the orphan. Query pg_locks and pg_stat_activity directly on PostgreSQL. Correlate the lock-holding PID with a backend whose state is idle. That idle backend is one of PgBouncer’s pooled server connections holding the lock for nobody.
4. Confirm the unlock warning. Search PostgreSQL and application logs for you don't own a lock of type ExclusiveLock. Its presence proves acquire and unlock landed on different sessions.
5. Identify the source. Grep the application codebase for pg_advisory_lock. If nothing turns up, audit frameworks: migration locking, distributed-lock libraries, job schedulers. Check when the behavior started against deploy history and any switch from session to transaction pooling.

Metrics and signals to monitor

Note the instrumentation gap: PgBouncer has no signal for this at all. It passes queries through without inspecting them, and its metrics cover pool health, not lock semantics. Detection lives on the PostgreSQL side and in logs.

Fixes

Use transaction-scoped advisory locks

Replace pg_advisory_lock() with pg_advisory_xact_lock() and drop the explicit unlock. The lock is released automatically at commit or rollback, before PgBouncer returns the connection to the pool, so nothing can be orphaned. This is the correct fix when the lock’s scope is naturally “the duration of this unit of work,” which covers most advisory lock uses (job de-duplication, migration guards, critical sections around a write).

Tradeoff: the lock cannot span multiple transactions. If your design genuinely needs a lock held across transaction boundaries on one logical session, this fix does not apply; use session pooling instead.

Route advisory-lock workloads through session pooling

Set pool_mode = session for the specific database or user that uses session-level advisory locks. In session mode the server connection is held for the entire client session, so acquire, hold, and unlock all land on the same PostgreSQL session and the semantics work as the application expects.

Tradeoff: you give up multiplexing for that pool. Each client session pins a server connection for its lifetime, so the pool behaves like direct connections and must be sized accordingly. Scope the override to just the workload that needs it (a dedicated database entry or user) rather than flipping the whole instance.

Clear an existing orphaned lock

The lock is released only when the holding session ends or unlocks. As containment during an incident, two options:

• Targeted: identify the idle backend holding the lock in pg_locks and terminate it with SELECT pg_terminate_backend(<pid>) against PostgreSQL directly. PgBouncer tolerates a pooled server connection disappearing and will open a fresh one. This kills exactly the orphaned session.
• Broad: the RECONNECT admin command on the PgBouncer console closes all server connections and forces new ones, dropping every orphaned lock along with them. This disrupts every pool on the instance, so use it deliberately.

Both are cleanup, not a fix. Tuning server_lifetime or server_idle_timeout down bounds how long a future orphan can survive, but only the code or pool mode change stops orphans from being created.

Fix the framework, not just the query

If a framework is the source, change its configuration rather than wrapping it. For Rails migrations, that is advisory_locks: false plus an operational rule that migrations never run concurrently. For distributed-lock libraries, use their transaction-scoped lock mode if one exists. Do not rely on server_reset_query to paper over this: PgBouncer does not run reset queries between transactions in transaction pooling mode, which is exactly the gap that creates the orphan in the first place.

Prevention

• Audit before switching pool modes. Any move to transaction or statement pooling must come with a code and framework audit for session-dependent features: advisory locks, prepared statements, temp tables, SET variables, LISTEN/NOTIFY. The failure is silent in testing and loud in production.
• Standardize on pg_advisory_xact_lock() anywhere the codebase runs behind PgBouncer. Make the session-level variant a code review flag.
• Test under real concurrency. Single-user and low-concurrency tests pass because connection reuse masks the reassignment. Lock-related integration tests need enough parallel workers to force pool multiplexing.
• Alert on the lock state, not the pool. A periodic check for advisory locks held by idle backends, and log alerting on the unlock warning, catch recurrence in minutes instead of after a user report.
• Document the constraint in the runbook for the service: which pooling mode each database uses and which features that forbids.

How Netdata helps

• Netdata’s PgBouncer collector tracks cl_waiting, maxwait, avg_wait_time, and pool utilization per second, which gives you the negative confirmation fast: the pool is healthy, so the contention is not a pooling capacity problem.
• Correlating those PgBouncer charts with PostgreSQL-side signals (blocked sessions, lock counts, transaction duration) on one dashboard is what turns “mysterious contention” into a two-minute diagnosis instead of an afternoon.
• Per-second granularity catches the intermittent pattern typical of this failure: brief lock waits that appear only under concurrency spikes and vanish before a manual check.
• Historical baselines of PgBouncer metrics let you line the first contention report up against the deploy or config change that introduced transaction pooling.
• Because PgBouncer exposes no error counters and this failure never touches its metrics, Netdata’s value here is the cross-layer view: PgBouncer green, PostgreSQL lock waits rising, application timeouts rising, all on one timeline.

Related guides

• PgBouncer avg_wait_time high: the latency the pool itself is injecting
• PgBouncer capacity planning: runway for pools, clients, and PostgreSQL slots
• How PgBouncer actually works in production: a mental model for operators
• PgBouncer maxwait high: the oldest client waiter and how close it is to timing out
• PgBouncer monitoring checklist: the signals every connection pooler needs
• PgBouncer monitoring maturity model: from survival to expert
• PgBouncer pool exhaustion: clients queue, wait times climb, and the retry cascade
• PgBouncer pool utilization high: sv_active approaching pool_size before clients queue
• PgBouncer query_wait_timeout: clients disconnected after waiting too long for a connection
• PgBouncer reserve pool activation: overflow capacity that hides an undersized pool
• PgBouncer pool_size sizing: matching pool capacity to transaction time and throughput
• PgBouncer sv_idle at zero: no headroom and one slow query from a cascade