Advisory Locks — Application-Level Coordination Through the Database

An advisory lock is a lock on an integer the application invents, attached to no row, table, or object Postgres knows about. It solves a gap row locks cannot reach: mutual exclusion over a stretch of code — a singleton cron job, a leader election, a non-idempotent call to an external API — where there is no piece of data to lock in the first place.

Intermediate transactions advisory-lock session-level-lock transaction-level-lock mutual-exclusion distributed-lock pgbouncer Created 2026-08-15 · Updated 2026-08-23

Key Components

Advisory lock: A lock on an application-invented 64-bit integer key. Postgres answers only one question — is any session currently holding this key? — and does not enforce the lock against any data. It works only because the code agrees to check it.
Transaction-level lock: Taken with pg_advisory_xact_lock. Released automatically at the end of the transaction — commit or rollback — with no unlock call.
Session-level lock: Taken with pg_advisory_lock. Held until an explicit unlock call or the session ends. It does not follow transaction boundaries — a ROLLBACK does not release it.
Try (non-blocking) function: The pg_try_advisory_* family. Returns true/false immediately instead of waiting for the key to free up.
Shared vs exclusive mode: Shared mode conflicts only with exclusive mode, never with another shared holder — the same rule row-level shared/exclusive locks follow.

Concrete Example

A nightly job emails every customer their invoice. It runs on three app servers for redundancy, each with a cron entry at 02:00. Every server wakes at the same second:

02:00:00  server-1 wakes → SELECT * FROM customers → sends 8,000 emails
02:00:00  server-2 wakes → SELECT * FROM customers → sends 8,000 emails
02:00:00  server-3 wakes → SELECT * FROM customers → sends 8,000 emails

Every customer gets three invoices. Row locking cannot fix this: SELECT ... FOR UPDATE needs a row, and there is no row representing "the act of sending" — the thing that must become exclusive is a piece of application code, not data.

An advisory lock has no such requirement. All three servers run the same line against the same Postgres at 02:00:00:

BEGIN;
SELECT pg_try_advisory_xact_lock(9001) AS got_it;

Server	`got_it`	Behaviour
server-1	true	proceeds — sends the 8,000 emails
server-2	false	logs "already running", exits
server-3	false	logs "already running", exits

with db.transaction():
    if not db.query("SELECT pg_try_advisory_xact_lock(9001)").scalar():
        log.info("invoice run already in progress, skipping")
        return
    send_all_invoices()          # only ever one server is inside this block
# COMMIT — lock released automatically

The number 9001 means "the nightly invoice job" only because every server was told so. Typo it as 9002 on one server and that server sails straight through and sends its own 8,000 emails — no error, no warning. The database polices the key faithfully; it has no opinion on whether the application used the right one.

Visual Model

Picture two due-date policies for the same locked cabinet key. Under one policy, the key goes back to the front desk the moment your library visit ends — whether you found your book or gave up and left early. Under the other, the key stays checked out under your name until you personally walk back and hand it in; leaving your visit early changes nothing. Postgres runs both policies on the same kind of key at the same time, and which one you picked decides what happens the instant a ROLLBACK hits.

Step through the same key (42) acquired two ways: once as a transaction-level lock, once as a session-level lock, both ended by the same ROLLBACK.

Step 1 of N

Loading…

Deeper — Edge Cases & Gotchas

Why not just build a locks table?

That instinct is correct — an advisory lock is the lock table you would otherwise build, provided by Postgres for free.

Roll your own	Advisory lock
Create a table, insert a row, `SELECT ... FOR UPDATE` it	`pg_try_advisory_xact_lock(9001)` — no table, no rows
Locking writes to the tuple → dead tuples → autovacuum churn	no bloat
A crashed holder leaves a stale row; needs lease expiry + a reaper job	the lock itself vanishes the instant the connection dies

The trade is enforcement, not mechanism: a job_locks row is real, inspectable data with a readable name. An advisory lock is an integer with no schema, no comment, and no foreign key explaining what it means — that documentation burden moves entirely into the codebase.

Key collisions

Both functions have two overloads — pg_advisory_lock(bigint) and pg_advisory_lock(int, int) — and the two key spaces do not overlap. The two-int form gives a natural (classifier, identifier) split for picking keys, e.g. pg_advisory_xact_lock(1, 48291) for "per-user critical section, user 48291". For string-named resources, the idiom is pg_advisory_xact_lock(hashtext('nightly-invoice-run')) — but hashtext is only a 32-bit hash, so two unrelated subsystems can compute the same key and block each other for reasons no one diagnoses quickly. Mitigation: reserve a distinct classifier per subsystem, and keep one shared registry of key constants rather than literals scattered at call sites.

Connection poolers

Under PgBouncer transaction pooling, a "session" is a backend connection borrowed for one transaction and handed to someone else afterward. A session-level advisory lock taken there leaks onto a connection the application no longer controls, and a later unrelated request can inherit it. Transaction-level advisory locks are safe under transaction pooling; session-level locks require session pooling.

Shared memory exhaustion

Advisory locks live in the same shared lock pool as regular locks, sized by max_locks_per_transaction × max_connections. Exhausting it leaves the server unable to grant any lock at all, giving a practical ceiling in the tens to hundreds of thousands. A per-row advisory lock over a million-row table is not a viable design.

They can deadlock too

Advisory locks share the same lock manager as regular locks, so two sessions acquiring keys in opposite order produce the same circular wait as any other deadlock (see the Deadlocks entry below): session A locks key 1 then wants key 2, while session B locks key 2 then wants key 1. The fix is identical — a consistent global acquisition order, most simply ascending numeric key order. The pg_try_advisory_* variants sidestep the problem entirely: a caller that never waits cannot join a wait-for cycle.

Anti-pattern: calling an advisory-lock function inside a query with a LIMIT, assuming only the returned rows get locked.

-- DANGER: may lock far more than 100 rows
SELECT pg_advisory_lock(id) FROM foo WHERE id > 12345 LIMIT 100;

Why it breaks: lock functions are ordinary functions evaluated per row, and LIMIT is not guaranteed to apply before they run. Postgres may call pg_advisory_lock for every row the scan touches before trimming to 100 — leaving dangling locks the application never intended to take and never releases, visible only in pg_locks for the rest of the session. The fix is forcing the LIMIT first, inside a subquery: SELECT pg_advisory_lock(q.id) FROM (SELECT id FROM foo WHERE id > 12345 LIMIT 100) q;

Test Yourself

A session calls pg_advisory_lock(42) (the session-level variant) inside a transaction, and that transaction then ROLLBACKs. What happens to the lock?

Three cron servers all call pg_try_advisory_xact_lock(9001) at the same instant. What happens to the two that don't get the lock?

Suppose a service used a homemade job_locks table (insert a row, then SELECT ... FOR UPDATE it) instead of an advisory lock, and the holder process got OOM-killed mid-run. Trace what state is left behind and what has to exist to recover automatically.