Module 6: Advisory Locks + Savepoints

Advanced concurrency in production has two common needs that classic transactions don't solve elegantly: distributed locks ("only one instance should process this job") and partial rollback ("if one item in the batch fails, I don't want to lose the others"). PostgreSQL has the answer to both — advisory locks and savepoints — and both are underused in the Python ecosystem.

The industry default for distributed locks is Redis (SETNX, Redlock). But PostgreSQL advisory locks are frequently the better option: ACID, transactional, no extra service. If your app already uses PostgreSQL and the only thing you use Redis for is locks, you could drop an entire dependency.

For partial rollback, the pattern is savepoints. SQLAlchemy exposes session.begin_nested(), which creates a savepoint automatically. It's the difference between "a batch that processes 99 items and one error breaks everything" and "a batch that processes 99 successfully and reports item 50 as a failure".

In this lesson we introduce the mental framework, real scenarios, and the module map. Lessons 02-05 cover advisory locks. Lesson 06 covers savepoints. Lesson 07 shows the combined pattern. Lesson 08 closes with a mini-project: a job runner that uses both.

Where are we? Where are we going?

What you already know (modules 1 through 5):

Deep JSONB with SQLAlchemy + GIN/GiST indexing (module 1).
Full-Text Search with tsvector/tsquery and trigram (modules 2-3).
Declarative partitioning with range/list/hash (module 4).
Materialized views with refresh strategies (module 5).

What you'll build this time:

Two flagship PostgreSQL features for concurrency: advisory locks (non-transactional locks with arbitrary keys — useful for distributed locking without Redis) and savepoints (nested transactions with partial rollback). You'll learn the central advisory-lock decision (session-level vs transaction-level), the pattern from SQLAlchemy with a context manager, the decision matrix vs Redis, and how to combine advisory locks with savepoints for robust job runners.

Why this module comes here:

Modules 1-5 covered PostgreSQL features applied to a single transaction. This module takes you into the space of coordination between transactions — locks that survive across transactions, rollback of a subset of operations. It's the last conceptual step before module 7 (useful extensions) and the final capstone project.

And there's a direct connection to module 5: the cron that refreshes materialized views should use advisory locks to prevent two concurrent crons from attempting a simultaneous refresh.

Professional goal

By the end of this module you'll be able to:

Distinguish pg_advisory_lock(key) (blocks until acquired) from pg_try_advisory_lock(key) (tries without blocking).
Distinguish session-level from transaction-level: when each one wins.
Implement the pattern from SQLAlchemy with a Pythonic context manager.
Argue advisory locks vs Redis vs ZooKeeper with a concrete decision matrix.
Use savepoints via SAVEPOINT SQL or session.begin_nested().
Implement batch processing that tolerates partial failures: 99 items succeed + 1 fail, without losing the 99.
Combine advisory lock + savepoints in job runners for single-instance + partial rollback.
Recognize limitations: advisory locks without an automatic TTL, savepoint overhead in huge batches.

Why does this module matter?

Advisory locks can remove Redis from your stack. That's not an exaggeration:

Cron that recalculates derived columns: needs a single-instance lock. Default: Redis. Alternative: pg_try_advisory_lock. One less dependency.
Job runner for async processing: needs a single worker per job. Default: Redis-based queue (Celery, RQ). Alternative: pg_advisory_lock with a job queue in PostgreSQL (SELECT FOR UPDATE SKIP LOCKED).
Data reconciliation: needs to avoid concurrent runs. Default: Redis SETNX. Alternative: pg_try_advisory_xact_lock.

If your app needs Redis only for some of these cases, advisory locks can simplify the stack. One less dependency = one less point of failure, lower cost, less complexity.

Savepoints transform batch processing. A typical case:

# Without savepoints
for item in batch:
    try:
        process(item)
    except IntegrityError:
        continue  # ❌ The transaction is in an aborted state — the whole batch fails

# With savepoints
for item in batch:
    try:
        with session.begin_nested():
            process(item)
    except IntegrityError:
        log_error(item)  # ✅ Only this item rolls back, the rest continues

The difference is between "1 item breaks everything" and "1 item gets reported as an error, the 99 get persisted".

In a real senior backend dev role, knowing these patterns separates "someone who knows basic SQL" from "someone with real operational experience". Advisory locks rarely show up in tutorials but are common in serious codebases. Savepoints are fundamental for any fault-tolerant batch processing.

For senior interviews, this topic shows up in questions like "you have a cron that runs every 5 minutes but sometimes the runs overlap, how do you prevent it?" or "your import batch fails when one item has invalid data, how do you make it tolerant?". The answers without this module are vague. With this module they're specific and demonstrate experience.

A scenario that illustrates the module

Your team launched BlogPlatform (the app for the guide's final project). It works fine at first. Three problems show up in production:

Problem 1: a re-indexing cron that duplicates itself.

A cron runs every hour to recalculate tsvector (full-text search) on modified posts. Sometimes the previous cron takes more than an hour, and a second cron starts before the first one finishes. Result: two processes doing the same work, row locks, degraded performance, weird "row modified concurrently" errors.

Problem 2: a batch import that fails on item 50 of 100.

The POST /posts/import endpoint accepts a CSV with 100 posts. Item 50 has a duplicate slug → IntegrityError. The entire transaction is aborted, the previous 49 items aren't saved, and the client gets a generic error without knowing what the problem was.

Problem 3: a duplicated materialized view refresh.

The top_posts_weekly MV refreshes every Sunday at 2am. Sometimes it runs twice because it overlaps with a maintenance job. PostgreSQL does both refreshes, spending twice the resources.

Without this module, the fixes are clumsy: add Redis for distributed locks (more infra), implement custom "skip if running" logic (fragile), accept the problem (technical debt).

With this module:

Lessons 02-04 (advisory locks): you implement pg_try_advisory_lock(key) before the re-indexing cron. If another one is running, the second exits immediately without doing anything. Problem 1 solved.
Lesson 05 (decision matrix): you justify why advisory locks > Redis for your case. You convince the team not to add a dependency.
Lesson 06 (savepoints): you refactor POST /posts/import with session.begin_nested() per item. Item 50 fails, items 1-49 and 51-100 succeed, and the response details item 50 as an error. Problem 2 solved.
Lesson 07 (combined pattern): the MV refresh cron uses pg_try_advisory_lock before REFRESH MATERIALIZED VIEW CONCURRENTLY. Problem 3 solved.
Lesson 08 (mini-project): you integrate everything into a job runner that processes a task queue with a global advisory lock + a savepoint per task.

Result: three problems solved without adding Redis, with simpler code than the workarounds, and reusable patterns for future cases.

Module map

Lesson	Topic	What you'll learn
01	Module introduction	You're here. Framework, scenario, map.
02	Advisory locks: the basic pattern	`pg_advisory_lock` vs `pg_try_advisory_lock`, keys, a simple example.
03	Session-level vs transaction-level	The central decision, cases for each, gotchas.
04	Pattern from SQLAlchemy with a context manager	A reusable Pythonic implementation.
05	Decision matrix: advisory locks vs Redis vs ZooKeeper	When to use each, concrete criteria.
06	Savepoints: SQL and `session.begin_nested()`	Nested transactions, partial rollback, batch processing.
07	Combined pattern: advisory lock + savepoints	Robust job runners, connection to the final project.
08	Mini-project: job runner with a task queue	Full implementation with benchmarks.

Narrative flow: advisory locks in depth first (02-05), then savepoints (06), then the combination (07), and a project to close (08).

Connection to the capstone project

The guide's final project (module 8 — Blog API refactor) uses this module in two places:

FTS re-indexing cron: a periodic trigger that recalculates tsvector on modified posts. Protected with pg_try_advisory_lock to avoid concurrent runs.
top_posts_weekly MV refresh cron: a scheduled job that refreshes the materialized view every week. Protected with an advisory lock.
Bulk import endpoint (inherited from guide #13, module 7): refactored to use session.begin_nested() per item, allowing partial success instead of all-or-nothing.

The module 8 mini-project (from #14) is the complete job runner pattern that you'll be able to reuse.

What this module does NOT cover

Pessimistic row-level locking (SELECT FOR UPDATE): covered in guide #13, module 6.
Optimistic locking with version columns: guide #13, module 6.
Distributed locking with Kafka, etcd, Consul: different architectures, out of scope.
Two-phase commit (PREPARE TRANSACTION): a specific case, rare in a typical backend.
PostgreSQL subtransactions beyond savepoints: they don't exist as a separate concept in PostgreSQL.
Operating-system-level locks (file locks, etc.): a different topic.
Other PostgreSQL locks (LOCK TABLE, etc.): mentioned in passing.

Traps to avoid while taking the module

1. "Advisory locks are an exotic feature, I won't use them." False. Any app with cron jobs, periodic batch processing, or async jobs eventually needs distributed locking. Advisory locks are the simplest option if you already have PostgreSQL.

2. "I'll use Redis for everything, it's standard." For distributed locks specifically, advisory locks win in many cases. Lesson 05 gives you the decision matrix with concrete criteria.

3. "Savepoints are glorified try/except." False. try/except doesn't roll back the changes in the DB when there's an error — it just silences the exception. After the first error, the transaction is in an aborted state and you CAN'T continue. Savepoints DO let you roll back changes and continue. Lesson 06 shows the difference.

4. "I'll put a savepoint on every query, just in case." Each savepoint has overhead. For huge batches (millions of items), this adds up. Savepoints are for medium batches (hundreds to thousands of items) with tolerance for partial failures.

5. "Advisory locks have a TTL like Redis." NO. If your process dies while holding a session-level advisory lock, the lock stays until the connection closes. There are mitigations (transaction-level when possible, monitoring) but it requires care. Lesson 03 covers this.

6. "session.begin_nested() is BEGIN inside BEGIN." Not exactly. PostgreSQL doesn't support real nested transactions. session.begin_nested() creates a SAVEPOINT under the hood. Similar behavior, but an important detail for understanding errors when they show up.

Self-assessment questions

Before starting this module, can you answer these questions?

What is a distributed lock and when do you need it?
What's the difference between pg_advisory_lock and pg_try_advisory_lock?
What's the difference between session-level and transaction-level advisory locks?
Why is try/except not enough for fault-tolerant batch processing?
What is a savepoint and how does it differ from a transaction?
When would you use advisory locks instead of Redis?
What happens if your process dies while holding a session-level advisory lock?

If you hesitate on more than three, the module is well calibrated for you.

Signs of success

By the end of the module, you'll know you succeeded if:

In a code review, you spot a cron without a distributed lock and propose pg_try_advisory_lock.
You implement batch processing with session.begin_nested() per item.
You argue against adding Redis for cases where advisory locks are enough.
You recognize when session-level vs transaction-level is the right answer.
A module mini-project on GitHub demonstrating the complete pattern.

We start in the next lesson

We start with lesson 02: advisory locks, the basic pattern. You'll see pg_advisory_lock(key) and pg_try_advisory_lock(key) with simple examples, the two key forms (a single integer or an integer/integer pair), and a first real case: a cron protected against concurrent runs. It's the foundation the next lessons build on.

Before moving on, make sure you have PostgreSQL 16, FastAPI 0.110+, SQLAlchemy 2.0+ with asyncpg, and a connection you'll use to experiment (it can be via Docker compose or local).

Module resources

PostgreSQL Docs — Advisory Locks — complete official reference.
PostgreSQL Docs — SAVEPOINT — reference.
SQLAlchemy 2.0 — Session.begin_nested — reference.
Citus Data — Advisory Locks for distributed coordination — real-world case.
Sidekiq — Job Uniqueness with Advisory Locks — an applicable Ruby pattern.
GitLab — Database concurrency patterns — operational reference.
Bruce Momjian — PostgreSQL locking internals — technical foundation.

Module 6 — Advanced PostgreSQL for Backend Guide

← PreviousModule 5 deliverable: blog dashboard with materialized views Next →Advisory locks: the basic pattern