Concurrent access problem in mysql database

Question

Hi i'm coding a python script that will create a number child processes that fetch and execute tasks from the database.

The tasks are inserted on the database by a php website running on the same machine.

What's the good (need this to be fast) way to select and update those tasks as "in progress" to avoid to be selected by multiple times by the python scripts

edit: database is mysql

Thanks in advance

Answer 1

Without knowing more about your architecture, I suggest the following method.

1) Lock Process table
2) Select ... from Process table where State="New"
3) processlist = [list of process id''s from step 2]
4) Update Process table set State="In progress" where ProcessId in [processlist]
5) Unlock Process table.

Answer 2

A way to speed things up is to put the process into a stored procedure, and return the selected row from that procedure. That way, only one trip to the db server.

Answer 3

Use an InnoDB table Tasks, then:

select TaskId, ... from Tasks where State="New" limit 1;
update Tasks set State="In Progress" where TaskId=<from above> and State="New";

if the update succeeds, you can work on the task. Otherwise, try again.

You'll want an index on TaskId and State.