SQLAlchemy (Postgres) and transaction
I want a record from a table (queue) to be selected, locked (no other process can edit this record) and updated later. I assumed that if I put the whole request and update in a transaction, no other process can edit / request the same record. But I am not 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 run 2 processes, they both update the same entry. I'm not sure if I created connections / sessions / transactions correctly. Any pointers?
+2
source to share