Parallel processing with SELECT

Oni

I'm forking a php script which does a select from a mysql table. Computation times vary. My problem is as follows: each child gets the same data from the query, and each child enters duplicates.

A lot of ugly fixes come to mind-- one is putting a random usleep at the top. Could someone suggest a better fix? (hopefully a much better one 🙂)

Bjom

Use transactions . Also have a look at the php manual on how to implement those.

Oni

Sorry, I think I've been misunderstood.

Duplicates are caused by the real problem... The inability to select different values that are not already being worked on (by the other children).

For example, here's another solution:

Create only 1 db connection from the parent, also set a variable to 0, implying that the connection is idle and can be used by a child. When a child uses the connection, it sets the variable to 1. A rand usleep is again needed to avoid having children checking the variable at the same time.

Oni

Although I haven't tested it completely, I believe I've found a solution to this problem at http://orensol.com/2009/04/20/connecting-several-php-processes-to-the-same-mysql-transaction/

The DBHandler Class allows you to use one db connection for all the children, one initiated by the parent. It has queuing so running queries at the same time is not a problem.

A few edits I had to do:

1) In the query function

if (!$this->result)
   throw new Exception ('Could not query: {' . $sql . '}. MySQL error was: '.  mysql_error());

That throws an exception if the select statement returns nothing. Unnecessary.

Changed to:

if (!$this->result)
   $this->exitSemaphore();
   return;

2) In the "// SELECT etc..., we have a result set" block you can see the select statement's work. Right after that query I inserted an update to the table showing that no work is needed. This is of course a false statement as work is not finished, a child is working on it. However, it prevents children doing the same computing.

I created another mysql connection in the background to do the updating. As work is done before the Semaphore (?) is exited, other queries wait a bit. Updating using the class's connection could cause problems.