In PostgreSQL, how do session-level and transaction-level advisory locks differ, and why does a session-level one leak behind PgBouncer's transaction pooling?
answer
- application-chosen keys, server-run queue
- one kind ignores ROLLBACK
- the xact variant has no unlock
- the backend owns it, not the client
- locktype advisory in pg_locks
basics
~20 sA session-level advisory lock is held until pg_advisory_unlock or disconnect, even across ROLLBACK; a transaction-level one ends with its transaction. Behind transaction pooling the unlock can reach another server connection, so the lock stays held.
solid answer
~50 sAdvisory locks are locks on application-chosen keys (one `bigint` or two `integer`s, local to a database) that PostgreSQL queues and tracks like any other lock but never takes on its own. `pg_advisory_lock` and `pg_try_advisory_lock` are session-level: the lock survives `COMMIT` and `ROLLBACK`, is re-entrant with a counter, and lasts until a matching `pg_advisory_unlock` or until the session ends. `pg_advisory_xact_lock` and `pg_try_advisory_xact_lock` are released automatically at transaction end and have no unlock function. Behind PgBouncer in transaction mode, each transaction may run on a different server connection: the lock is taken on one, the later unlock runs on another and returns `false` with a warning, and the lock stays with a backend other clients will reuse. So a singleton job, such as the email service's bounce-reconciliation sweep, should call `pg_try_advisory_xact_lock` inside the transaction that does the work.
code
sql · 9 lines-- runs every minute on every app replica; only one does the work
BEGIN;
SELECT pg_try_advisory_xact_lock(4207001) AS got_it; -- key reserved for the bounce sweep
-- if got_it is false: COMMIT and skip this run
UPDATE email_outbox
SET status = 'bounced'
WHERE status = 'sent'
AND id IN (SELECT email_id FROM email_events WHERE kind = 'hard_bounce');
COMMIT; -- the advisory lock is released here, also on ROLLBACKgo deeper
Recall that advisory locks are application-chosen keys, that the session variant must be unlocked explicitly, and that the xact variant ends with its transaction.
Explain rollback behaviour, re-entrancy counting, the try variants returning booleans, and how advisory locks appear in pg_locks with locktype advisory.
Show the pooler leak end to end, how you would find the holding backend, and why transaction-level locks are the default choice behind transaction pooling.
Discuss when an advisory lock is the right coordination tool versus a lease row or an external coordinator, given failover, pooling and observability.
## What an advisory lock is in PostgreSQL An **advisory lock** is a lock on a number the application chooses. PostgreSQL never takes one on its own and never checks that code respects it, hence "advisory", but it queues, grants and reports it through the same lock manager as table locks. Keys are either one `bigint` or two `integer` values, and they are **local to a database**. The manual lists the advantages over a flag column: faster, no table bloat, and automatic cleanup when the session ends. Typical uses are "only one replica runs this job" and mutual exclusion on something that is not a row. ## Session level versus transaction level | | Session level | Transaction level | |---|---|---| | Acquire, waiting | `pg_advisory_lock(key)` | `pg_advisory_xact_lock(key)` | | Acquire, try | `pg_try_advisory_lock(key)` returns boolean | `pg_try_advisory_xact_lock(key)` returns boolean | | Released by | `pg_advisory_unlock(key)` or session end | transaction end only | | On `ROLLBACK` | **still held** | released | | Explicit unlock | yes | none exists | Each function has a `_shared` variant, such as `pg_advisory_lock_shared`, that lets several holders share a key. Further rules from the manual: - Session-level locks are **re-entrant with a count**: taking the same key three times needs three unlocks before another session can have it. - A session that already holds a key always succeeds on another request for it, even while others are queued. - Session-level and transaction-level requests for the same key block each other normally. - `pg_advisory_unlock` on a key the session does not hold returns **false** and raises an SQL **warning**, not an error. ## The singleton-job pattern The email service runs a bounce-reconciliation sweep every minute on every app replica. Each run opens a transaction and calls `pg_try_advisory_xact_lock` with a key reserved for the sweep. The replica that gets `true` does the work; the others get `false`, end their empty transaction and skip the run. Commit or rollback releases the lock, so a crash cannot leave it behind. ## How a session lock leaks behind a pooler PgBouncer in **transaction pooling** mode gives a client a server connection only for the length of one transaction. A session-level lock belongs to the **server connection**, the backend process, not to the client: 1. Replica A runs `SELECT pg_try_advisory_lock(42)` on server connection S1 and gets `true`; the transaction ends and S1 returns to the pool. 2. Replica A later runs `SELECT pg_advisory_unlock(42)`, which lands on S2. It returns `false` with a warning; S1 still holds key 42. 3. Replica B is handed S1 and calls `pg_try_advisory_lock(42)`. Because S1 already holds the key, it gets `true`, and two replicas now believe they own the job. 4. Every client that tries key 42 on any other server connection gets `false` until some client on S1 unlocks it or S1 is closed. The cleanup that would fix this, `DISCARD ALL`, includes `SELECT pg_advisory_unlock_all()`, and it is PgBouncer's default `server_reset_query`. But PgBouncer runs that reset only in session pooling unless `server_reset_query_always` is set. The fix is to use **transaction-level** locks behind transaction pooling, or to give the job a direct or session-pooled connection. Other session state that breaks under transaction pooling is PgBouncer's own subject. ## Finding and bounding advisory locks - `pg_locks` lists them with `locktype = 'advisory'` and mode `ExclusiveLock`, or `ShareLock` for the shared variants. A `bigint` key is split: high half in `classid`, low half in `objid`, `objsubid = 1`; reassemble it with `(classid::bigint << 32) | objid::bigint`. Two-integer keys show as `classid`, `objid` and `objsubid = 2`. - Advisory and regular locks share one shared-memory lock table sized from `max_locks_per_transaction` (default `64`, changeable only at server start) and `max_connections`. Exhaust it and lock requests fail with `out of shared memory` and a hint to raise `max_locks_per_transaction`. - Beware `SELECT pg_advisory_lock(id) FROM email_outbox WHERE id > 1000 LIMIT 100`: the `LIMIT` is not guaranteed to apply before the function runs, so extra keys may be locked and left dangling. Apply the `LIMIT` in a subquery and call the function in the outer query. - Migration tools that take an advisory lock around schema changes are the migration-runner leaf's subject.
- How do you find which PostgreSQL backend holds a leaked advisory lock?Query `pg_locks` for `locktype = 'advisory'`; for a `bigint` key `objsubid` is 1 and the key is `(classid::bigint << 32) | objid::bigint`. The `pid` is the holding backend; join it to `pg_stat_activity` to see its client and state. Behind a pooler that backend is a pooled server connection, so closing that connection is often the only way to release the lock.
- Can advisory locks exhaust PostgreSQL's lock table?Yes. Advisory and regular locks share one shared-memory lock table sized from `max_locks_per_transaction` (default 64, restart required) and `max_connections`. Taking one lock per row over a large result can fill it, after which lock requests fail with `out of shared memory` and a hint to raise `max_locks_per_transaction`, and the server cannot grant other locks either.
- Why is SELECT pg_advisory_lock(id) FROM email_outbox WHERE id > 1000 LIMIT 100 dangerous?PostgreSQL does not guarantee the `LIMIT` is applied before the function runs, so it may lock more keys than the 100 rows returned. The extras are session-level locks the application does not know about and never unlocks, dangling in `pg_locks` until the session ends. Put the `LIMIT` in a subquery and call `pg_advisory_lock` in the outer query.
- ROLLBACK releases every lock, session-level advisory locks included
- PostgreSQL takes advisory locks automatically on rows you update
- pg_advisory_xact_lock must be paired with a pg_advisory_unlock call
- Taking the same advisory key twice in one session deadlocks that session
- Under transaction pooling, a lock and its unlock always reach the same backend