how to ensure consistent database update under race condition using flask-sqlalchemy

Question

This might be an old question. But I am new to database and flask-sqlalchemy and could not find any answers from previous posts. Maybe the answer is quite simple.

I am trying to deploy a Flask app to Heroku using postgresql. It has the following model, which is an inventory tracking how many items left in stock.

class Inventory(db.Model):
    __tablename__ = 'inventories'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(64), unique=True)
    count = db.Column(db.Integer)

There is a web form where users use to place an order which will update the number of items left in the database tracked by field count. The code below handles the order and sets different status depending if there are enough items left.

item = Inventory.query.filter_by(name=form.name.data).first()
order = int(form.count.data)
if item.count > order:
    item.count -= order
    db.session.add(item)
    db.session.commit()
    status = ('%r: %d left' % (item.name, item.count))
else:
    status = '%r not enough left' % item.name

If user1 and user2 place orders on the same item simultaneously and there are 5 items left. Maybe user1's request arrives first with 3 orders. Then when user2's request arrives, user1's order may not have been committed yet. So user2 also sees 5 items left. So in the end, items left in the database is 2; both user1 and user2 successfully place their orders of 3 items for each user, though there are only 5 items left at the beginning, which is eough. How can this issue be avoided?

This image explains the above situation.

Answer 1

To prevent stale data from leaking into the database, you need to perform an atomic update.

item.count = Item.count - order

You'll still have to check for and handle negative quantities after you commit the session.

This can be as simple as

db.session.commit()

if item.count < 0:
  # roll back the order 

When you commit a session, SQLAlchemy clears its internal cache and will reload records from the database.