Clone wiki

sqlalchemy / UsageRecipes / SafeCounterColumns

Sometimes you need to atomically get and increment a field per row. For example, you may need an order number that increments monotonically per customer: A customer's first order is always "1", the second is "2", etc. You can implement this by storing the "next id" as a column of a customer table.

customers = Table('customers', metadata,
    Column('cust_id', Integer, primary_key=True),
    Column('name', String(40)),
    Column('next_order_id', Integer, default=1))

CREATE TABLE customers (
  cust_name VARCHAR(40),
  next_order_id INTEGER,
  PRIMARY KEY (cust_id))

You could just select the current value, then update:

c = session.query(Customer).filter_by(...).one()
order_id = c.next_order_id
c.next_order_id += 1

But this isn't safe--if two connections both try to do this, the second one can SELECT the same "next id" before the first one gets to update it. In SQL, you can prevent this by selecting the row "for UPDATE". This locks just that row until the row is next updated:

SELECT next_order_id FROM customers WHERE [...] FOR UPDATE
-- Now the row is locked
UPDATE customers SET next_order_id = next_order_id + 1 WHERE cust_id = xx
-- Now the row is unlocked

In SQLAlchemy, you can do this by adding a 'lockmode' to the query:

c = session.query(Customer).with_lockmode('update').filter_by(...).one()
order_id = c.next_order_id
c.next_order_id += 1