Module 6: Optimistic Locking + Schema Versioning

Optimistic vs pessimistic locking: when each one wins

Next →

When two concurrent transactions try to modify the same row, there are two fundamental strategies. Pessimistic locking: lock the row when you read it, nobody else can modify it until your transaction finishes. Optimistic locking: read with no lock, assume nobody else is going to modify it, validate at the moment of writing; if somebody else modified it in the meantime, fail and retry.

Each strategy has cases where it shines and cases where it's a disaster. The wrong choice leads to deadlocks, massive contention, or silent data loss. The right choice is invisible — the app just works under concurrency.

In this capsule you're going to learn the decision matrix with two axes (conflict probability × retry cost), see concrete cases where each strategy wins, and the most common anti-patterns. By the end you'll be able to defend in code review why endpoint X uses optimistic and endpoint Y uses pessimistic with specific arguments.

Pessimistic locking: lock first

The classic SQL pattern is SELECT ... FOR UPDATE:

BEGIN;

-- Lock the row
SELECT id, balance FROM accounts WHERE id = 123 FOR UPDATE;
-- Another transaction trying to read with FOR UPDATE waits

-- Modify
UPDATE accounts SET balance = balance - 100 WHERE id = 123;

COMMIT;

FOR UPDATE takes a row-level lock. Other transactions trying to SELECT ... FOR UPDATE or UPDATE/DELETE that row wait until your transaction does a COMMIT or ROLLBACK. A plain SELECT (without FOR UPDATE) can still read — PostgreSQL uses MVCC to show the previous version.

Variants:

-- Wait for the lock indefinitely (the default)
SELECT ... FOR UPDATE;

-- Fail immediately if it's locked
SELECT ... FOR UPDATE NOWAIT;

-- Skip the locked rows (useful for job queues)
SELECT ... FOR UPDATE SKIP LOCKED;

-- A weaker lock that allows other FOR SHARE reads
SELECT ... FOR SHARE;

When pessimistic wins

Case 1: high contention over few resources.

A job queue: many workers competing for the next task.

-- Worker fetching next job
SELECT id FROM jobs
WHERE status = 'pending'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;

SKIP LOCKED makes each worker take a different row with no waiting. With no lock, two workers could take the same job and run it twice.

Case 2: financial or critical operations with side effects.

A bank transfer: deduct from A, add to B. If you fail halfway from a race condition, you lost money.

async def transfer(session, from_id, to_id, amount):
    # Lock both rows in a consistent order to avoid a deadlock
    accounts = await session.execute(
        select(Account)
        .where(Account.id.in_([from_id, to_id]))
        .order_by(Account.id)
        .with_for_update()
    )
    accounts = accounts.scalars().all()

    from_acc = next(a for a in accounts if a.id == from_id)
    to_acc = next(a for a in accounts if a.id == to_id)

    if from_acc.balance < amount:
        raise InsufficientFunds()

    from_acc.balance -= amount
    to_acc.balance += amount

    await session.commit()

With no lock, two concurrent transfers from the same account could both pass the sufficient-balance check and result in a negative balance.

Case 3: the retry cost is high.

If the operation takes 30 seconds (calls to external APIs, PDF generation, etc.), retrying is expensive. Better to lock and guarantee it runs once.

When pessimistic loses

Problem 1: scaling. Pessimistic creates bottlenecks. If all the users edit the same "popular list", they all wait in a queue for the lock. With hundreds of concurrent users, latency spikes.

Problem 2: deadlocks. If you lock A and then B, while another locks B and then A, both wait forever. PostgreSQL detects deadlocks and aborts one, but detecting the deadlock is extra work and the abort causes visible failures.

Problem 3: long transactions. Pessimistic locking requires keeping the transaction open while you edit. If the "edit" is a UI with a human (filling out the form), the transaction can last minutes — holding locks that entire time. This blocks the whole app.

Problem 4: not-online code paths. If the lock gets taken from inside a long transaction with a slow network or a disconnected client, the lock stays held longer than necessary.

Optimistic locking: validate at write time

The pattern is to add a version column or timestamp to the table and validate it in every UPDATE.

-- Schema
CREATE TABLE tasks (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    status TEXT NOT NULL,
    version INTEGER NOT NULL DEFAULT 1
);

-- Read with no lock
SELECT id, title, status, version FROM tasks WHERE id = 123;
-- Returns: id=123, title='Fix bug', status='pending', version=5

-- Later, try an UPDATE with a version check
UPDATE tasks
SET title = 'Fix critical bug',
    status = 'in_progress',
    version = 6
WHERE id = 123 AND version = 5;
-- If the version changed in the meantime (somebody else updated), affected rows = 0

The key pattern: the UPDATE checks the version atomically. If another UPDATE happened while you read, the version is no longer 5 — the query affects no rows, and your app knows there was a conflict.

result = await session.execute(
    update(Task)
    .where(Task.id == 123, Task.version == 5)
    .values(title='Fix critical bug', status='in_progress', version=6)
)

if result.rowcount == 0:
    raise OptimisticLockConflict("Task was modified by another user")

When optimistic wins

Case 1: rare conflicts.

Two browser tabs editing the same document is possible but infrequent. Most saves don't compete with anybody. Optimistic allows high concurrency (there are no locks blocking reads), and when a conflict happens (rarely), the app handles it.

Case 2: UX with a human.

The user opens the form, edits for 5 minutes, saves. Pessimistic requires a lock during those 5 minutes — blocking other users. Optimistic doesn't lock anything until the final save, and it only fails if somebody else saved in between.

Case 3: a cheap retry.

If the operation is fast (an UPDATE of one field), retrying is cheap. Optimistic can fail and be retried with no problem.

Case 4: scaling.

With no locks blocking reads, optimistic scales better. Web apps with thousands of concurrent users editing different resources have no contention.

When optimistic loses

Problem 1: frequent conflicts. If most saves collide with others, the app spends more time retrying than moving forward. Pessimistic with a queue is more efficient.

Problem 2: irreversible side effects. If your UPDATE triggers an email or charges a card, "fail and retry" can cause double emails or double charges. Pessimistic guarantees it runs once.

Problem 3: logic that depends on reading-and-modifying atomically. An incremental counter (balance = balance + amount): if two do optimistic based on version=5, both can succeed if they only check the version, not the balance. Pessimistic with FOR UPDATE or the UPDATE ... SET balance = balance + ? pattern (which is atomic in itself) are the alternatives.

The decision matrix

Two axes:

  • X axis — Conflict probability: what fraction of simultaneous operations touch the same row?
  • Y axis — Retry cost: how expensive is it to retry the operation if it fails?
Conflict probability \ Retry costLow (< 100ms, idempotent)High (seconds, side effects)
Low (<5%)OptimisticOptimistic (with a clear UI to resolve)
High (>30%)Pessimistic or an async queuePessimistic, mandatory

Typical cases in the matrix:

  • Editing a task: low probability, cheap retry → optimistic.
  • A job queue: high probability, expensive retry (a duplicated job) → pessimistic with SKIP LOCKED.
  • Inventory during Black Friday: high probability, cheap retry → pessimistic (Black Friday's natural queue favors it).
  • A long-running calculation: low probability, expensive retry → optimistic with care or idempotency keys.
  • A like counter on a viral post: extremely high probability → neither optimistic nor pessimistic; use Redis or an atomic counter increment (UPDATE ... SET likes = likes + 1).

Hybrid patterns

Sometimes neither of the two pure strategies fits. There are alternatives.

1. Idempotency keys

The client generates a UUID per operation and sends it on every retry. The server stores the processed UUIDs; if it gets the same one twice, it doesn't re-execute.

@router.post("/orders")
async def create_order(
    request: OrderRequest,
    idempotency_key: str = Header(...),
    db: AsyncSession = Depends(get_db),
):
    # Check if we already processed this key
    existing = await db.scalar(
        select(IdempotencyRecord)
        .where(IdempotencyRecord.key == idempotency_key)
    )
    if existing:
        return existing.response_body  # Return the cached response

    # Create the order
    order = Order(...)
    db.add(order)
    db.add(IdempotencyRecord(key=idempotency_key, response_body=...))
    await db.commit()

    return order

Useful for POSTs with side effects (charges, emails). It allows a safe retry with no pessimistic locking.

2. Queue + worker

Instead of modifying the resource from the synchronous endpoint, you put a message on a queue and a single-threaded worker processes it.

@router.post("/process")
async def enqueue_processing(request, db):
    job = ProcessingJob(payload=request.model_dump())
    db.add(job)
    await db.commit()
    return {"status": "queued", "job_id": job.id}

The worker processes jobs one by one (or with pessimistic FOR UPDATE SKIP LOCKED). No contention in the synchronous endpoint — the client gets an immediate response.

3. An atomic UPDATE

For cases like counters, you don't need locking — a single UPDATE is atomic.

-- Atomic: PostgreSQL handles the concurrency
UPDATE posts SET likes = likes + 1 WHERE id = 42;

There's no race here. PostgreSQL serializes the UPDATEs implicitly.

4. A pessimistic lock only when there's a signal of conflict

A hybrid: read optimistically first, and if you detect a signal of a probable conflict, redo it with a lock.

# Read with no lock (fast in the common case)
task = await session.get(Task, task_id)

if task.recent_edit_at > datetime.now() - timedelta(seconds=10):
    # There's a recent edit — high probability of conflict
    # Redo it with a lock
    task = await session.execute(
        select(Task).where(Task.id == task_id).with_for_update()
    )

Advanced, not always worth the extra complexity.

Traps and common mistakes

1. Using pessimistic always "to be safe."

Locks degrade throughput drastically under concurrency. Apps with hundreds of users suffer massive contention. Optimistic is the default for typical CRUD.

2. Using optimistic for counters.

If two do UPDATE counters SET value = X simultaneously with the value calculated in the app, optimistic with a version can fail but the "lost" increment gets noticed. For counters, an atomic UPDATE (SET value = value + 1) is the answer.

3. SELECT ... FOR UPDATE with no explicit BEGIN/COMMIT.

# ❌ Anti-pattern
result = await session.execute(
    select(Account).where(Account.id == 1).with_for_update()
)
# The lock gets released when the session closes, but if there are errors, it isn't clear

Better to use explicit transactions or a context manager:

async with session.begin():
    account = await session.execute(
        select(Account).where(Account.id == 1).with_for_update()
    )
    # ... operations ...
# The lock gets released when leaving the context (commit or rollback)

4. A lock in queries with a JOIN without understanding the behavior.

SELECT * FROM a JOIN b ... FOR UPDATE locks both tables. If you only need to lock a, use FOR UPDATE OF a:

SELECT * FROM accounts a
JOIN customers c ON a.customer_id = c.id
WHERE a.id = 1
FOR UPDATE OF a;  -- Only locks accounts

5. Not detecting deadlocks in pessimistic.

If two transactions lock resources in the opposite order, deadlock. Best practice: always take locks in the same order (e.g. by ascending ID). PostgreSQL detects deadlocks but transactions still abort.

6. Optimistic with a version you forget to update.

If your UPDATE doesn't include version = version + 1, the counter doesn't advance, and future checks use the old version — the equivalent of not having optimistic locking. Native SQLAlchemy handles it automatically (capsule 03).

7. Mixing optimistic on some fields and pessimistic on others with no clear reason.

Inconsistency confuses and creates bugs. If the table is a "human-editable domain" one, use optimistic on all the fields. If it's a "processing queue" one, use pessimistic. Mixing requires clear documentation.

8. Not considering SERIALIZABLE as an alternative.

PostgreSQL supports the SERIALIZABLE isolation level, which detects conflicts automatically with no need for version columns. The tradeoff: transactions can abort with serialization_failure and the client has to retry. Useful in specific cases but less common than explicit optimistic locking.

Exercise: classify cases according to the decision matrix

For each case, determine:

  • Optimistic or pessimistic?
  • Which specific pattern (a version column, FOR UPDATE, FOR UPDATE SKIP LOCKED, an atomic UPDATE, an idempotency key, a queue + worker)?
  • Justify it.

Case 1: an Evernote-style notes app. The user edits a note for minutes before saving. Multiple devices of the same user can have the note open.

Case 2: the backend of an online game. When two players attack the same monster, decrementing the monster's HP.

Case 3: e-commerce with limited stock. Black Friday: 1000 people trying to buy the last 50 units of the popular product.

Case 4: a payments API. An endpoint that charges a card. The client does an automatic retry on timeout.

Case 5: a support ticket system. An agent edits the ticket. Another agent can assign it to themselves simultaneously.

Case 6: a visit counter on the blog's homepage.

See solutions

Case 1 (Evernote): optimistic with a version column.

  • Conflict probability: low (the same user, different devices, it's rare both edit at the same time).
  • Retry cost: low.
  • UX: if there's a conflict, show a diff and let the user choose which one wins or merge.

Case 2 (game HP): pessimistic with FOR UPDATE or an atomic UPDATE.

  • Conflict probability: high (multiple players attacking concurrently).
  • Retry cost: high (losing a hit degrades the game's UX).
  • Better: UPDATE monsters SET hp = hp - ? WHERE id = ? AND hp > 0 (atomic, avoids negative HP).

Case 3 (Black Friday): pessimistic with FOR UPDATE or an atomic UPDATE.

  • Conflict probability: very high.
  • Retry cost: medium.
  • Atomic UPDATE: UPDATE products SET stock = stock - 1 WHERE id = ? AND stock > 0. If rowcount=0, no stock.

Case 4 (payments): an idempotency key + queue.

  • Conflict probability: low (each charge is unique).
  • Retry cost: EXTREMELY high (a double charge is a disaster).
  • An idempotency key is mandatory. A queue to serialize the actual processing.

Case 5 (tickets): optimistic with a version column.

  • Conflict probability: low (it's rare for two agents to edit the exact same ticket at the same second).
  • Retry cost: low.
  • UX: a 409 response with info on who edited what, so the agent can decide.

Case 6 (page views): an atomic UPDATE or a Redis incr.

  • Conflict probability: very high (every hit increments).
  • Retry cost: low (losing a few counts is OK).
  • Atomic: UPDATE pages SET views = views + 1 WHERE id = ? or better: a counter in Redis with periodic sync to PostgreSQL.

The key lesson: the matrix doesn't give a single answer — it gives a guide. You have to consider the specific case and choose the simplest pattern that satisfies the requirements.

Summary and next step

What you learned:

  • Pessimistic locking (SELECT ... FOR UPDATE): locks resources when reading them, others wait. Ideal when conflicts are frequent or a retry is expensive.
  • Optimistic locking (a version column + a check in the UPDATE): doesn't block reads, validates at write time. Ideal when conflicts are rare and a retry is cheap.
  • The decision matrix: two axes (conflict probability × retry cost). Optimistic wins in typical human CRUD; pessimistic wins in queues, financial operations, high contention.
  • Hybrid patterns: idempotency keys, queue + worker, an atomic UPDATE, a conditional lock.
  • Anti-patterns: pessimistic always out of fear, optimistic on counters, locks with no explicit transactions, deadlocks from an inconsistent order.

Before moving on, you should be able to:

  • Look at an endpoint and argue whether it should use optimistic or pessimistic with specific reasons.
  • Identify an atomic UPDATE as an alternative to locking on counters.
  • Explain FOR UPDATE SKIP LOCKED and its use case (job queues).
  • Recognize when idempotency keys are the right answer (POSTs with side effects).

In the next capsule we go to the specific implementation: native version columns in SQLAlchemy 2.0. You're going to see __mapper_args__ = {"version_id_col": Model.version} in action, how SQLAlchemy handles the automatic increment and raises StaleDataError with no extra code. And the alternative with a timestamp instead of a counter, with its trade-offs.

Resources

  1. PostgreSQL Docs — Explicit Locking — the complete reference for lock modes.
  2. Martin Fowler — Optimistic Offline Lock — the classic reference.
  3. Martin Fowler — Pessimistic Offline Lock — the counterpart.
  4. FOR UPDATE SKIP LOCKED for queues — a real use case.
  5. Vlad Mihalcea — Optimistic vs Pessimistic locking — an analysis with benchmarks.
  6. Stripe Engineering — Idempotency keys — a real case of idempotency at scale.
  7. PostgreSQL Wiki — Lock contention diagnosing — debugging when pessimistic causes problems.

Capsule 02 of 08 — Module 6 — SQL Patterns for Production APIs Guide

← PreviousModule 6: Optimistic Locking + Schema VersioningNext →Version columns in SQLAlchemy 2.0: the native implementation