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:
        # hashtext() in SQL, not Python's hash(): str hashes are salted per process
        # (PYTHONHASHSEED), so every worker would lock a different key
        cur.execute("SELECT pg_try_advisory_lock(hashtext(%s))", ("daily-report",))
        got_lock = cur.fetchone()[0]
        if not got_lock:
            # another worker is doing it
            return
        try:
            run_daily_report()
        finally:
            cur.execute("SELECT pg_advisory_unlock(hashtext(%s))", ("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 cleanly, its connection closes, the session-level lock is released, and the next attempt wins. Two failure modes make this weaker than it looks:

• Split brain on a dropped connection. If the leader’s connection drops (network blip, PgBouncer restart) but the process keeps running, Postgres frees the lock, another worker becomes leader, and the old one carries on. Check that the lock is still held before each side effect, or make the protected writes carry a fencing token.
• No leader for hours on a vanished host. A host that disappears without closing TCP keeps its lock until Postgres notices; with Linux keepalive defaults that is about 2 hours 11 minutes. Set tcp_keepalives_idle, tcp_keepalives_interval, tcp_keepalives_count and tcp_user_timeout on the server.

Both are covered in Consensus and leader election.

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, and not by releasing: the lock stays held on the server connection, which the pooler hands to another client after the transaction. Your later pg_advisory_unlock may run on a different server connection, where it returns false with a warning, and the lock leaks until that server connection closes. 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 Connection pooling.

Distributed locking caveats

Advisory locks are per database: they live in the cluster’s shared lock table, but the lock tag includes the database OID (visible in pg_locks.database), so the same key in two databases is two different locks. 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.

Related

• Celery Beat: HA, Scheduling, and Lock Contention - Celery Beat is the scheduler — it enqueues tasks on a schedule.