How do you handle race conditions when writing to a database without using transactions?

When working with race conditions without transactions, several approaches can be used:

• Optimistic locking: store a record version (e.g., a version or timestamp field). When updating, check that the version has not changed since reading. If it has changed, repeat the operation.

• Using unique constraints and checks at the database level: for example, attempt to insert a record with a unique key; if a conflict occurs, handle the error and retry.

• Explicit locking at the application level: use mutex or lock in code so that only one thread/process can write to the database at a time.

• Using atomic database operations: if supported by the DBMS, use commands like INSERT ... ON CONFLICT or UPDATE ... WHERE version = ?.

Example of optimistic locking in Python with SQLAlchemy:

from sqlalchemy import update

# Read the record with version
record = session.query(MyModel).filter_by(id=record_id).one()
current_version = record.version

# Attempt to update with version check
stmt = update(MyModel).where(
    MyModel.id == record_id,
    MyModel.version == current_version
).values(data=new_data, version=current_version + 1)

result = session.execute(stmt)
if result.rowcount == 0:
    # Conflict, version changed — retry or handle error
    pass
else:
    session.commit()

This approach helps prevent data loss during concurrent writes without using transactions.