PostgreSQL Advisory Locks

A native Postgres locking primitive separate from row-level locks. The DB cares about the lock key; the lock means whatever you say it means. Used for distributed coordination, leader election, deduplication, serialized cron jobs.

Two flavors

Transaction-level is safer (auto-release); session-level is what you need when the work outlives a single transaction (background job).

API

-- Blocking: wait until acquired
SELECT pg_advisory_lock(42);

-- Non-blocking: returns true/false
SELECT pg_try_advisory_lock(42);

-- Two-key form (8 bytes via two 32-bit ints)
SELECT pg_advisory_lock(42, 99);

-- Release
SELECT pg_advisory_unlock(42);

The key is an arbitrary bigint (or two ints). You’re responsible for picking unique numeric IDs per resource. Hash a string if needed:

SELECT pg_advisory_xact_lock(hashtext('migrate-orders-table'));

hashtext is fast; collisions are theoretically possible but rare. For high-cardinality keys, use a domain prefix + identifier:

SELECT pg_advisory_xact_lock(1000, user_id);   -- "domain 1000, user X"

Use case: one-of-N workers runs a job

import psycopg

with psycopg.connect(...) as conn:
    with conn.cursor() as cur:
        cur.execute("SELECT pg_try_advisory_lock(%s)", (hash("daily-report"),))
        got_lock = cur.fetchone()[0]
        if not got_lock:
            return                       # another worker is doing it
        try:
            run_daily_report()
        finally:
            cur.execute("SELECT pg_advisory_unlock(%s)", (hash("daily-report"),))
            conn.commit()

N workers race to claim the lock; only one wins. The rest move on. The classic “guaranteed-singleton job across a fleet” pattern.

Use case: leader election

A Celery Beat fleet, each beat tries to acquire the lock; only one runs at a time. Even Celery Beat itself has a leader-election extension built on advisory locks.

with conn.cursor() as cur:
    cur.execute("SELECT pg_try_advisory_lock(%s)", (BEAT_LOCK_ID,))
    if cur.fetchone()[0]:
        run_as_leader()

If the leader dies, the connection closes; the session-level lock auto-releases; next attempt wins.

Use case: pessimistic upsert serialization

Avoid contention on a row by serializing the intent without locking the row:

# Concurrent inserts of "the same logical user"
with conn.transaction():
    with conn.cursor() as cur:
        cur.execute("SELECT pg_advisory_xact_lock(%s, %s)", (USER_DOMAIN, user_id))
        cur.execute("INSERT INTO users (id, ...) VALUES (%s, ...) ON CONFLICT DO NOTHING", (user_id, ...))

Less common — usually ON CONFLICT is enough. Advisory locks help when the operation spans multiple statements that need to look atomic to other callers.

Use case: migration coordination

The classic init container migration problem — multiple pods start, each tries to run alembic upgrade head simultaneously. Result: dead-locks on schema changes, or two migrations applied concurrently.

def safe_migrate():
    with engine.connect() as conn:
        result = conn.execute(text("SELECT pg_try_advisory_lock(:k)"), {"k": MIGRATE_LOCK}).scalar()
        if not result:
            print("Another pod is migrating, skipping")
            return
        try:
            alembic_upgrade_head()
        finally:
            conn.execute(text("SELECT pg_advisory_unlock(:k)"), {"k": MIGRATE_LOCK})

One pod gets the lock and migrates; the rest skip. Robust against pod count.

Pgbouncer pool-mode interaction

Session-pooling: fine; the connection stays bound to the client, locks behave normally.

Transaction-pooling: session-level advisory locks break — the connection is returned to the pool after each transaction; the lock is released. pg_advisory_xact_lock works (released at COMMIT regardless of pool mode).

Statement-pooling: even transaction locks break.

If using transaction pooling, you must use the _xact_ variant. See 09_connection_pooling.md.

Distributed locking caveats

Advisory locks are per-database (technically: per-cluster, since they live in shared memory). A replica doesn’t share locks with the primary. For HA, the leader’s locks become inaccessible when promoted; design jobs to be safe with brief overlap during failover.

For cross-database / cross-region distributed locks, Postgres advisory locks aren’t the right tool — use a real distributed lock service (etcd, Consul, Redis Redlock with the caveats).

Common gotchas

• Forgetting to release. Session-level locks held until session ends. If your app holds the connection forever (pool), the lock holds forever.
• Mixing session and transaction locks on the same key. They count independently; the same key can be both locked session-level and transaction-level simultaneously.
• Reentrant locks: acquire same key twice, must release twice. Locks are reference-counted.
• pg_advisory_lock blocks on an already-held lock with no timeout. Use pg_try_advisory_lock + retry with backoff to avoid indefinite waits.
• Lock visibility. pg_locks view shows them: SELECT * FROM pg_locks WHERE locktype = 'advisory';

Inspection

-- Who holds advisory locks?
SELECT pid, locktype, classid, objid, granted, mode
FROM pg_locks
WHERE locktype = 'advisory';

-- Match to query/user
SELECT l.*, a.query, a.usename, a.application_name
FROM pg_locks l JOIN pg_stat_activity a USING (pid)
WHERE locktype = 'advisory';

Comparison to SELECT FOR UPDATE

If the work is “modify this row, only one writer at a time”, SELECT ... FOR UPDATE is correct — it locks the row directly. Advisory locks are for named concurrency control where there isn’t a natural row to lock.

Interview angle

• “What’s a Postgres advisory lock and when do you use it?” — application-defined lock on an arbitrary integer key, separate from row locks. Use for distributed coordination (one-of-N job, leader election, migration safety) where there’s no natural row to lock.
• “Session vs transaction advisory locks?” — session: held until explicit unlock or session ends. Transaction: held until transaction COMMIT/ROLLBACK. Transaction is safer (auto-released). Session is needed when work outlives the transaction.
• “How do you do leader election with advisory locks?” — each node tries pg_try_advisory_lock(KEY); the one that gets true is leader. When the leader’s connection dies, the lock auto-releases; next attempt wins.
• “How do you prevent two pods from running the same migration?” — pg_try_advisory_lock(MIGRATE_KEY) at startup; if you don’t get it, skip. The pod that got it runs alembic upgrade head; releases the lock at the end.
• “How do advisory locks interact with pgbouncer?” — session-level locks break in transaction pooling mode (connection returned to pool, lock released). Transaction-level locks (pg_advisory_xact_lock) work in transaction pooling but not statement pooling. Use the _xact_ variant when uncertain.
• “Why SELECT FOR UPDATE instead of advisory locks?” — when there is a row to lock. Advisory locks are for named-resource locking with no row counterpart. Choose based on the natural unit of contention.