How can conflicts be prevented when writing data to a database simultaneously?

To prevent conflicts during simultaneous data writes to a database, several approaches are used:

• Transactions and locks: Transactions can ensure the atomicity of operations. Locks are distinguished at the row, table, or database level so that other operations wait for the current one to finish.
• Optimistic locking: When reading data, a version or timestamp is saved. When writing, it checks that the data has not changed since it was read. If it has changed, the operation is rolled back or retried.
• Access order: Designing the application so that write operations occur sequentially or through a queue.

An example of optimistic locking in Python using SQLAlchemy:

from sqlalchemy import Column, Integer, String, Version
from sqlalchemy.orm import sessionmaker

# model with a version field
class Item(Base):
    __tablename__ = 'items'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    version = Column(Integer, nullable=False, default=1)

Session = sessionmaker(bind=engine)
session = Session()

item = session.query(Item).filter_by(id=1).one()
item.name = 'new name'
item.version += 1  # update version

try:
    session.commit()
except:
    session.rollback()
    print("Conflict during write, please try again")

Thus, data loss during concurrent modifications is prevented.