CODEWITHSUNDEEP
phpmysqlsqldatabase
I'll-Be-Back
I'll-Be-Back

Reputation: 10828

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

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?

Upvotes: 3

Views: 2447

Answers (4)

heguangyu5
heguangyu5

Reputation: 21

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.

Upvotes: 0

Brent Baisley
Brent Baisley

Reputation: 12721

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.

Upvotes: 3

Sabeen Malik
Sabeen Malik

Reputation: 10880

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.

Upvotes: 1

halfdan
halfdan

Reputation: 34214

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.

Upvotes: 1

Related Questions