CODEWITHSUNDEEP
sqlmysqldatabaseconcurrency
arthurprs
arthurprs

Reputation: 4627

Concurrent access problem in mysql database

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

Upvotes: 1

Views: 1801

Answers (3)

Keith Randall
Keith Randall

Reputation: 23265

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.

Upvotes: 1

dar7yl
dar7yl

Reputation: 3757

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.

Upvotes: 1

dar7yl
dar7yl

Reputation: 3757

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.

Upvotes: 1

Related Questions