Locking mechanisms for preventing data integrity issues in concurrent data access scenarios

[fkromer]

First Check

• I added a very descriptive title to this issue.
• I used the GitHub search to find a similar issue and didn't find it.
• I searched the SQLModel documentation, with the integrated search.
• I already searched in Google "How to X in SQLModel" and didn't find any information.
• I already read and followed all the tutorial in the docs and didn't find an answer.
• I already checked if it is not related to SQLModel but to Pydantic.
• I already checked if it is not related to SQLModel but to SQLAlchemy.

Commit to Help

• I commit to help with one of those options 👆

Example Code

n.a.

Description

So far SQLModel does not provide mechanisms for preventing data integrity issues in concurrent data access scenarios out of the box.

Wanted Solution

It would be great to have something similar like LockModeTypes provided by the Java Persistence API (API).

Optimistic locking (@Lock(LockModeType.OPTIMISTIC_FORCE_INCREMENT)):

• OPTIMISTIC_FORCE_INCREMENT - Optimistic lock, with version update on the database level.

Pessimistic locking (@Lock(LockModeType.<...>)):

• PESSIMISTIC_READ - acquire a shared lock, and the locked entity cannot be changed before a transaction commit.
• PESSIMISTIC_WRITE - acquire an exclusive lock, and the locked entity can be changed.
• PESSIMISTIC_FORCE_INCREMENT - acquire an exclusive lock and update the version column, the locked entity can be changed

When using OPTIMISTIC_FORCE_INCREMENT based optimistic locking and pessimistic locking table rows are locked at the database level. So this should be in the scope of SQLModel.

Another option would be to provide optimistic locking on the class instance level instead of the database level similar to the options available in Java. Hibernate provides e.g. optimistic locking via @OptimisticLocking(type = OptimisticLockType.<...>:

• ALL - perform locking based on all fields
• DIRTY - perform locking based on only changed fields
• VERSION - perform locking using a dedicated version column

In SQLModel one could implement this functionality using Pydantic classes instead of the SQLAlchemy classes.

Wanted Code

n.a.

Alternatives

Reinvent the wheel over and over again :smil

Operating System

Other

Operating System Details

n.a.

SQLModel Version

n.a.

Python Version

n.a.

Additional Context

The article Optimistic and Pessimistic Locking in JPA is a nice resource about what types of locking is provided by and used in JPA.

[pitwegner]

I second that. Writing to the same object in concurrent threads currently leads to a ROLLBACK. I have no way to catch a concurrent write and wait for the operation to finish before starting the other.

[ahmedazizkhelifi]

Any update ?

[hamiltont]

This worked for me to implement version-column based optimistic locking

class Proposal(SQLModel, table=True):
    ... prior code ...
    length: Optional[float] = None
    version: int = Field(sa_column=Column(Integer), default=1)

    @classmethod
    def __declare_last__(cls):
        cls.__mapper__.version_id_col = cls.__table__.c.version

Initially tried using mapper_args but for the life of me I could not figure out how to get the SqlAlchemy Column from the SQLModel Field

Disclaimer: I'm no expert on these libraries, used chatbots to figure this out

[F1yingWhite]

This worked for me to implement version-column based optimistic locking

class Proposal(SQLModel, table=True):
    ... prior code ...
    length: Optional[float] = None
    version: int = Field(sa_column=Column(Integer), default=1)

    @classmethod
    def __declare_last__(cls):
        cls.__mapper__.version_id_col = cls.__table__.c.version

Initially tried using mapper_args but for the life of me I could not figure out how to get the SqlAlchemy Column from the SQLModel Field

Disclaimer: I'm no expert on these libraries, used chatbots to figure this out

It works!