CODEWITHSUNDEEP
sqlalchemy
neurolabs
neurolabs

Reputation: 542

How to do Optimistic Concurrency Control for Entities handed out of the session/process via an API and later returned for update via API

I am providing entities to a SPA frontend via a graphql API. The frontend can fetch entities and later update them.

I want to implement OCC. Can I achieve this using an sqlalchemy version counter?

My naive approach does not work:

class Entity(Base):
    __tablename__ = 'entity'
    id = Column(postgresql.UUID(as_uuid=True), primary_key=True, server_default=text('gen_random_uuid()'))
    version = Column(Integer, nullable=False)
    name = Column(String, nullable=False)

    __mapper_args__ = {
        # enable optimistic locking
        "version_id_col": version
    }

    def mutate(
            root,
            info,
            id,
            version: int,
            name: str,
    ) -> 'UpdateEntity':
        entity = EntityModel.query.get(id)

        entity.version = version
        entity.name = name

        try:
            get_db_session().commit()
            return UpdateEntity(entity)
        except StaleDataError as e:
            raise Exception(
                "Updating the entity was not successful since the entity was changed in the meantime. Please refresh.")

This code leads to a SQL UPDATE statement including the version check, but it does not check for the version passed via the API, but for the one loaded with the entity:

INFO:sqlalchemy.engine.base.Engine:UPDATE entity SET version=%s, name=%s WHERE entity.id = %s AND entity.version = %s
INFO     2021-01-12 16:29:48,150 base.py:1236] (3, 'test', UUID('f9a13067-ffba-46e5-a5de-f395819d9957'), 2)

So basically, this leads to the version field updated, but the version check succeeding when it should not.

How can I achieve a version check against a version passed in via an API?

Upvotes: 2

Views: 1416

Answers (1)

neurolabs
neurolabs

Reputation: 542

I got it working now by updating the version field as a committed value.

Instead of

entity.version = version

if I use

from sqlalchemy.orm.attributes import set_committed_value

set_committed_value(entity, 'version', version)

then the version is checked by sqlalchemy as expected.

Upvotes: 2

Related Questions