vvek

Postgres advisory locks: one-at-a-time work without a lock table

How pg_advisory_xact_lock and pg_try_advisory_lock let you run a job or migration on one worker at a time, and the traps with sessions, pooling and LIMIT.

Sometimes you need exactly one thing to run at a time. One worker should run the nightly cleanup, not three. One deploy should run migrations, not all ten pods that just started. One customer's import should not overlap with itself.

You can build this with a "locks" table, a unique constraint and some cleanup code. Or you can use something Postgres already has: advisory locks. They are locks that the database tracks for you but never applies to any table or row. The meaning is entirely yours.

What an advisory lock is

An advisory lock is a lock on a number. You pick the number, any session can ask for it, and Postgres makes sure only one session holds it exclusively at a time. Postgres does not know what the number stands for. Your application agrees with itself that, say, key 42 means "the nightly cleanup".

The key is one bigint, or a pair of integer values if you want to split it into a namespace and an id. The Postgres documentation on advisory locks describes them as a way to build locking schemes the database cannot enforce itself.

Because they are held in shared memory, they are fast. They do not touch your tables, so they cause no bloat and no vacuum work.

The two flavours

The most important decision is the level.

Level Acquire Released
Transaction pg_advisory_xact_lock(key) Automatically at commit or rollback
Session pg_advisory_lock(key) By pg_advisory_unlock(key), or when the connection ends

Each comes in a waiting form and a pg_try_... form that returns true or false immediately instead of waiting. Shared variants (_shared) also exist, for readers that may overlap with each other but not with a writer.

Transaction-level locks have no unlock function at all. As the docs put it, they "are automatically released at the end of the transaction". That is the reason to prefer them: you cannot forget to release one, and a crash cannot leave one behind.

Tip

Default to pg_try_advisory_xact_lock. If the work fits inside one transaction, the lock's lifetime is exactly the work's lifetime, and cleanup is Postgres's problem.

Pattern 1: run this job on one worker only

Say several workers wake up on a timer and each tries to run the cleanup. Only one should win; the others should skip quietly.

BEGIN;

SELECT pg_try_advisory_xact_lock(42) AS got_it;
-- if got_it is false: ROLLBACK and go back to sleep

DELETE FROM sessions WHERE expires_at < now();

COMMIT; -- the lock goes away here

In application code the shape is the same: begin a transaction, try the lock, return early if you did not get it, do the work, commit. There is no heartbeat, no expiry time to tune and no "stale lock" to clear by hand. If the worker dies, its connection drops, the transaction ends and the lock is free.

Compare this with a row-based claim, as in a job queue in Postgres with SKIP LOCKED. SKIP LOCKED is the right tool when many workers share many items. An advisory lock fits when there is one named thing that must not overlap.

Pattern 2: serialize migrations

When a rollout starts several replicas and each runs migrations on boot, you want the second one to wait for the first, then find nothing left to do.

BEGIN;
SELECT pg_advisory_xact_lock(7001); -- waits its turn
-- check the schema version, apply what is missing
COMMIT;

Here you want the waiting form, not try. Pair it with a lock timeout so a stuck migration does not hold the others forever:

SET LOCAL lock_timeout = '30s';

For the table-level side of migrations, see schema changes without downtime in Postgres.

Pattern 3: one at a time per customer

The key does not have to be a constant. Derive it from an id so each customer gets their own lock:

SELECT pg_try_advisory_xact_lock(hashtext('import:' || $1));

hashtext is a built-in helper that turns text into a 32-bit integer. It works, but it is an internal function, so if you want something you control, compute a stable 64-bit number in your application instead. Either way, remember that two different inputs can collide and share a lock. That is harmless for correctness (they merely take turns) but can surprise you when debugging.

A tidy habit is to use the two-integer form: the first number names the purpose (7001 is "imports"), the second is the id. Then different features cannot clash by accident.

The traps

Session locks ignore transactions. The documentation is direct about it: a session-level lock taken in a transaction that later rolls back is still held after the rollback, and an unlock is effective even if the transaction fails later. If you reach for pg_advisory_lock, release it in a finally block, and expect to think about it.

They stack. Lock the same key twice in one session and you must unlock it twice. A helper that locks "just in case" can leave you holding a lock after you thought you had let go.

Pooling. A session lock belongs to a database connection. If a pooler in transaction mode hands your next statement to a different connection, your unlock may run somewhere that does not hold the lock, and the original connection keeps it. Transaction-level locks avoid this, because the lock and the work share one transaction and therefore one connection. Our post on what PgBouncer's pool modes change explains why that matters.

LIMIT and evaluation order. Do not call a lock function in a query that also filters and limits rows. The docs warn that LIMIT is not guaranteed to be applied before the locking function runs, so you may lock more keys than you meant to. Their safe form puts the limit in a subquery:

SELECT pg_advisory_lock(q.id) FROM
  (SELECT id FROM foo WHERE id > 12345 LIMIT 100) q;

The docs call the unsafe version's leftovers "dangling" locks, still visible but easy to lose track of.

Memory is finite. Advisory locks share a pool sized by max_locks_per_transaction and max_connections. Taking one lock per row of a million-row table is the wrong use; taking one per job or per customer is fine.

Warning

An advisory lock does not protect data. Code that forgets to ask for the lock sails straight through. Put the locking in one shared function, and keep real invariants in constraints too.

Seeing who holds what

Advisory locks appear in the pg_locks view, so you can look when something seems stuck:

SELECT l.pid, l.classid, l.objid, l.granted, a.query, a.state
FROM pg_locks l
JOIN pg_stat_activity a USING (pid)
WHERE l.locktype = 'advisory';

A row with granted = false is a session waiting its turn. The query column often tells you what the holder is doing, which is usually enough to decide whether to wait or to end that session.

When to use something else

If you need a lock to survive a crash, or to span several databases, a database-local lock is the wrong tool. If the real need is "process each item once", use a queue. And if work takes minutes, think about whether holding a transaction open that long is acceptable, since it holds back cleanup of old row versions. For long jobs, a short transaction that records "running" in a table, plus a timeout, is usually kinder to the database.

For the common case, though, the habit is small: begin, pg_try_advisory_xact_lock, return if you lost, do the work, commit. It is easy to read and hard to get wrong.

Sources

Keep reading

A short email when we publish. Confirm once, unsubscribe any time.