CODEWITHSUNDEEP
pythonmysqlflasklockingflask-sqlalchemy
Daocheng
Daocheng

Reputation: 451

Flask-SQLAlchemy with_for_update() row lock

I have a model called 'User', and 'User' has 'Money'.
There a scenario that multiple session can read the model 'User' and update 'money' at the same time.

Session 2 should read the 'money' value after session 1 updated successfully.
I tried to lock the 'User' row when updating.
Here's my code.

user = User.query.with_for_update().filter_by(id=userid).first()
print('000000')
before_money = user.money
print('111111')
time.sleep(1)
user.money -= 0.1
print('User:' + str(user.id) + '***' + str(before_money) + '-' + str(0.1) + ' = ' + str(user.money))
time.sleep(1)
db.session.commit()
print('22222')

I opened two session to run this code at the same time, the output

000000
111111
User:1***125.3-0.1 = 125.2
000000
111111
22222
User:1***125.3-0.1 = 125.2
22222

Session 2 didn't read the updated value.

I would really like to know where the problem is.

Upvotes: 17

Views: 24616

Answers (2)

Vuk
Vuk

Reputation: 81

You just need to state what you want to lock:

user = User.query.with_for_update(of=User).filter_by(id=userid).first()
user.money -= 0.1

Upvotes: 8

Daocheng
Daocheng

Reputation: 451

After struggling for one whole day, i found the problem.

user = User.query.with_for_update().filter_by(id=userid).first()

should be

result = db.session.query(User.money).with_for_update().filter_by(id=userid).first()
money = result[0]
user.money = money - 0.1

Yes, so simple but annoying

Upvotes: 28

Related Questions