Postgres Advisory Locks Explained (With Examples)

Sometimes you need to make sure only one process does something at a time, across all your app servers and workers: run tonight's billing job once, rebuild one customer's report without two workers colliding, apply migrations from a single deploy. You could add Redis and a locking library. If you already have Postgres, advisory locks do the job with no new infrastructure.

What advisory locks are

Normal Postgres locks protect rows and tables and are taken automatically. Advisory locks are different: they lock an arbitrary number that means whatever your application decides. Postgres doesn't care what it represents; it just guarantees only one holder (for exclusive locks) at a time, across every connection to the database.

"Advisory" means they're cooperative — they only work if all your code checks the same lock before doing the work.

The functions

Function	Behaviour
`pg_advisory_lock(key)`	Wait until the lock is free, then take it (session-level)
`pg_try_advisory_lock(key)`	Take it if free and return `true`; otherwise return `false` immediately
`pg_advisory_unlock(key)`	Release a session-level lock
`pg_advisory_xact_lock(key)`	Wait, then hold until the transaction ends
`pg_try_advisory_xact_lock(key)`	Try, transaction-level

Keys are a single bigint, or a pair of ints (handy as namespace + ID). Shared (_shared) variants also exist for reader/writer patterns.

Session-level vs transaction-level

Session-level locks last until you explicitly unlock them or the connection closes. Lock twice, and you must unlock twice. Forget to unlock and the lock stays held as long as that connection lives.
Transaction-level (_xact_) locks release automatically at COMMIT or ROLLBACK. You can't forget to release them. Prefer these whenever the protected work fits in a transaction.

Pattern 1: a cron job that runs exactly once

You have three app servers, each running the same scheduler. Without coordination, the nightly job runs three times. (Cron expressions explained.)

const NIGHTLY_BILLING = 72001; // any constant unique to this job

await db.transaction(async (tx) => {
  const { rows } = await tx.query(
    "SELECT pg_try_advisory_xact_lock($1) AS acquired",
    [NIGHTLY_BILLING]
  );
  if (!rows[0].acquired) return; // another server is running it

  await runNightlyBilling(tx);
}); // lock released on commit

The first server to arrive gets the lock and runs the job; the others skip. Using a try-lock means nobody queues up waiting.

(For long jobs, holding one transaction open for the whole run has costs — it holds back VACUUM. For those, use a session lock on a dedicated connection and unlock in a finally.)

Pattern 2: a per-entity mutex

Serialise work on one customer, while different customers proceed in parallel. Use the two-int form as namespace + ID:

SELECT pg_advisory_xact_lock(42, $1);   -- 42 = "recalculate invoices" namespace, $1 = customer id

Two requests for customer 9 run one after the other; customer 9 and customer 10 run concurrently. This also prevents a class of deadlocks, because all work on that customer queues behind one lock instead of contending row by row.

Pattern 3: string keys

Locks take numbers. For string identifiers, hash them:

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

hashtext returns a 32-bit integer, so collisions between different strings are possible (rare, and a collision only makes two unrelated operations wait for each other, not corrupt anything). For lower collision risk, hash to 64 bits in your application and pass a bigint.

The connection pooler trap

This is the big one. Session-level advisory locks belong to a database connection. With a connection pool — and especially PgBouncer in transaction mode, or Supabase's transaction-mode pooler — consecutive statements from your app may run on different server connections:

you take a session lock on connection A,
your unlock runs on connection B and does nothing,
connection A goes back to the pool still holding the lock, and some unrelated request inherits it.

Rules:

Through a transaction-mode pooler, use only transaction-level (_xact_) locks, inside a transaction.
For session-level locks, use a direct connection (or session-mode pooling) and keep the same client for lock, work and unlock.

(Postgres connection pooling explains pooling modes.)

Seeing who holds what

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

For a single-bigint key, the value is split across classid (high 32 bits) and objid (low 32 bits); for the two-int form, they're the two ints.

Advisory locks vs alternatives

SELECT ... FOR UPDATE locks real rows — better when the thing you're protecting is a row.
FOR UPDATE SKIP LOCKED is the right tool for job queues, letting many workers each grab different jobs. (Postgres as a job queue.)
Redis-based locks make sense if you don't use Postgres or need locks independent of the database's availability. (Redis: when you need it.)

Advisory locks fit "one at a time for this concept" — a job, a tenant, a migration. Many migration tools use them for exactly that: only one deploy applies migrations at a time.

Pitfalls

Forgetting session unlocks — prefer _xact_ locks.
Key collisions between features — keep a registry of constants, or use namespaces.
Long holds — a lock held for an hour blocks everything waiting on it; prefer try-locks or timeouts (SET lock_timeout) for waiters.
Assuming they survive failover — locks live in the server's memory; a restart or failover releases them all.

The summary

Advisory locks lock application-defined numbers, coordinated by Postgres across all connections.
Prefer transaction-level locks; they release automatically.
Use try-locks for "run once" jobs and namespace + ID keys for per-entity mutexes.
Through transaction-mode poolers, use only transaction-level locks.

EasySpawn runs your app, workers and PostgreSQL on the same server, so Claude Code can test locking by actually running two workers at once against your real database. See how it works or join the waitlist.

Postgres Advisory Locks: Distributed Locking Without Redis