SQLAlchemy(Postgres) and transaction

Question

I want a record from a table(queue) to be selected, locked(no other process can edit this record) and updated at a later point in time.
I assumed if I put the whole querying and updating in a transaction, no other process can edit/query the same record. But I am not quite able to achieve this.

def move(one, two):
  from settings import DATABASE_USER, DATABASE_PASSWORD, DATABASE_HOST, DATABASE_PORT, DATABASE_NAME
  from sqlalchemy.orm import sessionmaker, scoped_session
  from sqlalchemy import create_engine
  engine = create_engine('postgres://%s:%s@%s:%s/%s' % (DATABASE_USER, DATABASE_PASSWORD, DATABASE_HOST, DATABASE_PORT, DATABASE_NAME), echo = False)
  conn = engine.connect()
  tran = conn.begin()
  Session = scoped_session(sessionmaker())
  session = Session(bind=conn)
  url = session.query(URLQueue).filter(URLQueue.status == one).first()
  print "Got record: " + str(url.urlqueue_id)
  time.sleep(5)
  url.status = two
  session.merge(url)
  session.close()
  tran.commit()

move('START', 'WIP')

If I start 2 process, they both update the same record. I am not sure if I created the connections/sessions/transactions properly. Any pointers?

Answer 1

Either make your transaction isolation level serializable, or fetch the record for updating via query.with_lockmode('update').