Repository navigation
Locking mechanisms for preventing data integrity issues in concurrent data access scenarios #307
Description
Activity
- changed the title
[-]Locking mechanisms for preventing data integrity issues during concurrent [/-][+]Locking mechanisms for preventing data integrity issues in concurrent data access scenarios[/+]on Apr 19, 2022 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.
Reacted by Florian Kromer and Mikhail KondratyevAny update ?
Reacted by Mikhail Kondratyev and Hleb KrakhotkinThis 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.versionInitially tried using mapper_args but for the life of me I could not figure out how to get the SqlAlchemy
Columnfrom the SQLModelFieldDisclaimer: I'm no expert on these libraries, used chatbots to figure this out
Reacted by F1yingWhite and Muhammad Mughees RazaThis 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.versionInitially tried using mapper_args but for the life of me I could not figure out how to get the SqlAlchemy
Columnfrom the SQLModelFieldDisclaimer: I'm no expert on these libraries, used chatbots to figure this out
It works!
- locked and limited conversation to collaborators
on May 18, 2026
First Check
Commit to Help
Example Code
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
LockModeTypesprovided by the Java Persistence API (API).Optimistic locking (
@Lock(LockModeType.OPTIMISTIC_FORCE_INCREMENT)):Pessimistic locking (
@Lock(LockModeType.<...>)):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.<...>:In SQLModel one could implement this functionality using Pydantic classes instead of the SQLAlchemy classes.
Wanted Code
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.