3

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?

hakre
  • 193,403
  • 52
  • 435
  • 836
I'll-Be-Back
  • 10,530
  • 37
  • 110
  • 213
  • Try to use mysql transactions. Example: http://stackoverflow.com/questions/2708237/php-mysql-transactions-examples – Vladimir Posvistelik Nov 20 '11 at 01: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 Nov 20 '11 at 02:45

4 Answers4

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.

Brent Baisley
  • 12,641
  • 2
  • 26
  • 39
  • 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? – I'll-Be-Back Nov 20 '11 at 02:49
  • 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). – Brent Baisley Nov 20 '11 at 04:41
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.

halfdan
  • 33,545
  • 8
  • 78
  • 87
  • 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 Nov 20 '11 at 01:33
  • 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? – I'll-Be-Back Nov 20 '11 at 02:01
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.

Sabeen Malik
  • 10,816
  • 4
  • 33
  • 50
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.

heguangyu5
  • 21
  • 4