I didn't need a lock server — Postgres already was one

The orchestrator for a multi-tenant recommendation platform runs with several replicas. If one falls over during a deploy or a crash, the others keep the work moving. Each replica wakes on a timer, scans the tenants whose models are due for retraining, and tries to push each one’s pipeline forward a step.

That redundancy buys availability, but it introduces a hazard: two replicas must never drive the same tenant’s pipeline at the same time.

The problem

The pipeline for a single tenant is a state machine. It submits a training job that might run for hours, waits, downloads results, advances to the next stage. Now picture two replicas both deciding, in the same tick, that tenant 42 is due. Without coordination they’ll both submit a fresh training job, both advance the same stored pointer, and trip over each other — double the cost, corrupt state, or worse.

An in-process lock doesn’t help here because the contenders aren’t threads in one process — they’re separate processes, often on separate machines. I needed mutual exclusion across processes, keyed by tenant.

The obvious answer is a lock service: Redis with a SET NX lease, or ZooKeeper, or etcd. But every one of those is another thing to deploy, monitor, secure, and think about when it’s the thing that’s down. So I looked at the DIY version first: a locks table with a tenant id, an owner, and an expiry timestamp. Grab a row, write your name and a TTL, renew while you work, delete when done.

That scheme has a rotten core: the TTL. Too short, and a slow-but-alive replica loses its lock mid-run and a second one double-runs the tenant. Too long, and a replica that died mid-hold blocks that tenant until expiry. You end up building heartbeats to renew leases and a janitor to sweep abandoned rows, and now your “simple” lock is a small distributed system with its own failure modes. I already had Postgres holding the pipeline state; I wanted the lock to come from there too, without a new table and without a TTL.

Advisory locks

Postgres has a lock manager built in. Most of the time you never address it directly — it takes row and table locks as a side effect of your queries. What’s less well known is that it exposes application-defined locks you can take by hand, keyed by an integer you choose. They’re called advisory locks because Postgres attaches no meaning to them: they aren’t tied to any row or table, so they mean whatever your application agrees they mean.

The one I care about is pg_try_advisory_lock(key). It asks Postgres: is anyone holding the lock named by this integer? If not, give it to me. It returns true if you got it, false if someone else has it, and answers immediately instead of waiting. No table, no row, no TTL.

The key is a 64-bit integer, but my pipelines are named by tenant. So I hash the name into that range:

import hashlib, sys

def lock_key(name: str) -> int:
    # pg_try_advisory_lock wants a signed 64-bit integer.
    # Take the first 64 bits of an md5 of the name...
    digest = hashlib.md5(name.encode()).hexdigest()[:16]
    # ...then slide it into the signed range Postgres expects.
    return int(digest, 16) - sys.maxsize

Two different names could hash to the same key and contend falsely. For a few hundred tenants that’s astronomically unlikely — and a false collision only ever costs a skipped cycle, not correctness. The worst case is that two unrelated tenants briefly take turns instead of running in parallel.

Try-and-skip, not block-and-wait

Postgres gives you both flavours. pg_advisory_lock(key) blocks — it parks your session until the current holder lets go. pg_try_advisory_lock(key) tries — it grabs the lock or returns false immediately.

My replicas are interchangeable. If replica B finds that tenant 42 is already locked, it gains nothing by waiting — replica A is already handling 42. The useful thing for B to do is move to tenant 43 and come back later. So the loop is try-and-skip:

with try_lock(lock_key(f"training:{tenant_id}")) as lock:
    if not lock.acquired:
        # Another replica owns this tenant right now. Nothing to do —
        # move on; we'll try again on the next scan.
        return
    advance_pipeline(tenant_id)   # we hold the lock: safe to run one step

The scheduler reruns the scan every few minutes, so skipping costs nothing. Waiting would have been strictly worse — it would tie up a replica doing nothing while another already does the work.

The blocking variant still earns its keep for a different shape of job. Onboarding a brand-new tenant is genuinely serial — you do want callers to queue and take turns — so there I use pg_advisory_lock and let it wait. Same primitive, two modes, picked per job.

The part that made this actually better than TTLs

Advisory locks come in two lifetimes: transaction-scoped (released when the transaction ends) and session-scoped (held for the life of the database connection). I use session-scoped, and I tie each lock to its own connection:

class try_lock:
    def __enter__(self):
        self.conn = psycopg2.connect(DSN)        # a dedicated connection (see below)
        cur = self.conn.cursor()
        cur.execute("SELECT pg_try_advisory_lock(%s)", (self.key,))
        self.acquired = cur.fetchone()[0]        # True if we got it, False if held elsewhere
        return self

    def __exit__(self, *exc):
        if self.acquired:
            cur = self.conn.cursor()
            cur.execute("SELECT pg_advisory_unlock(%s)", (self.key,))
        self.conn.close()   # even without the unlock, closing frees the lock

That last line is the important one. A session-scoped lock is bound to the connection that took it, so the moment that connection goes away — a clean close(), or a replica that crashed, got OOM-killed, or lost the network — Postgres sees the backend vanish and drops every advisory lock it held. Automatically. The explicit pg_advisory_unlock is just politeness; the connection’s lifetime is the real guarantee.

With the table-and-TTL approach, “the holder is alive” is a timestamp I have to write, renew on a heartbeat, and interpret correctly. A dead holder still blocks the key until its TTL lapses. With a session advisory lock, “the holder is alive” is the same TCP connection Postgres is already watching. No TTL to tune, no heartbeat to send, no janitor.

One footgun: the connection pool

A session lock belongs to a physical connection, not to a function or a request. If you borrow a connection from a shared pool, take a lock on it, and hand it back, the next caller to get that connection inherits your lock — or your unlock lands on the wrong session. It’s worse behind a transaction-mode connection pooler like PgBouncer, which multiplexes many clients across a few backends and won’t keep you pinned to one; session-scoped advisory locks are explicitly unsafe in that mode.

So the rule is: the lock gets its own dedicated connection, opened when acquired and closed when released, never routed through the app’s shared pool. That’s the bare psycopg2.connect(DSN) in the snippet above rather than a pool.getconn(). One extra short-lived connection per lock attempt is a small, predictable cost.

Limits worth knowing

Advisory means advisory. Postgres won’t stop code that doesn’t ask for the lock from touching tenant 42. The guarantee holds only because every path that mutates a pipeline agrees to take the lock first. With a single chokepoint it’s easy to keep true; if pipeline mutations were scattered across a dozen call sites, I’d trust it a lot less.

It all rides on one Postgres. If the database is down, nobody runs. But I already depend on that same Postgres for the pipeline state, so this isn’t a new point of failure — it’s the same one I already had.

---

When I looked at what I actually needed — several interchangeable workers, at most one per key, dead workers must free their keys automatically — advisory locks answered all of it. And the auto-release-on-disconnect is a stronger correctness story than most TTL schemes, because it leans on connection liveness the database is already tracking.