Read-Modify-Write Patterns in Python

Read-Modify-Write can cause "lost updates". Solutions, in order of preference:

Use calculated updates
Use optimistic row-level locks (version column)
Use pessimistic row-level locks
Use database locks for higher-level issues
Use external locks
Use serialisable transactions

Calculated updates

UPDATE table SET a = a + 1 WHERE id = 1;

These are ideal because no read occurs and they are atomic.

If there are zero updated columns, then you know someone else beat you to it.

It's possible to do more complicated variants that are safe to other kinds of race conditions:

UPDATE balance
SET balance = balance - 100
WHERE id = 1 AND balance >= 100;

In python...

Use optimistic row-level locks (version column)

These are better in read-heavy situations (which is typically the case).

A variant on relative updates with an explicit version column.

UPDATE book
SET
    name = :name,
    version = version + 1
WHERE id = :id AND version = :current_version

Note: only one version of an entity exists at any time, otherwise other kinds of race conditions are possible!

In Python, SQLAlchemy has good built-in support for this https://docs.sqlalchemy.org/en/14/orm/versioning.html and can raise StaleDataError.

Use pessimistic row-level locks

These are better in write-heavy situations. In general, they should be avoided because they create long-running transactions which interfere with database migrations.

In Python, the pattern for these would be:

def create(session: Session: book_new: BookNew) -> BookOut:
    model = convert_book_new_to_model(book_new)
    result = session.add(model)
    return _convert_one(result)

def get_by_id(session: Session, id_: int) -> BookOut:
    result = session.execute(Book.id=id_)
    return _convert_one(result)

def get_locked_by_id(session: Session, id_: int) -> Tuple[BookOut, BookUpdate]:
    result = session.execute(
        select(Book)
        .filter(Book.id=id_)
        .with_for_update()
        .one_or_none()
    )
    return _convert_one(result)

def save(
    session: Session, old_book_out: BookOut, book_update: BookUpdate
) -> BookOut:
    assert book_out.id == book_update.id
    model = _convert_book_update_to_model(book_update)
    new_book_out = session.add(model)
    publish_book_updated(session, old_book_out, new_book_out)
    return _convert_one(new_book_out)


# Usage
book_out, book_update = get_locked_by_id(session, 1)
book_update.name = "Super Book"
save(session, book_out, book_update)

Avoid locks where they are unnecessary
Impossible to update objects without getting a lock
Impossible to update objects without publishing an event (if desired)
Published events have access to old and new state
Transactions are managed outside these methods

Note that where there are child objects (say of a DDD aggregate), then the row-level lock can just be taken on the root entity, trusting your database-access layer to consistently take locks.