Handle an optimistic version conflict with SQLAlchemy

• Map a non-null integer version_id as SQLAlchemy's version column.
• Carry the expected version from the API contract.
• Flush inside the owned transaction so the conflict is observed there.
• Roll back before translating the exception.
• Return the new version after success.

1. Configure version_id_col on the mapped order.
2. Load the row and compare the client's expected version before mutation for a clear early failure.
3. Mutate and flush; SQLAlchemy includes the current version in the update predicate.
4. Catch StaleDataError outside the failed transaction and translate it.
5. Test two independent sessions committing in the dangerous order.

from sqlalchemy.orm import Mapped, mapped_column
from sqlalchemy.orm.exc import StaleDataError

class Order(Base):
    __tablename__ = "orders"
    id: Mapped[int] = mapped_column(primary_key=True)
    status: Mapped[str]
    version_id: Mapped[int] = mapped_column(nullable=False, default=1)
    __mapper_args__ = {"version_id_col": version_id}

async def change_status(session_factory, order_id: int, expected: int, status: str):
    try:
        async with session_factory() as session:
            async with session.begin():
                order = await session.get(Order, order_id)
                if order is None:
                    raise OrderNotFound(order_id)
                if order.version_id != expected:
                    raise StaleOrder(expected)
                order.status = status
                await session.flush()
                new_version = order.version_id
        return new_version
    except StaleDataError as exc:
        # The transaction context has rolled back before translation.
        raise StaleOrder(expected) from exc

The indexed lookup and conditional update are O(log n). A conflict costs a rollback and client-visible resolution path.

• Bulk updates bypass normal ORM version checking unless designed explicitly.
• A missing row and a stale row can both surface as zero matched rows in manual updates.
• One row version cannot protect an invariant spanning several rows.
• The exception must not be translated while the session remains in a failed state.

• Load the same row in two sessions and commit both updates in order.
• Assert the loser changes no state.
• Verify the winner increments the version.
• Verify a stale request is not automatically retried.

• Checking a version followed by an unconditional bulk update leaves a race.
• Catching the exception without rollback leaves the session unusable.
• Retrying automatically can overwrite the user's newer intent.
• Forgetting to return the new version guarantees another stale request.