Running multiple PHP scripts at the same time (database loop issue)

Question

I am running 10 PHP scripts at the same time and it processing at the background on Linux.

For Example:

while ($i <=10) {
 exec("/usr/bin/php-cli run-process.php > /dev/null 2>&1 & echo $!");
 sleep(10);
 $i++;
}

In the run-process.php, I am having problem with database loop. One of the process might already updated the status field to 1, it seem other php script processes is not seeing it. For Example:

$SQL = "SELECT * FROM data WHERE status = 0";
$query = $db->prepare($SQL);
$query->execute();

while ($row = $query->fetch(PDO::FETCH_ASSOC)) {
    $SQL2 = "SELECT status from data WHERE number = " . $row['number'];
    $qCheckAgain = $db->prepare($SQL2);
    $qCheckAgain->execute();
    $tempRow = $qCheckAgain->fetch(PDO::FETCH_ASSOC);

    //already updated from other processs?
    if ($tempRow['status'] == 1) {
        continue;
    }

    doCheck($row)
    sleep(2)
}

How do I ensure processes is not re-doing same data again?

Try to use mysql transactions. Example: stackoverflow.com/questions/2708237/… — Uladzimir Pasvistselik
– Uladzimir Pasvistselik, Commented Nov 20, 2011 at 1:23
Just an aside that if your query doesn't have any parameters, calling prepare() just adds unnecessary overhead. Instead, you can use $db->exec($SQL); — Michael Mior
– Michael Mior, Commented Nov 20, 2011 at 2:45

Brent Baisley · Accepted Answer · 2011-11-20 02:31:45Z

3

When you have multiple processes, you need to have each process take "ownership" of a certain set of records. Usually you do this by doing an update with a limit clause, then selecting the records that were just "owned" by the script.

For example, have a field that specifies if the record is available for processing (i.e. a value of 0 means it is available). Then your update would set the value of the field to the scripts process ID, or some other unique number to the process. Then you select on the process ID. When your done processing, you can set it to a "finished" number, like 1. Update, Select, Update, repeat.

answered Nov 20, 2011 at 2:31

Brent Baisley

12.7k2 gold badges28 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

I'll-Be-Back Over a year ago

I am not sure what you mean by "update with a limit clause"? So basically you mean like I can add a field called like run_by and the value will be Robot-A, Robot-B, Robot-C - first 10,000 rows will be for Robot-A to process?

Brent Baisley Over a year ago

Yes. So your query would be UPDATE table SET run_by="ROBOT-A" WHERE run_by="UNPROCESSED" LIMIT 10,000. The SELECT * FROM table WHERE run_by="ROBOT-A". Then when processing is done UPDATE table SET run_by="PROCESSED" WHERE run_by="ROBOT-A". MySQL handles contention issues with records. If using InnoDB you'll need to issue a LOCK TABLE before the UPDATE (then unlock).

halfdan · Accepted Answer · 2011-11-20 01:21:49Z

1

The reason why your script executeds the same query multiple times is because of the parallelisation you are creating. Process 1 reads from the database, Process 2 reads from the database and both start to process their data.

Databases provide transactions in order to get rid of such race conditions. Have a look at what PDO provides for handling database transactions.

answered Nov 20, 2011 at 1:21

halfdan

34.3k8 gold badges82 silver badges88 bronze badges

2 Comments

I'll-Be-Back Over a year ago

I have used second sql query for like a race conditions - it check if status = 1 then skip it. I will look into database transactions, thanks. Do I need to add database transactions when updating status to 1? That happen in doCheck($row) function (see my question)

I'll-Be-Back Over a year ago

I have just read database transactions, if I understand correctly, do I need to add beginTransaction() before the while loop and commit() in while loop?

Sabeen Malik · Accepted Answer · 2011-11-20 01:24:52Z

1

i am not entirely sure of how/what you are processing.

You can introduce limit clause and pass that as a parameter. So first process does first 10, the second does the next 10 and so on.

answered Nov 20, 2011 at 1:24

Sabeen Malik

10.9k4 gold badges37 silver badges51 bronze badges

1 Comment

I'll-Be-Back Over a year ago

I have over 100,000 rows in the data table - how to calculate the limit for each process? let say 10 processes.

heguangyu5 · Accepted Answer · 2011-11-20 03:05:38Z

0

you need lock such as "SELECT ... FOR UPDATE".

innodb support row level lock.

see http://dev.mysql.com/doc/refman/5.0/en/innodb-locking-reads.html for details.

answered Nov 20, 2011 at 3:05

heguangyu5

214 bronze badges

Collectives™ on Stack Overflow

Running multiple PHP scripts at the same time (database loop issue)

4 Answers 4

2 Comments

2 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related