Implementing optimistic locking with version counters

Add __mapper_args__ = {"version_id_col": version} and SQLAlchemy appends WHERE version = :old to every UPDATE, raising StaleDataError when the row has moved on — which converts a silent lost update into an error you can retry. This is the read-modify-write case that no upsert can express, under upserts and conflict resolution strategies.

Quick Answer

The lost update, and what the version check does The same interleaving, twice. Without a version column, transaction A reads a balance of 100 and computes 90, transaction B reads the same 100 and computes 80, and both UPDATE statements succeed — the final balance is 80, and A's deduction has vanished with no error anywhere. With a version column, B's UPDATE carries WHERE version = 1, which no longer matches because A's commit advanced it to 2. The statement affects zero rows, SQLAlchemy detects the mismatch and raises StaleDataError, and B can re-read and retry against the value that actually exists. without a version column A reads 100, computes 90 B reads 100, computes 80 both UPDATEs succeed final balance 80 — A is silently lost with version_id_col A commits, version 1 → 2 B UPDATEs WHERE version = 1 0 rows affected → StaleDataError B re-reads and retries against 90 Nothing about the first column is an error condition to the database: both statements are valid and both succeed. That is what makes a lost update so hard to notice.

Before — a read-modify-write with no protection:

account = await session.get(Account, account_id)
account.balance -= amount            # computed in Python from a value read earlier
await session.commit()               # UPDATE accounts SET balance = 90 WHERE id = 1

After — a version counter that detects the conflict:

from __future__ import annotations

import decimal

from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column


class Account(Base):
    __tablename__ = "accounts"

    id: Mapped[int] = mapped_column(primary_key=True)
    balance: Mapped[decimal.Decimal]
    version: Mapped[int] = mapped_column(nullable=False, default=1)

    # Every UPDATE now carries WHERE version = :current, and increments it.
    __mapper_args__ = {"version_id_col": version}

# The UPDATE SQLAlchemy now emits:
#   UPDATE accounts SET balance = 90, version = 2
#   WHERE accounts.id = 1 AND accounts.version = 1
#
# Zero rows affected → sqlalchemy.orm.exc.StaleDataError

Better, where SQL can express it — no version, no retry:

from sqlalchemy import update

stmt = (
    update(Account)
    .where(Account.id == account_id, Account.balance >= amount)
    .values(balance=Account.balance - amount)      # computed by the database
)
if (await session.execute(stmt)).rowcount == 0:
    raise InsufficientFunds(account_id)

The third form is atomic by construction and needs no locking of any kind. Reach for a version counter when the new value genuinely cannot be derived in SQL — a state-machine transition, a recomputed aggregate, a value validated against external data.

Execution Context & Async Workflow Integration

version_id_col is enforced by the ORM's unit of work, so it applies to flushes of loaded instances and not to Core update() statements — a bulk update bypasses it entirely and will happily overwrite a row whose version has moved on. That is a correct division of responsibility, and a surprise the first time a bulk job silently defeats the mechanism.

# Async — the retry loop, with the re-read that makes it correct
from sqlalchemy.orm.exc import StaleDataError


async def withdraw(session_factory, account_id: int,
                   amount: decimal.Decimal, attempts: int = 3) -> None:
    for attempt in range(attempts):
        async with session_factory() as session:
            try:
                async with session.begin():
                    # Re-read inside the loop: each attempt must see current state.
                    account = await session.get(Account, account_id)
                    if account is None:
                        raise NoSuchAccount(account_id)
                    if account.balance < amount:
                        raise InsufficientFunds(account_id)
                    account.balance -= amount
                return
            except StaleDataError:
                if attempt == attempts - 1:
                    raise
                await asyncio.sleep(0.02 * (2 ** attempt))     # brief backoff

Three details make this loop correct rather than merely present. A fresh session per attempt avoids inheriting the identity map from the failed one, which would return the stale instance from get() and produce an infinite loop of identical failures. The read happens inside the loop, so each attempt computes from what is actually stored. And the attempts are bounded, so a permanently contended row surfaces as an error rather than as a coroutine that never returns.

The interaction with a rollback-isolated test suite is worth knowing: StaleDataError is raised at flush, so a test that provokes it must ensure both writers are genuinely separate transactions. Two coroutines sharing one session serialise on the session and never conflict, which is the same requirement described for upsert tests in the parent guide.

Three controls, chosen by contention Three approaches. Optimistic versioning assumes conflicts are rare: it takes no locks, costs nothing when there is no conflict, and raises when there is — which is right for user-edited records where two people rarely touch the same row at once. A pessimistic SELECT FOR UPDATE takes the row lock up front and makes the second writer wait rather than fail, which is right for a hot row where retries would thrash. And when the new value can be expressed in SQL from the old one — a balance decremented by an amount, a counter incremented — neither is needed: a single UPDATE with an expression is atomic by construction. optimistic versioning assumes conflicts are rare no locks, raises on conflict user-edited records SELECT ... FOR UPDATE takes the lock up front the second writer waits a hot, contended row an atomic SQL expression balance = balance - :amount no read, no lock, no retry when SQL can express it Reach for the third column first: most read-modify-write cycles are an expression someone wrote as a read and a write because the ORM made that shape convenient.

Resolving Warnings, Errors & Common Mistakes

Error / WarningRoot CauseProduction Fix
StaleDataError: UPDATE statement on table 'accounts' expected to update 1 row(s); 0 were matchedAnother transaction committed a change to this row first.Roll back, re-read, recompute, retry — bounded.
The same StaleDataError every retryThe retry reuses the session, so get() returns the stale instance from the identity map.A fresh session per attempt, or session.expire(obj) before re-reading.
A bulk update() overwrites regardless of versionversion_id_col is enforced by the ORM unit of work, not by Core statements.Add the version predicate explicitly to the bulk statement, or route those writes through the ORM.
StaleDataError on a row nobody else touchedAn after_update listener or a database trigger modified the row, advancing the version.Exclude the version column from trigger-driven updates, or use a server-side version scheme.
Version conflicts under a single writerTwo sessions in the same process both loaded the row — a background task and a request handler.The mechanism is working; the design has two owners for one row.
Retries thrash on a hot rowContention is high, which optimistic locking assumes it is not.Switch to SELECT ... FOR UPDATE, or make the update an atomic SQL expression.

Advanced: Timestamp Versions, Server-Side Counters, and FOR UPDATE

An integer counter is the default, and two variants are worth knowing.

A server-generated version avoids a race that exists in theory when two application processes compute the next version simultaneously — though the WHERE clause already makes that safe. It is more useful for interoperability, where another system writes the same table:

from sqlalchemy import func

class Document(Base):
    __tablename__ = "documents"

    id: Mapped[int] = mapped_column(primary_key=True)
    body: Mapped[str]
    updated_at: Mapped[datetime.datetime] = mapped_column(server_default=func.now())

    __mapper_args__ = {
        "version_id_col": updated_at,
        # Let the database supply the new value rather than the ORM.
        "version_id_generator": False,
    }

With version_id_generator=False, SQLAlchemy expects the column's new value to come from the server — a trigger, or a server_onupdate — and still uses the old value in the WHERE clause. The advantage is that any writer, including one outside your application, advances the version correctly.

A pessimistic lock is the right answer when contention is high enough that retries dominate:

from sqlalchemy import select

async def withdraw_pessimistic(session, account_id: int, amount) -> None:
    async with session.begin():
        account = await session.scalar(
            select(Account).where(Account.id == account_id).with_for_update()
        )
        if account.balance < amount:
            raise InsufficientFunds(account_id)
        account.balance -= amount

with_for_update() takes the row lock at read time, so a concurrent writer waits rather than failing. The cost is that waiting is serialisation: throughput on that row becomes one transaction at a time, and a long transaction holding the lock blocks everyone. Use with_for_update(nowait=True) to fail fast instead of waiting, or skip_locked=True for the queue-consumer pattern where each worker should take a different row.

The rule of thumb is contention. Optimistic versioning is cheaper when conflicts are rare, because the no-conflict path costs nothing. Pessimistic locking is cheaper when conflicts are common, because waiting once beats retrying five times. If you cannot tell which you have, instrument it: count StaleDataError occurrences with the query-counting approach in instrumenting and observing async queries, and let the number decide.

Retrying a version conflict correctly Five steps, and the order is what makes it correct. On StaleDataError, roll back — the transaction is aborted and nothing else will run. Re-read the row, which is the step that matters: the retry must see the value another transaction committed, not the stale one it started from. Recompute the new value from what was just read, because a retry that replays the original computation writes the same lost update the version check just prevented. Write again, and expect this attempt to succeed since conflicts are assumed rare. Bound the attempts so a permanently contended row fails visibly rather than spinning. StaleDataError raised the UPDATE affected 0 rows the row moved on roll back the transaction is aborted nothing else runs until this re-read the row this is the step that matters a fresh version and a fresh value recompute from what was read not from the original computation or you rewrite the lost update write again, bounded attempts three is usually plenty Replaying the original computed value on retry defeats the whole mechanism: the check stopped the lost update, and the retry then performs it anyway.

Adding a Version Column to an Existing Table

Introducing optimistic locking on a table already in production is an expand-and-contract change, because the column must exist and be populated before any code depends on it.

The first release adds the column as nullable with a server default of 1, which on PostgreSQL 11 and later is a catalogue-only change and takes no meaningful lock — the reasoning is in adding a NOT NULL column without locking in PostgreSQL. No application code references it yet.

The second release backfills any NULL values in bounded batches and then adds the NOT NULL constraint as NOT VALID, validating it separately. Still no application code references it.

The third release adds version_id_col to the mapper. This is the moment behaviour changes, and it is worth deploying alone so that any rise in StaleDataError is unambiguously attributable.

That last point deserves emphasis. Enabling versioning will surface conflicts that were previously silent lost updates, and the rate is genuinely unknown until you turn it on. Expect an increase in errors, treat it as the mechanism working rather than as a regression, and have the retry loop deployed before the mapper change rather than in the same release — otherwise the first thing users see is a wave of 500s from a code path that has no retry yet.

It is also worth adding a metric before the mapper change, counting how often a row's version differs from the one that was read, computed by a temporary read-only check. That gives you the conflict rate in advance and turns the decision between optimistic and pessimistic locking into a measurement rather than a guess.

Frequently Asked Questions

Does version_id_col add a column to my table?

It maps an existing column — you have to create it in a migration. SQLAlchemy then includes it in the WHERE clause of every UPDATE for that mapper and increments it on each flush. The column is ordinary in every other respect.

Does it protect against concurrent deletes?

Yes. A DELETE for a versioned mapper also carries the version predicate, so deleting a row someone else has modified raises StaleDataError rather than removing a row you have not seen. That is usually what you want, and occasionally surprising.

Why does a bulk update() bypass the version check?

Because the check is implemented in the ORM unit of work, which only participates when flushing loaded instances. A Core update() is a statement the ORM does not mediate. Either add the version predicate explicitly to the bulk statement, or accept that bulk paths are exempt and document it.

Should I use an integer or a timestamp as the version?

An integer, unless another system also writes the table. Integers increment unambiguously and cannot collide; timestamps can, at the resolution the column stores, and comparing them across clocks is a distraction. Use a server-generated timestamp only when writers outside your application must advance the version too.