Advisory Locks
Application-defined locks on a number you choose: session vs transaction level, try vs blocking, pooler pitfalls, and where they fit (cron dedupe, leader election).
The problem
Sometimes the thing you need to serialize is not a row: a nightly job that must run on one node, a per-tenant import, a migration runner. Advisory locks let the application lock an arbitrary number through PostgreSQL, with no table involved. The database enforces mutual exclusion but attaches no meaning to the key.
How it works
- Key space: one `bigint`, or two `int4` values (useful as namespace + id, such as `(1001, tenant_id)`). The two forms are separate key spaces and never collide with each other. In `pg_locks` they appear with `locktype = 'advisory'`; a single bigint is split across `classid`/`objid` with `objsubid = 1`, a pair uses `objsubid = 2`.
- `pg_advisory_lock(key)` blocks until it gets the lock; `pg_try_advisory_lock(key)` returns `true`/`false` immediately. Use the try form for cron dedupe and leader election, where losing means 'someone else is doing it, skip'.
- Session-level locks (`pg_advisory_lock`, `pg_try_advisory_lock`) are held until you call `pg_advisory_unlock` or the session ends. They ignore transaction boundaries: `COMMIT` and `ROLLBACK` do not release them, and the same session can take the same key several times and must unlock the same number of times.
- Transaction-level locks (`pg_advisory_xact_lock`, `pg_try_advisory_xact_lock`) release automatically at the end of the transaction and have no unlock function. Prefer them whenever the protected work fits in one transaction; they cannot leak.
- Pooling hazard: with a transaction-mode pooler such as PgBouncer, consecutive statements may run on different server connections, so a session-level lock can be taken on one and never unlocked on another. The lock then leaks on a pooled connection until it is recycled. Use xact-level locks, or a dedicated direct connection that lives as long as the lock.
- Leader election and cron dedupe: every instance runs `SELECT pg_try_advisory_lock(7001)` on a long-lived dedicated connection; the one that gets `true` is the leader and keeps that connection open. When it dies or loses its connection, PostgreSQL drops the lock and another instance wins on its next try.
- Choosing keys: use fixed constants for global jobs, `(namespace, id)` pairs for per-entity locks, and keep a registry of namespaces in code. Hashing a string to a bigint works but can collide, and a collision silently serializes unrelated work.
- Limits: advisory locks live in shared memory (the lock table is sized from `max_locks_per_transaction`) and are lost on failover or crash, since they are not replicated. A lock held on the old primary is gone on the new one, so two nodes can believe they lead. They are a convenience for a single database, not a Redlock-style guarantee across failover; add a fencing token if correctness depends on it.
Examples
Transaction-level try-lock with a (namespace, id) key. It disappears at COMMIT; got_it would be false if another session held the same key.
BEGIN;
SELECT pg_try_advisory_xact_lock(1001, 42) AS got_it;
SELECT locktype, classid, objid, objsubid, mode, granted
FROM pg_locks
WHERE locktype = 'advisory' AND pid = pg_backend_pid();
COMMIT;A session-level lock survives COMMIT (still_held is 1). Behind a transaction-mode pooler the matching unlock may land on another connection, which is how these locks leak.
BEGIN;
SELECT pg_advisory_lock(42);
COMMIT;
SELECT count(*) AS still_held
FROM pg_locks
WHERE locktype = 'advisory' AND pid = pg_backend_pid();
SELECT pg_advisory_unlock(42) AS released;Leader election / cron dedupe. The lock belongs to the connection, so keep that connection open for as long as the instance is the leader.
-- skip-exec: needs two sessions
-- Session A:
SELECT pg_try_advisory_lock(7001); -- true: A is the leader
-- Session B:
SELECT pg_try_advisory_lock(7001); -- false: skip the cron run
-- Session A disconnects (or calls pg_advisory_unlock(7001)); then in B the same call returns true: B takes over
SELECT pg_try_advisory_lock(7001);In practice
- Rails (ActiveRecord) — Takes a PostgreSQL advisory lock around migrations so two deploying nodes do not run them concurrently.
- PgBouncer — Its feature compatibility notes list session-level advisory locks as unsupported in transaction pooling mode, for exactly the leak described above.
- GoodJob — A Rails background-job library that uses advisory locks to ensure a job is run by one worker at a time.
- In practice — Advisory locks are popular for 'only one instance runs this cron' and for per-tenant serialization, because they need no extra table or service; the failover caveat is what to remember.
Trade-offs
Pros
- No table, no cleanup rows: the lock vanishes with the transaction or connection
- try variants make skip-if-busy logic one line
- Cheap and fast, since locks live in shared memory
Cons
- Session-level locks leak or misbehave behind transaction poolers
- Not replicated, so they give no mutual exclusion across a failover
- Keys are bare numbers with no ownership or metadata, so collisions and forgotten namespaces are easy