I have a table, jobs
, with fields id
, rank
, and datetime started
in a MySQL InnoDB database.
Each time a process gets a job, it "checks out" that job be marking it started, so that no other process will work on it.
I want a single process with a session to be able to:
- Find the job with the highest ranking
- Update this job's started field to the current timestamp
without risking that any other session might also choose and start on the job with the highest ranking. Other sessions are also changing the rankings at any given time.
This is my attempt:
session.execute("LOCK TABLES jobs READ")
next_job = session.query(Jobs).\filter(Jobs.started == None).\order_by(Jobs.rank.desc()).first()# mark as started
smt = update(Jobs).where(Jobs.id == next_job.id).\values(started=datetime.now())
session.execute(smt)
session.execute("UNLOCK TABLES")
but this fails with a:
OperationalError: (OperationalError) (1099, "Table 'jobs' was locked with a READ lock and can't be updated")
I'd prefer to do it in a more pythonic way that SQLAlchemy offers anyway. How can I do this?
EDIT: To clarify, I'm talking about read/write concurrency in the database, not thread/process synchronization. My workers will be spread across a network.