Concurrent atomic select-update

Question

How can I, using sqlalchemy, do something like this?

user = session.query("select * from user")
if user.state == "active"
    session.query("update user set state = 'inactive' where id = %d" % user.id)

I need the select and update to be one atomic operation. Another program should not be able to select/update the user while the operation is going.

How can I do it concurrently?

Note: I need to know if we succeeded in change the state or not.

How can I achieve that?

Am I doing it wrong?

Should it be a stored procedure?

Should I use database "lock"?

Answer 1

You can do this by setting an isolation level of Serializable, which can be done on a specific session with SQLAlchemy using session.connection(execution_options={'isolation_level': 'SERIALIZABLE'}). If two connections conflict (they both read before the other wrote), committing the transaction will fail, and you can just loop until it goes through.

from sqlalchemy import Column, create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from sqlalchemy.types import Boolean, Integer, String


Base = declarative_base()

class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False)
    active = Column(Boolean, nullable=False)


url = 'postgresql://postgres@localhost/test'
engine = create_engine(url, echo=True)
if not engine.dialect.has_table(engine.connect(), 'users'):
    Base.metadata.create_all(bind=engine)
    Session = sessionmaker(bind=engine)
    session = Session()
    session.add(User(name="remram", active=True))
    session.commit()

Session = sessionmaker(bind=engine)
session = Session()
session.connection(execution_options={'isolation_level': 'SERIALIZABLE'})  # Commenting this line will make this unsafe in a concurrent environment

user = session.query(User).filter(User.name == "remram").one()
if user.active:
    user.active = False
raw_input()  # So you can run this twice
session.commit()

Answer 2

If your database supports SELECT FOR UPDATE (ie: PostgreSQL) you could use:

user = session.query("select * from user where id = %d for update" % theId)
if user.state == "active"
    session.query("update user set state = 'inactive' where id = %d" % user.id)