Module 6: Optimistic Locking + Schema Versioning

You've reached the "Concurrency and Versioning" block. Modules 1-5 gave you the foundations to build production-ready APIs: safe pagination, defensive modeling, multi-tenancy, zero-downtime migrations. Now you're going to tackle two problems that show up the day your API has more than one active client simultaneously: concurrency conflicts and contract versioning.

The two topics look different but they share a central idea: assume something changed since the last time you saw it, handle the conflict when it happens. Optimistic locking applies it to data: two browsers editing the same task, the second one to save gets a 409 Conflict. Schema versioning applies it to the contract: your mobile client has an API version from 6 months ago, and your backend evolved — the new fields don't break the old client, the removed fields get deprecated gradually.

In this capsule we cover the mental framework, the closing scenario, and the module map. Capsule 02 opens with the topic's most important decision: optimistic vs pessimistic locking, when each one wins. Then we build the tools: SQLAlchemy's native version columns (cap 03), an informative 409 response (cap 04), the If-Match HTTP header (cap 05). And we close with schema versioning: compatible vs breaking changes (cap 06), deprecation headers (cap 07), and the mini-project that integrates everything (cap 08).

Where are we? Where are we going?

What you already know (modules 1 to 5):

Cursor pagination with robust tiebreakers (module 1).
Correct soft deletes with no anti-patterns (module 2).
Audit logs via triggers or lightweight event sourcing (module 3).
Multi-tenancy with Row-Level Security in PostgreSQL (module 4).
Zero-downtime migrations with the expand-contract pattern (module 5).

What you're going to build this time:

Two closely related disciplines. First, optimistic locking to handle concurrency in writes: SQLAlchemy 2.0's native version with version_id_col, translation to correct HTTP codes (412 and 409), responses with a useful body so the client can resolve the conflict. Second, schema versioning to evolve your API without breaking clients in production: backward-compatible changes, Deprecation and Sunset headers, contract testing.

Why this module comes here:

Modules 1-5 taught you to build a schema and migrate it. This one teaches you to live with it once you have diverse clients hitting it simultaneously. It's the first module where the problem isn't technical but social: two users wanting to modify the same thing, or a mobile client you can't force to update.

And it comes before module 7 (bulk operations) because optimistic locking applies to individual operations — the conceptual model changes when you work with bulk inserts/updates of 100k rows. Covering individual first, then bulk, is the logical order.

Professional objective

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

Decide between optimistic and pessimistic locking according to the conflict pattern and the cost of a retry. Know the decision matrix.
Implement native version columns in SQLAlchemy 2.0 with __mapper_args__ = {"version_id_col": Model.version} — without reinventing the pattern manually.
Catch StaleDataError and translate it to an HTTP 409 with an informative body: the current version, the changed fields, the resource's current state, the option to resolve.
Implement the If-Match header (RFC 7232) and a 412 Precondition Failed response for optimistic concurrency at the HTTP level.
Distinguish compatible changes from breaking ones in schema evolution: adding an optional field vs changing a type, adding an enum value vs renaming.
Apply a deprecation strategy with Deprecation: true and Sunset: <date> headers, plus usage metrics so you know when it's safe to remove the old field.
Argue against /v2/ URL versioning and reserve it for cases of an unavoidable breaking change, not for new features.

Why does this module matter?

Optimistic locking is the difference between an API that silently loses data and one that protects integrity. The classic scenario: two editors working on the same document. Without optimistic locking, the last one to save overwrites the first one's work — that change gets lost with nobody knowing. With optimistic locking, the second one gets a 409 Conflict with the information needed to reconcile. The difference between a "basic feature" and a "production-ready feature" is exactly this pattern.

Schema versioning is the difference between an API you can evolve and one that's frozen. The moment you have a mobile client in production, adding a field becomes a coordination event: is the client going to accept it? Is it going to break? Without versioning discipline, teams end up with APIs that can't change because any modification breaks one of the 17 deployed clients. With discipline (compatible changes + gradual deprecation), you evolve continuously without coordinating.

In the real senior backend dev role, this is the module that separates "someone who knows how to write CRUD" from "someone who maintains an API in production for years". Both skills show up in code review: when someone proposes an endpoint that overwrites with no version check, you spot it; when someone proposes /v2/ to add a field, you propose doing it backward-compatibly in /v1/.

For senior interviews, this topic comes up directly: "how would you handle concurrency when two users edit the same task?" or "how would you evolve an API that has 5 mobile client versions in production?". The answers without this module are vague. With this module they're specific: a version column with SQLAlchemy's version_id_col, a 409 response with {current_version, changed_fields, current_state}, a deprecation header instead of /v2/.

A scenario that illustrates the module

Your team launched a task management app (TaskFlow) a year ago. It worked well at first, now it has growing problems:

Problem 1: lost edits. Users report that updates they see confirmed on screen "disappear" minutes later. Investigating, you discover the pattern: two browser tabs editing the same task, both do PUT /tasks/123 almost simultaneously, the second overwrites the first. There's no error — client B saw "saved successfully" but its version is the one that ended up persisted, overwriting client A's changes.

Problem 2: the old mobile client breaking. Your team added a priority field to the Task model. The backend returns priority in every response. Mobile app v3.2 (used by 30% of users) blows up because its Pydantic-equivalent model is on extra='forbid' — it doesn't accept unknown fields. Users with the old app see the app crash every time they list tasks.

Problem 3: the impossibility of changing a field's type. You need to change due_date from DATE to TIMESTAMPTZ to support time zones. But you have 5 client versions in production consuming the field as a DATE. Any change breaks somebody.

Without this module, the solutions are patches: add JS that detects conflicts in the frontend, make the mobile app resilient with a generic catch, don't change the type (add due_date_v2 and keep both forever).

With this module:

Capsule 02 (the decision matrix): you understand that TaskFlow is a typical optimistic locking case — rare conflicts, cheap retries.
Capsule 03 (version columns): you add version to Task with __mapper_args__. SQLAlchemy handles everything automatically.
Capsule 04 (StaleDataError → 409): you catch the exception and return a 409 with the body {"current_version": 7, "your_version": 5, "changed_fields": ["status"], "current_state": {...}}. The frontend shows a "somebody else edited this, do you want to review?" UI.
Capsule 05 (If-Match): the frontend sends If-Match: 5 in every PUT, the server returns 412 Precondition Failed when the header's version doesn't match.
Capsule 06 (compatible changes): you add priority as optional with a default. You document that clients should use extra='ignore' in their models. The old clients keep working — they ignore the new field.
Capsule 07 (deprecation): for the due_date type change, you don't change it — you add due_at (timestamptz) and deprecate due_date with the header Deprecation: true, Sunset: <date> for 6 months. You track usage of the old field in logs. When usage < 1%, you remove it.
Capsule 08 (the integrative project): you consolidate everything in TaskFlow with three schema versions (v1 base, v2 with priority, v3 with due_date deprecated and due_at added), demonstrating that clients of each version work simultaneously.

The result: TaskFlow supports continuous evolution without breaking existing clients, and editing conflicts get handled with clear UX instead of silent data loss.

Module map

Capsule	Topic	What you'll learn
01	Module introduction	You are here. The "assume change, handle conflict" mental framework, the TaskFlow scenario.
02	Optimistic vs pessimistic locking	The decision matrix: conflict probability × retry cost. When each one wins.
03	Version columns in SQLAlchemy 2.0	The native implementation with `version_id_col`, counter vs timestamp.
04	`StaleDataError` → HTTP 409 with an informative body	Catching, translating, what to include in the response so the client can resolve.
05	The `If-Match` header and HTTP 412	RFC 7232 applied. Optimistic concurrency at the HTTP level, ETags.
06	Schema versioning: compatible vs breaking	Adding an optional field, enum values, anti-patterns.
07	Deprecation strategy	The `Deprecation` and `Sunset` headers (RFC 8594), usage metrics, the anti-`/v2/` bias.
08	Project: TaskFlow with optimistic locking + 3 schema versions	Consolidation. v1 base, v2 with priority, v3 with due_date deprecated.

The narrative flow: first the fundamental decision (02). Then we build the technical pieces (03-05). Then we switch to the module's other side (schema versioning, 06-07). And we close with a project that ties the two disciplines together (08).

Connection with the integrative project

The guide's final project (Module 8 — TaskFlow) implements everything in this module:

Optimistic locking in PUT /tasks/{id}:

An If-Match: <version> header required in every request.
A 412 response when the header's version isn't the current one.
A 409 response when the UPDATE's version check fails (a race between the fetch and the write).
An informative response body: the current version, the changed fields, the current state.
A test that simulates two concurrent clients editing the same task.

Schema versioning with three versions of TaskResponse:

v1: the base fields (id, title, status, created_at).
v2: adds priority (compatible — old clients ignore it with extra='ignore').
v3: adds due_at (timestamptz), deprecates due_date (date) with a Deprecation: true header.
Contract testing with Schemathesis verifying that clients of each version work.

Module 8's mini-project is focused: only TaskFlow optimistic locking + schema evolution. Module 8's final project (from #13) is the complete version with multi-tenancy + audit logs + cursor pagination.

What is NOT covered in this module

Deep pessimistic locking (SELECT ... FOR UPDATE, FOR SHARE, NOWAIT, SKIP LOCKED): we cover the concept in capsule 02 and the decision matrix, but the deep implementation belongs to guide #14 (Advanced PostgreSQL — the concurrency patterns section).
Distributed locking with advisory locks: advanced, it belongs to #14 too.
Conflict-free Replicated Data Types (CRDTs) and operational transformation: advanced techniques for real-time collaboration (Google Docs style). Out of scope.
GraphQL versioning: this module focuses on REST. The principles apply but the implementations differ.
Wire-format-level versioning (Protobuf, Avro): specific to gRPC/Kafka stacks, not REST.
Client migration tooling (forced upgrades, per-version feature flags): it belongs to mobile/frontend guides.

Traps to avoid while taking the module

1. "Optimistic locking is always better than pessimistic, I'm going to use it always." False. Pessimistic wins when conflicts are frequent (a job queue, inventory) or when the cost of a retry is high (an expensive operation with irreversible side effects). Capsule 02 gives you the concrete decision matrix.

2. "I'm going to implement the version column manually with a check in every UPDATE." SQLAlchemy 2.0 supports it natively. Implementing it manually is writing code that already exists, with subtle bugs the native implementation has already solved. Capsule 03.

3. "Returning a 409 alone is enough, the client will know what to do." It doesn't know. A 409 with no further information is useless to the client — it doesn't know what changed, what version the server has, or how to resolve it. Capsule 04 covers the mandatory rich response.

4. "To add a field to the API, I'm going to ship /v2/." A terrible idea. Adding fields is backward-compatible — old clients ignore them. /v2/ forces you to maintain two parallel codebases, almost always unnecessarily. Capsules 06-07 cover when it IS justifiable (rarely).

5. "My client is Pydantic, I don't need to worry about forward-compatibility." Pydantic v2's default is extra='ignore' (compatible). But some teams switch to extra='forbid' for strictness and that breaks forward-compat. Documenting the decision is the responsibility of whoever designs the API. Capsule 06.

6. "Schemathesis is overkill, my integration tests are enough." Integration tests test your code against your current schema. Schemathesis tests that the schema is right, generating edge cases automatically. You catch bugs manual tests don't find. Capsule 06.

Self-assessment questions

Before starting this module, can you answer these questions?

What's the difference between optimistic and pessimistic locking, and when do you prefer each one?
What exactly happens when SQLAlchemy detects a stale write with version_id_col?
What information should a 409 response include so the client can resolve it?
What are the HTTP codes 412 and 428 and when are they used?
Which changes to a schema are backward-compatible vs breaking?
What do the Deprecation and Sunset headers say?
Why is /v2/ URL versioning generally a bad idea?

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

Evidence of success

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

In code review, you spot an endpoint that does a PUT with no version check and propose the fix with SQLAlchemy's version_id_col.
When somebody proposes "let's ship /v2/ to add a field", you argue why doing it backward-compatibly is better and show how.
You implement a PUT /resources/{id} endpoint with an If-Match header and a 412/409 response with an informative body.
You design the evolution of a schema with three versions (base → add a field → deprecate another) without breaking any.
In a senior interview, you walk confidently through the optimistic locking + schema versioning pattern with code and cases.

We start in the next capsule

We start with capsule 02: optimistic vs pessimistic locking. The topic's fundamental decision. You're going to see concrete cases where each one wins, the decision matrix with two axes (conflict probability × retry cost), and the typical anti-patterns of choosing the wrong one. It's the conceptual foundation the rest of the module assumes.

Before moving on, make sure you have PostgreSQL 16, FastAPI 0.110+, SQLAlchemy 2.0+, Pydantic 2+ running. For the module's mini-project you also need Schemathesis (pip install schemathesis) and an HTTP client that supports custom headers (curl, httpie, Postman).

Resources for the module

SQLAlchemy 2.0 — Versioning Counter — the official reference for version_id_col.
Martin Fowler — Optimistic Offline Lock — the classic explanation of the pattern.
RFC 7232 — Conditional Requests (If-Match) — the HTTP standard for optimistic concurrency.
RFC 8594 — Sunset HTTP Header — the standard for deprecation.
Deprecation HTTP Header (Internet-Draft) — the active draft of the Deprecation header.
Stripe API Versioning — a real case of versioning at scale, maintaining versions for years.
Schemathesis — contract testing for FastAPI/OpenAPI.
PostgreSQL Wiki — Lock Modes — a reference for understanding pessimistic locks.

Module 6 — SQL Patterns for Production APIs Guide

← PreviousModule 5 deliverable: a live zero-downtime migration Next →Optimistic vs pessimistic locking: when each one wins