Advisory locks

A mutex that isn’t a row

What do you lock when the thing you’re coordinating isn’t a single row — it’s a whole job, a tenant’s rebuild, or the right to be the only server doing something right now? That’s where advisory locks come in.

Every lock so far protected a row. An advisory lock protects nothing in particular — it’s a named latch the application agrees on, keyed by a 64-bit integer (or two 32-bit ints) that you assign meaning to. Postgres just guarantees at most one holder of a given key at a time, cluster-wide. It’s a distributed mutex you get for free with the database you already have.

-- Try to become the single worker for "nightly-billing" (key 42).
SELECT pg_try_advisory_lock(42);
-- returns true  -> you hold it, do the work
-- returns false -> someone else holds it, skip silently
SELECT pg_advisory_unlock(42);   -- release when done

pg_advisory_lock(key) blocks until it can acquire; pg_try_advisory_lock(key) returns immediately with true/false. The non-blocking try_ form is what you want for “only one of us should do this — the rest just skip.”

Session-scoped vs transaction-scoped

This is the distinction that decides whether your lock works in production.

• Session-level (pg_advisory_lock, pg_try_advisory_lock) — the lock is held until you explicitly call pg_advisory_unlock, or the session (database connection) ends. It does not release at COMMIT.
• Transaction-level (pg_advisory_xact_lock, pg_try_advisory_xact_lock) — the lock is automatically released at the end of the transaction (COMMIT or ROLLBACK). There is no unlock function and no way to leak it.

-- Transaction-scoped: released automatically at COMMIT/ROLLBACK. Pooler-safe.
BEGIN;
SELECT pg_try_advisory_xact_lock(42);
-- ... do the guarded work ...
COMMIT;   -- lock gone, guaranteed, even if your code forgot to unlock

Where advisory locks shine

• Leader election / cron dedup — N replicas each pg_try_advisory_lock(key); the one that gets true runs the singleton task, the rest skip. No ZooKeeper, no Redis.
• App-level mutex over a logical resource — serialize a code path keyed by something that isn’t a single row: “rebuild tenant 99’s search index” → pg_advisory_xact_lock(99).
• Serializing a hot path without locking real data rows, avoiding bloat and FK contention.

Versus row locks: a row lock requires the row to exist and protects exactly that tuple; an advisory lock protects an abstract key that may correspond to no row, a future row, or a whole logical operation. Use row locks for data you’re editing; advisory locks for coordination.