Module 6: Advisory Locks + Savepoints

Decision matrix: advisory locks vs Redis vs ZooKeeper/etcd

Next →

For distributed locking, the industry default is Redis (SETNX + Redlock). But PostgreSQL advisory locks are frequently the better option and almost nobody teaches it. This lesson gives you the complete decision matrix with concrete criteria.

By the end you'll be able to argue in a code review why your cron uses advisory locks (not Redis), when Redis really is the answer, and when the problem needs something stronger (ZooKeeper, etcd).

The 3 main options

Option 1: PostgreSQL Advisory Locks

async with advisory_xact_lock(session, key):
    await do_critical_work()

Characteristics:

  • Transactional (ACID).
  • No automatic TTL.
  • Re-entrant (the same session can acquire it multiple times).
  • Releasable only by the session that acquired it.
  • No native configurable timeout (use statement_timeout).

Option 2: Redis SETNX / Redlock

# Basic SETNX pattern
got = await redis.set(key, "locked", nx=True, ex=300)  # NX = if not exists, EX = TTL in seconds
if got:
    try:
        await do_critical_work()
    finally:
        await redis.delete(key)

Characteristics:

  • No real transactions (Redis is eventually consistent in cluster mode).
  • Automatic TTL (the key expires after N seconds).
  • Not re-entrant by default.
  • Releasable by anyone with the key (a race vulnerability).
  • The Redlock algorithm for multi-master.

Option 3: ZooKeeper / etcd

# With kazoo (ZooKeeper client)
from kazoo.client import KazooClient

zk = KazooClient(hosts="zk1:2181,zk2:2181,zk3:2181")
zk.start()

lock = zk.Lock("/locks/critical_job", "instance-1")
with lock:
    do_critical_work()

Characteristics:

  • Strong consistency (Paxos / Raft).
  • Watch events (notification when the lock releases).
  • Ephemeral nodes (auto-cleanup if the client dies).
  • Designed for distributed coordination.

Decision matrix with concrete criteria

CriterionAdvisory LocksRedis SETNXZooKeeper/etcd
Setup complexity✅ You already have PG⚠️ +1 service❌ A specific cluster
Latency🟢 ~1ms (local PG)🟢 ~0.5ms (local Redis)🟡 ~2-5ms
Throughput🟢 High🟢 Very high🟢 High
Consistency✅ Strong (ACID)⚠️ Eventual (cluster)✅ Strong
Auto TTL❌ No (manual)✅ Yes✅ Ephemeral nodes
Lock fairness✅ FIFO with pg_advisory_lock❌ Not guaranteed✅ Yes
Multi-region❌ Limited⚠️ Redlock complicates it✅ Designed for this
Debugging🟢 pg_locks view🟡 redis-cli KEYS🟡 zkCli
Operational cost✅ Zero extra🟡 Maintain Redis❌ Maintain a cluster
When the client dies⚠️ Lock persists until the conn closes✅ TTL releases✅ Ephemeral cleanup

When each one wins

Advisory locks win when

1. You already have PostgreSQL and want simplicity.

If your app uses PostgreSQL and you need a distributed lock, advisory locks are free — no new service, no new infrastructure, no new monitoring. Just SQL you already do.

2. The operation is transactional.

An MV refresh cron. An atomic operation. An idempotent refresh. You want the lock to go with the transaction. pg_advisory_xact_lock is perfect.

3. You need integration with other PG locks.

If your logic also needs SELECT FOR UPDATE or other PostgreSQL locks, advisory locks play well with them. Same system, same backend.

4. Single-region, single PostgreSQL primary.

Typical SaaS setups where the primary DB is one (with read-only replicas). Advisory locks coordinate across all the workers that write to the primary.

5. Low-to-medium lock volume.

PostgreSQL handles thousands of advisory locks without a problem. If you have dozens of lock types, perfect.

Redis wins when

1. You already have Redis for something else.

If Redis is in your stack for caching/session storage, adding SETNX doesn't add a dependency. The decision is marginal.

2. You need a native TTL.

If your lock has to expire automatically after X minutes (case: a lock whose holder might die and you want automatic cleanup), Redis is simpler. Advisory locks require manual cleanup.

3. Extreme throughput.

For cases like rate limiting that need hundreds of thousands of locks/sec, Redis is more optimized. PostgreSQL advisory locks are fast but Redis is faster at this level.

4. Multi-master / clustering.

Setups with multiple Redis masters (Redis Cluster). The Redlock algorithm coordinates between them. PostgreSQL advisory locks don't scale that way.

5. Pub/Sub for coordination.

If you need to "notify others when I release the lock", Redis Pub/Sub can integrate. PG has no easy-to-use native equivalent.

ZooKeeper / etcd wins when

1. Complex distributed coordination.

Beyond locks: leader election, configuration management, service discovery. If you already have ZK/etcd for this, adding locks is natural.

2. Multi-region with coordination between datacenters.

ZK/etcd are designed for this. PG/Redis aren't.

3. Strong guarantees with failure detection.

Ephemeral nodes guarantee cleanup if the client dies. More robust than a fixed TTL.

4. Large systems with multiple microservices.

If your architecture is 50+ microservices, a dedicated coordination service can simplify things.

The typical case: mid-size B2B SaaS

For typical apps (FastAPI + PostgreSQL + possibly Redis for cache):

Without Redis:

  • Distributed locks → advisory locks. No new dependency.

With Redis (for cache):

  • If it's simple Redis, locks → you can use SETNX or advisory locks. Nearly a tie.
  • If it's Redis Cluster with multi-master → Redlock is more complex but more correct.

With ZooKeeper / Kafka:

  • Probably a large architecture, locks → ZK for consistency with the rest of the stack.

The "client dies" case — a critical gotcha

A scenario that differentiates the options:

"My worker holds a lock, then the server dies from OOM. What happens to the lock?"

Session-level advisory lock:

  • The lock is held until the connection to PG closes.
  • If the worker dies abruptly, the TCP conn is eventually detected (TCP keepalive ~2 hours by default), then it closes and the lock releases.
  • Mitigation: configure tcp_keepalives_idle and tcp_keepalives_interval more aggressively (e.g., 60s).
  • Mitigation: use transaction-level when you can.

Transaction-level advisory lock:

  • The lock releases on the tx rollback (which happens when the conn closes).
  • There's still a TCP keepalive delay to detect a dead conn.

Redis SETNX with TTL:

  • The TTL releases it automatically. If you set 60s, at most 60s.
  • Trade-off: a very short TTL → the legitimate worker can lose the lock; too long → cleanup is slow.

ZooKeeper / etcd ephemeral:

  • The heartbeat detects a dead worker in seconds.
  • Automatic, fast cleanup.

Verdict: for "worker dies", ZK/etcd > Redis with TTL > advisory locks. If your case needs fast recovery after a crash, advisory locks are the weakest.

Pragmatic mitigation for advisory locks: configure aggressive TCP keepalives on the PG conn. "Connection dead" detection goes from hours to seconds.

# asyncpg connection with TCP keepalive
conn = await asyncpg.connect(
    "postgresql://...",
    server_settings={
        "tcp_keepalives_idle": "60",
        "tcp_keepalives_interval": "10",
        "tcp_keepalives_count": "3",
    }
)

Real-world case: migrating from Redis SETNX to advisory locks

Your app:

  • FastAPI + PostgreSQL.
  • Redis used ONLY for cron distributed locks.
  • 3 crons, each one with SETNX + TTL.

Before (Redis):

import aioredis

redis = aioredis.from_url("redis://...")

async def cron_with_redis_lock(name: str):
    got = await redis.set(name, "locked", nx=True, ex=600)  # 10min TTL
    if not got:
        return

    try:
        await do_work()
    finally:
        await redis.delete(name)

Costs:

  • A Redis instance ($20-40/month in the cloud).
  • Monitoring.
  • Backups (minimal, but they exist).
  • One more dependency in the stack.

After (advisory locks):

async def cron_with_advisory_lock():
    async with SessionLocal() as session:
        async with session.begin():
            async with advisory_xact_lock_ns(
                session, LockNamespace.CRON, hash("cron_name") & 0x7FFFFFFF
            ) as got:
                if not got:
                    return
                await do_work()

Costs:

  • 0. You already have PG.

Trade-off: you lose the automatic TTL. You mitigated it with TCP keepalive. For crons that sometimes take more than 10min, advisory locks are better (they don't release prematurely).

Decision: refactor to advisory locks → drop Redis. A simpler app, fewer dependencies, the same functional result.

This is the most common case where advisory locks win clearly.

Arguing the decision on your team

When someone proposes "let's add Redis for this":

Questions to ask:

  1. Do we need Redis for anything besides locks?

    • No → consider advisory locks.
    • Yes (cache, sessions) → a marginal trade-off.

  2. Does the lock need a TTL?

    • No → advisory locks lose nothing.
    • Yes → is it manageable with TCP keepalive? Probably yes. Or do we need an exact TTL? Redis.

  3. Multi-region or single-region?

    • Single → advisory locks are fine.
    • Multi → consider something stronger (etcd).

  4. Does the team know advisory locks?

    • Yes → use them.
    • No → teach them (this guide exists). It's worth it.

Positive argument: "If we don't already have Redis, adding it just for locks is one more dependency with an operational cost. PostgreSQL advisory locks give us the same thing without a new service."

Defensive argument: "Redis is the industry standard, but for our case (single-region, transactional, short scope), advisory locks are simpler and just as robust. We can migrate to Redis if we need automatic TTL or multi-region in the future."

Traps and common mistakes

1. "Redis is standard, let's go with that."

Standard for general cases. For your specific case, it may not be optimal. Evaluate the real trade-offs.

2. "Advisory locks don't scale."

PostgreSQL handles thousands of advisory locks without a problem. The scalability is similar to Redis for typical cases. The difference only matters at extreme throughput.

3. "Redis has a TTL, I need it."

Sometimes not really. If your lock accompanies a time-bounded operation (a 5min cron, an MV refresh), transaction-level advisory locks are safe without a TTL. The "I need a TTL" argument is often habit, not requirement.

4. "If I add advisory locks, I'll block myself on migrations."

Migrations run as a superuser that does NOT normally bypass advisory locks — but the locks your app holds do NOT affect DDL migrations (table locks are a different thing). No issue.

5. "My monitoring doesn't support advisory locks."

pg_locks is already available. Datadog/Prometheus can scrape it. A simpler setup than monitoring for a new Redis.

6. "Advisory locks aren't safe in a cluster."

If your PG is single-master with read-only replicas, the locks live on the master. The replicas don't participate. Only a problem if you have multi-master, rare in typical setups.

7. "Redlock is safer than advisory locks."

Redlock has controversies about its correctness ("How to do distributed locking" by Martin Kleppmann critiques it). PG advisory locks are simpler and more verifiable.

8. "ZK/etcd is better for everything."

Only if you already have the infra. Adding a ZK cluster just for locks is over-engineering in most apps.

Case by case: when to choose what

Case 1: a cleanup cron that runs every hour

  • Without Redis: transaction-level advisory locks. Zero overhead.
  • With Redis: a tie, a slight preference for advisory due to consistency.

Case 2: per-user rate limiting (1000 req/sec globally)

  • Redis. Extreme throughput and latency.
  • Advisory locks don't scale to this level.

Case 3: weekly MV refresh

  • Transaction-level advisory locks. Trivial.

Case 4: leader election between 5 instances of a service

  • ZK/etcd if you have it. Otherwise Redis with Redlock.
  • Advisory locks can, but with care about detecting dead leaders.

Case 5: idempotency keys in endpoints

  • Transaction-level advisory locks. Same PG, same tx, simple.
  • Redis with TTL if you want the idempotency key to expire automatically after N days.

Case 6: distributed cache invalidation

  • Redis Pub/Sub or some broker.
  • Advisory locks do NOT help here (they have no pub/sub).

Case 7: a job queue with multiple workers

  • Combo: SELECT FOR UPDATE SKIP LOCKED for job claiming + an advisory lock for a "worker active marker".
  • Or Celery (which internally uses Redis or RabbitMQ).

Exercise: defend the decision

For each scenario, which do you use and why?

Scenario 1: B2B SaaS with 50 tenants. A daily cron that recalculates per-tenant stats. Each cron takes 30min. PG primary + read replica. They don't have Redis.

Scenario 2: An API that receives webhooks. It needs idempotency based on webhook_id. If two requests with the same webhook_id arrive in parallel, one processes, the other gets a 409.

Scenario 3: A system with 10 microservices. Coordination of a "leader" between instances. Multi-region (US + EU).

Scenario 4: Rate limiting 1000 req/sec/user. An app with 100k concurrent users.

Scenario 5: A worker that processes a job queue. 5 workers, each taking jobs from a jobs table.

See solutions

Scenario 1: Advisory locks. With no Redis installed, they're the simplest. A single PG primary, a short-to-medium scope (30min). With TCP keepalive well configured, robust.

Scenario 2: Transaction-level advisory locks with a namespace per webhook_id. Natural idempotency in the transaction, no TTL needed (the record in the idempotency table is persistent).

Scenario 3: etcd or ZooKeeper. Multi-region + leader election + 10 services = a case for specific coordination.

Scenario 4: Redis. Extreme throughput, in-memory operations.

Scenario 5: SELECT FOR UPDATE SKIP LOCKED + an advisory lock for "worker active". The queue itself is PG (you don't need Celery). The worker-active marker is an advisory lock.

Key takeaway: the right answer depends on the context. The decision matrix isn't prescriptive — it's for reasoning.

Summary and next step

What you learned:

  • 3 options: advisory locks (PostgreSQL), Redis SETNX, ZooKeeper/etcd.
  • Decision matrix: setup complexity, latency, consistency, TTL, debugging, operational cost.
  • Advisory locks win when you already have PG, a short-to-medium scope, single-region, transactional.
  • Redis wins with a native TTL, extreme throughput, already in the stack, multi-master.
  • ZK/etcd wins with multi-region, complex coordination, large systems.
  • The "client dies" gotcha: ZK/etcd > Redis TTL > advisory locks (mitigated with TCP keepalive).
  • Migration Redis → advisory locks: many cases, dropping a dependency.

Before moving on, you should be able to:

  • Argue the choice of lock in a code review.
  • Recognize when Redis is really necessary vs habit.
  • Mitigate advisory-lock gotchas (TCP keepalive).
  • Defend against "let's add Redis" when advisory locks are enough.

In the next lesson we change topics within the module. Up to here, advisory locks. Now savepoints: the solution for batch processing that tolerates partial failures. You'll learn pure SQL (SAVEPOINT/ROLLBACK TO/RELEASE) and SQLAlchemy (session.begin_nested()), and why a simple try/except doesn't solve the problem.

Resources

  1. Martin Kleppmann — How to do distributed locking — a critique of Redlock.
  2. Citus Data — Distributed locking with PostgreSQL — real-world case.
  3. Redis Docs — Distributed locks — Redlock explained.
  4. ZooKeeper Recipes — Locks — the official pattern.
  5. etcd — Concurrency primitives — reference.
  6. PostgreSQL — TCP keepalive settings — to mitigate dead connections.

Lesson 05 of 08 — Module 6 — Advanced PostgreSQL for Backend Guide

← PreviousPattern from SQLAlchemy with a context managerNext →Savepoints: partial rollback within a transaction