CODEWITHSUNDEEP
pythonmysqlflasksqlalchemyflask-sqlalchemy
coderadi
coderadi

Reputation: 35

Flask-SQAlchemy with_for_update() returning old data

I have a situation in my Flask application where I need to generate increasing numbers against a consumer. For this, every consumer has a row in a separate table which holds the number generator up till now.

Since multiple requests may concurrently land on the server, I am locking the row for the update and then updating the data and committing. However, after commit, the other requests are reading the old data for the same row.

I am doing a with_for_update on the Model's query. The data is read, then updated, however, the second requests reads the old data. Tried by setting the DB isolation level to higher ones (SERIALIZABLE) but didn't work.

Sample code:

obj = NumberGenerator.query.filter(
    NumberGenerator.consumer_id == consumer_id
).with_for_update().first()

obj.number = obj.number + 1

db.session.add(obj)
db.session.commit()

The second request reads the old data. I am not able to understand why this is happening. I tried the same approach via iPython and it worked flawlessly. Tried writing raw MySQL queries using MySQLdb, that too worked. Searched a lot on various forums (stack overflow included), package documentation and couldn't find anything.

Flask-SQLAlchemy version: 2.3.2

MySQL version: 5.6

Upvotes: 1

Views: 1503

Answers (1)

reclosedev
reclosedev

Reputation: 9522

I've encountered the same problem. See following issue for solution https://github.com/sqlalchemy/sqlalchemy/issues/4774

In short, the simplest way is to add populate_existing() to query

obj = NumberGenerator.query.filter(
    NumberGenerator.consumer_id == consumer_id
).with_for_update().populate_existing()

Upvotes: 2

Related Questions