What are Postgres advisory locks and how do they work?

Advisory locks are application-defined locks. You pick a key, either one bigint or a pair of int4 values, and ask Postgres to lock it. The regular lock manager tracks them alongside table and row locks, but an advisory lock protects nothing by itself. Postgres has no idea that key 42 means "the nightly billing job." The lock is only meaningful if every piece of code that touches the shared work agrees to acquire it first. Any code path that skips the lock bypasses it silently.

Locks come in two scopes:

  • Session-level locks (pg_advisory_lock, pg_try_advisory_lock, released with pg_advisory_unlock) last until you unlock them or the connection closes. They ignore transaction boundaries. If you acquire one inside a transaction that rolls back, you still hold it afterwards.
  • Transaction-level locks (pg_advisory_xact_lock, pg_try_advisory_xact_lock) are released automatically at commit or rollback. There is no function to release them early.

Session locks are reentrant. Acquiring the same key twice in one session needs two unlocks before another session can take it, so an unbalanced code path can keep a lock held indefinitely. The plain functions block until the lock is free. The try variants return true or false immediately, which is the behavior you usually want when skipping duplicate work. Every function also has a _shared form, so readers can hold a key together while a writer takes it exclusively.

Comparison of function families

Family Scope Released when Reentrant Waits for lock
pg_advisory_lock Session Explicit unlock or disconnect Yes, one unlock per acquire Blocks
pg_try_advisory_lock Session Explicit unlock or disconnect Yes, one unlock per acquire Returns boolean immediately
pg_advisory_xact_lock Transaction Commit or rollback only Stacks, all released at transaction end Blocks
pg_try_advisory_xact_lock Transaction Commit or rollback only Stacks, all released at transaction end Returns boolean immediately
-- Session A: session-level locks are reentrant
SELECT pg_advisory_lock(42);        -- acquired (hold count 1)
SELECT pg_advisory_lock(42);        -- acquired again (hold count 2)
SELECT pg_advisory_unlock(42);      -- true, but A still holds it once

-- Session B: try variant returns immediately instead of blocking
SELECT pg_try_advisory_lock(42);    -- false: A has not fully released

-- Session A: second unlock balances the second acquire
SELECT pg_advisory_unlock(42);      -- true, lock is now free

-- Session B: now it succeeds
SELECT pg_try_advisory_lock(42);    -- true
SELECT pg_advisory_unlock(42);      -- release when done

-- Session locks ignore transaction boundaries
BEGIN;
SELECT pg_advisory_lock(99);
ROLLBACK;                           -- lock on 99 is STILL held
SELECT pg_advisory_unlock(99);      -- must be released explicitly

-- Transaction-level lock using the two-int4 key form
BEGIN;
SELECT pg_try_advisory_xact_lock(7, 1001);  -- true if no one else holds (7, 1001)
-- ... do the protected work here ...
COMMIT;                             -- released automatically; no early unlock

-- Shared vs exclusive: readers can share, a writer must wait
SELECT pg_advisory_lock_shared(500);        -- readers can hold this together
SELECT pg_advisory_unlock_shared(500);

-- Inspect advisory locks currently held or awaited
SELECT pid, classid, objid, objsubid, mode, granted
FROM pg_locks
WHERE locktype = 'advisory';

Two sessions demonstrate session-lock reentrancy, the non-blocking try variant, session locks surviving a rollback, transaction-scoped release at commit, the shared variant, and how to inspect held advisory locks in pg_locks.

Who should use advisory locks, and when are they the right tool?

The target is a common shape: one application codebase deployed as several identical replicas behind a load balancer, all talking to a single Postgres primary. Each replica runs the same scheduler, the same startup hooks, and the same background workers. Without coordination, a nightly billing job runs once per replica, a schema migration starts on three pods at the same time, and a queue consumer that must be single-threaded is not.

Advisory locks fit here because the coordination point already exists. Every replica needs a working connection to the primary to do anything useful, so tying "who runs the job" to that same database adds no new failure domain. If Postgres is down, the job could not run correctly anyway. A separate coordination service is another system to deploy, secure, monitor, upgrade, and reason about during incidents. It also creates new partial-failure cases: the lock service is healthy but the database is not, or the reverse.

The development cost is also low. Engineers already write SQL against this database, and the locking calls are ordinary function calls inside queries the team already knows how to test, log, and review. There is no new client library, no new deployment, and no new on-call runbook to write.

The caveat that shapes everything that follows: the lock belongs to a database session or transaction, not to your process. Postgres has no idea which replica, thread, or job holds it. It only knows which connection does. Every pitfall in the later sections, including pooling, visibility, and failure handling, comes from that distinction.

How do you choose stable, collision-free lock keys?

Lock keys are bare integers, so Postgres cannot tell you that two features picked the same number. You need a scheme that makes collisions unlikely and keys reproducible.

The most common approach is to hash a namespaced string such as job:nightly-invoice into a bigint. Do the hashing with an algorithm whose output is fully specified, such as MD5 or SHA-256 truncated to 64 bits. In SQL, ('x' || substr(md5('job:nightly-invoice'), 1, 16))::bit(64)::bigint produces the same key on every server and from any client. The truncated hash is easy to reimplement in any language.

Avoid hashtext(). It is an internal function and is not documented as stable across major versions. Language-level hashes are a worse choice. Python's hash() on strings is randomized per process by default, so two replicas of the same build would compute different keys and never contend. The lock would appear to work while providing no mutual exclusion.

The two-int form, pg_advisory_lock(namespace, id), suits per-entity locks. Reserve the first integer for a subsystem, such as 1 for billing or 2 for search indexing. Use the second for the row ID. This keeps subsystems from colliding without any hashing.

Whichever scheme you choose, keep a single registry in application code. This can be one module of named constants, for example LockKeys.NIGHTLY_INVOICE and LockNamespaces.BILLING, with a comment on each entry explaining what it guards. A test that fails on duplicate values catches collisions at review time. Without a registry, two unrelated jobs can block each other with no error to point you to the cause.

# lock_keys.py: the single registry of advisory lock keys for this codebase.
import hashlib


def key_from_name(name: str) -> int:
    """Stable bigint key: first 64 bits of MD5, read as signed big-endian.

    Matches the SQL derivation exactly:
      SELECT ('x' || substr(md5('job:nightly-invoice'), 1, 16))::bit(64)::bigint;
    Never use Python's hash(): it is randomized per process.
    """
    digest = hashlib.md5(name.encode("utf-8")).digest()
    return int.from_bytes(digest[:8], "big", signed=True)


class LockKeys:
    # Ensures only one replica runs the nightly invoice job.
    NIGHTLY_INVOICE = key_from_name("job:nightly-invoice")
    # Serializes the hourly search index rebuild.
    SEARCH_REINDEX = key_from_name("job:search-reindex")


class LockNamespaces:
    # First int of pg_advisory_lock(namespace, id); second int is the row ID.
    # Both arguments are int4, so row IDs must fit in 32 bits.
    BILLING = 1  # per-account billing updates
    SEARCH_INDEXING = 2  # per-document reindexing


def _values(cls) -> list[int]:
    return [v for k, v in vars(cls).items() if k.isupper()]


# test_lock_keys.py: fails the build if two entries share a value.
def test_no_duplicate_keys():
    for cls in (LockKeys, LockNamespaces):
        values = _values(cls)
        assert len(values) == len(set(values)), f"duplicate key in {cls.__name__}"


# Usage: cur.execute("SELECT pg_try_advisory_lock(%s)", (LockKeys.NIGHTLY_INVOICE,))
#        cur.execute("SELECT pg_try_advisory_lock(%s, %s)", (LockNamespaces.BILLING, account_id))

A central Python registry that derives bigint lock keys with the same truncated-MD5 calculation as the SQL expression, reserves two-int namespaces per subsystem, and includes a test that rejects duplicate values.

How do you implement singleton jobs and leader election with pg_advisory_lock?

Pattern 1: singleton cron job

Every replica fires the same scheduled tick. Each one opens a transaction and calls pg_try_advisory_xact_lock(key). Exactly one gets true and does the work. The rest get false immediately and return without waiting, so a slow run does not pile up blocked sessions behind it. The lock belongs to the transaction, so commit, rollback or a crashed process releases it. No unlock path can be forgotten.

The cost is that the lock lives only as long as the transaction. Keep that transaction short. A transaction left open for minutes holds a snapshot that stops vacuum from removing dead rows across the whole database, not only the tables the job touches. If the work takes long, split it into batches that each take and release the lock, or use pattern 2.

Pattern 2: leader election for a long-running worker

Each replica takes one connection out of the pool and keeps it for itself. The leader calls pg_try_advisory_lock(key) on that connection and holds the session-level lock while it runs leader-only work. Its normal queries still go through pooled connections. Followers call the same function at a fixed interval and stay idle while it returns false.

If the leader's process dies, Postgres releases the lock once it notices the session is gone. The harder case is a leader that stays up but loses its connection. Postgres may release the lock and promote a follower while the old leader still thinks it holds the lock. To prevent that, the leader runs a cheap query on its dedicated connection on a short timer. It stops all leader-only work as soon as that check fails or times out. Make the check interval shorter than the followers' polling interval, so the old leader stops before a new one can take over.

import time
import psycopg
from psycopg_pool import ConnectionPool

DSN = "postgresql://app:change-me@db.internal:5432/app"
REPORT_KEY, LEADER_KEY = 4201, 4202   # one fixed bigint per job / role
pool = ConnectionPool(DSN, min_size=2, max_size=10)

# Pattern 1: every replica runs this on the same cron tick.
def nightly_report_tick():
    # The pool's connection() block is one transaction: commit on exit, rollback on error.
    with pool.connection() as conn:
        sql = "SELECT pg_try_advisory_xact_lock(%s)"
        if not conn.execute(sql, (REPORT_KEY,)).fetchone()[0]:
            return  # another replica won; return at once instead of queueing
        conn.execute("INSERT INTO report_runs (ran_at) VALUES (now())")  # keep it short
    # Commit, rollback or a dead process releases the lock. There is no unlock call.

# Pattern 2: leader election on a dedicated connection that is not in the pool.
def run_leader(do_one_small_step, poll_s=10.0, check_s=2.0):
    # tcp_user_timeout (ms) makes a check on a dead network path fail instead of hang.
    lock_conn = psycopg.connect(DSN, autocommit=True, tcp_user_timeout=3000)
    sql = "SELECT pg_try_advisory_lock(%s)"
    while not lock_conn.execute(sql, (LEADER_KEY,)).fetchone()[0]:
        time.sleep(poll_s)  # follower: stay idle and poll again
    last_check = time.monotonic()
    try:
        while True:
            # check_s must stay below poll_s so the old leader stops before a follower can win.
            if time.monotonic() - last_check >= check_s:
                lock_conn.execute("SELECT 1")  # raises if the session is gone
                last_check = time.monotonic()
            do_one_small_step()  # leader-only work in short steps, on pooled connections
    except psycopg.OperationalError:
        return  # lost the session, and Postgres may already have freed the lock: stop now
    finally:
        lock_conn.close()  # closing the session releases a lock we still hold

Python (psycopg 3) code for both patterns: a transaction-scoped try-lock for a singleton cron tick, and a session-level leader lock on a dedicated connection that stops leader-only work as soon as its liveness check fails.

Why do connection pools break session-level advisory locks?

A session-level advisory lock belongs to the server backend process that ran pg_advisory_lock, not to the code path that requested it. The lock stays held until that same session calls pg_advisory_unlock or disconnects. Your application's notion of "who holds the lock" plays no part in this.

With an in-process connection pool, that ownership model leaks. Suppose a job acquires a lock, then returns its connection to the pool without unlocking, perhaps because an exception skipped the cleanup. The lock stays alive on that connection. The next request to borrow it inherits the lock without knowing. Session advisory locks are also reentrant, so if that borrower requests the same key, it succeeds immediately and stacks a second hold. Two pieces of code now each believe they are the only owner. The original job, running on a different connection, cannot release the lock either. An unlock from another session returns false.

An external pooler in transaction mode makes this worse. Each transaction can land on a different server session, so the lock call and the matching unlock may run on unrelated backends. The lock gets orphaned on a session your replica no longer controls.

Follow these rules:

  • Session locks need a dedicated, unpooled connection that you hold for the lifetime of the lock.
  • Transaction-level pooling permits only transaction-scoped locks (pg_advisory_xact_lock), which release at commit or rollback.

Dedicated connections have a cost. Each held session lock pins one backend. A fleet of replicas, each keeping a polling connection open for leader election, uses up part of max_connections that request traffic would otherwise get.

How do you see which replica holds an advisory lock?

Every advisory lock, held or awaited, appears in the pg_locks view as a row with locktype = 'advisory'. On its own, that row only gives you a pid and a granted flag. Join it to pg_stat_activity on pid to get the backend's application_name, client_addr, state and query. Rows with granted = false are waiters, and the holder is the row with the same key where granted = true. Filter on the database column as well, because advisory keys are scoped per database.

The key is spread across three columns. For a single bigint key, classid holds the high 32 bits, objid holds the low 32 bits, and objsubid is 1. For the two-int4 form, classid is the first argument, objid is the second, and objsubid is 2. Because of the objsubid value, pg_advisory_lock(1, 2) and a bigint key with the same bit pattern do not conflict.

client_addr alone is often not enough to tell holders apart. Behind NAT, a sidecar proxy or a shared pooler, many replicas show up with the same address. Set application_name on each replica's connection to something like the pod or host name, and the holder becomes obvious.

This view is where you start when debugging. A stuck singleton job usually shows up as a holder sitting idle or idle in transaction long after its work should have finished. An unexpected holder whose query belongs to a different feature points to a key collision.

Blocking pg_advisory_lock calls can deadlock if two sessions take keys in opposite order. Postgres detects the cycle after deadlock_timeout and raises a "deadlock detected" error in one participant. That participant's code must handle the error and retry.

-- Optional, per replica: name the session so holders are identifiable.
-- Usually set via the connection string instead (application_name=orders-api-7f9c).
SET application_name = 'orders-api-7f9c';

-- Show every advisory lock holder and waiter in the current database.
SELECT
  l.pid,
  l.granted,                            -- false = waiting, true = holding
  CASE l.objsubid
    WHEN 1 THEN ((l.classid::bigint << 32) | l.objid::bigint)::text   -- single bigint key
    WHEN 2 THEN l.classid::int || ',' || l.objid::int                 -- two int4 keys
  END                          AS lock_key,
  l.objsubid                   AS key_form,   -- 1 = bigint, 2 = (int4, int4)
  l.mode,                                     -- ExclusiveLock or ShareLock
  a.application_name,                         -- which replica, if set per pod/host
  a.client_addr,
  a.state,                                    -- 'idle in transaction' on a holder is a red flag
  now() - a.state_change       AS in_state_for,
  pg_blocking_pids(l.pid)      AS blocked_by, -- for waiters: pids holding the conflicting lock
  left(a.query, 80)            AS last_query  -- unrelated query on a holder suggests a key collision
FROM pg_locks l
JOIN pg_stat_activity a ON a.pid = l.pid
WHERE l.locktype = 'advisory'
  -- advisory keys are per database, so ignore other databases' locks
  AND l.database = (SELECT oid FROM pg_database WHERE datname = current_database())
ORDER BY lock_key, l.granted DESC, in_state_for DESC;

A diagnostic query that joins pg_locks to pg_stat_activity, decodes classid/objid/objsubid back into bigint or two-int keys, and shows which named replica holds or waits on each advisory lock.

When should you avoid advisory locks, and what are the risks?

The core risk is that an advisory lock tracks the session, not the process doing the work. If the network drops or a connection is killed, Postgres ends the backend and releases the lock. The old leader's process may still be running mid-job, unaware that it lost the lock. A second replica acquires the lock and starts the same job, so two workers are now active. Advisory locks provide no fencing token, so downstream systems have no way to reject writes from the stale holder.

Detecting a dead session is slow. Postgres learns that a client is gone through TCP keepalives (tcp_keepalives_idle and related settings) or when a write to the socket fails. Takeover can therefore take seconds to minutes. The opposite failure is worse: a leader that hangs but keeps its connection open holds the lock indefinitely.

Locks exist only in the primary's shared memory. They are not replicated, so a failover drops every lock. Lock availability is no better than the primary's availability. Advisory locks are also purely cooperative. A manual psql session or another service that never calls pg_advisory_lock is not blocked.

Mitigations

Write job logic that is safe to run twice. Record each completed run durably, for example as a row with a unique constraint on (job_name, period), and insert it in the same transaction as the job's effects. A worker that takes over after a lost connection then hits the constraint instead of applying the effects a second time.

Choose something else when

  • coordination spans several databases or non-Postgres systems
  • you need strict fencing, so a stale holder's writes are rejected
  • you would hold very large numbers of locks at once, since they consume shared lock-table memory

What are the alternatives to advisory locks for distributed locking in Postgres?

Each of those limits points to a different tool, and most of them still live inside Postgres.

Row-level locks with SELECT ... FOR UPDATE SKIP LOCKED suit job queues. Each replica claims rows that no other transaction holds, and the claim ends when the transaction commits or rolls back. The lock is tied to the data, so there is no separate key scheme to maintain.

A lease table stores the holder, an expiry timestamp and a token that increases with each acquisition. Downstream writes include the token and are rejected when it is lower than the latest one seen. This gives you fencing, which advisory locks cannot provide.

Unique constraints on run records, such as (job_name, scheduled_for), make a second insert fail. The result is at-most-once execution with no lock lifetime to reason about.

A dedicated coordination service or consensus store fits when coordination must outlive a database failover or span several systems. Orchestrator-level singletons or scheduler-side scheduling remove the need for replicas to coordinate at all.

Option Setup cost Operational cost Fencing Failover behavior Best fit
Advisory locks Low Low No Released when session dies Singleton jobs, simple leader election
SKIP LOCKED Low Low No Released on transaction end Job queues
Lease table Medium Medium (expiry tuning) Yes, via token Takeover after expiry Long-running leaders
Unique constraint Low Low N/A Duplicate insert fails Scheduled runs
Coordination service High High Yes Independent of database Cross-system coordination
Orchestrator singleton Medium Medium No Depends on orchestrator Single scheduled worker

For a typical monolith, the first four options reuse the existing database, migrations and on-call knowledge. They cost hours of engineering time, while a coordination service costs weeks of new infrastructure. Reach for that service only when fencing across systems is a hard requirement.

How OneEnv can help

Disclosure: OneEnv is the product behind this blog.

OneEnv (oneenv.io) is a workspace-based governance platform for the things services share — environment variables, database structure, API routes and validation rules. A change to a shared value is staged on a branch, reviewed once, and then every affected service explicitly approves or rejects it before it goes live, so you can see who a change touches before it ships instead of finding out in production.

Applied to postgres advisory locks, this is where it helps:

Saves time

  • Every service that inherits a shared value is listed in one place, so nobody searches repos and chats to find out who is affected.
  • One review per branch replaces coordinating each team separately; each service still keeps its own approve/reject.
  • Changing a route's URL or method automatically notifies the services that consume it — no hunting through repos for callers.

Saves cost

  • Fewer production incidents and rollbacks caused by a shared value, route or column changing under a consumer unannounced.
  • Less duplicated configuration to maintain: one shared definition inherited by many services.
  • Consumers learn about a breaking change at review time rather than from production errors.
  • OneEnv has a free plan (1 workspace, unlimited services and members, full governance), so trying it costs nothing.

These are qualitative benefits, not measured results — how much you save depends on how many services share the value.

Want to try it? Start at oneenv.io.