title: how to ensure consistent database update under race condition using flask-sqlalchemy
body: <p>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.</p>
  
  <p>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.</p>
  
  <pre><code>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)
  </code></pre>
  
  <p>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 <strong>count</strong>. The code below handles the order and sets different status depending if there are enough items left.</p>
  
  <pre><code>item = Inventory.query.filter_by(name=form.name.data).first()
  order = int(form.count.data)
  if item.count &gt; 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
  </code></pre>
  
  <p>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?</p>
  
  <p><a href="http://i.stack.imgur.com/v3xHN.png" rel="noreferrer">This image explains the above situation.</a></p>