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?

Answer 1

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.

Answer 2

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.