I've dealt with a similar problem once...

The key to the solution is that the $sth->rows method will return the number of rows affected by the last query-like statement. SELECT *and* UPDATE are query-like statements, so here it goes...

You have to create a control field in the table with an int value and set it's default value to some non-null value.

Then you do the following...

-- fetch 10 to avoid querying too many times....
SELECT * FROM tablex ORDER BY dateofinsertion LIMIT 10;
-- Mark the register as owned by you
UPDATE tablex SET controlfield=controlfield+1 WHERE id=$id AND control
+field=$controlfieldvalue;
[download]

When you execute the second query, you test the rows.

if ($sth->rows) {
    # it did the update, so the row is mine.
} else {
    # someone else took the register, let's try another one
}
[download]

Simple, isn't it? If some process fail, next time a process get the 10 registers, it will get that again and try to process.

How fast do you want to check for new records? Rather than repeatedly querying the table, you might consider an insert trigger (not sure if that could work) or tailing a log file instead.

I'd have a single master process monitoring the queue in a tight loop. As soon as there is a new record to deal with, the master process would spawn a worker to perform the desired tasks. Spawned processes can only deal with the record that they have been allocated. If a spawned process can not handle the task, it alerts the master process, who will reallocate the task as required

Of course, a lot of this comes down to how much resource you've got. You don't want to spawn more processes than the server can handle. You may wish to precreate a finite pool of workers if resources are a problem

# pseudocode
while (1) {
   acquire_lock();   # blocks
   choose_record();  # choose an unflagged record
   flag_record();    # this one is mine
   release_lock();   # let others in 
   process_record(); # do work
}
[download]

MySQL has "advisory" locking that is suitable for this (see MySQL functions GET_LOCK() and RELEASE_LOCK()).

Update 2: As Foxcub correctly points out below, it is futile to protect the SELECT using a MUTEX but not wrapping the DELETE in the same call. Thus I am completely wrong, my logic is entirely flawed. Apologies.

Update 3: Added readmore tags.

This was an idea that crossed my mind, but it causes an overhead because processes have to wait for the MUTEX to be released and cannot process records simultaneous.

Marcello

Read more... (1096 Bytes)

Update 2: as pointed out by the reply to this post by Foxcub I was wrong to ignore the fact that the DELETE also needs to occur within the atomic action that contains the SELECT. Apologies.

Update 3: added readmore tags.

• select all processes that have a null timestamp or a timestamp that's older than 10* Minutes
• update all selected processes with the timestamp of now.
• process all selected processes
• delete processed processes

You're still racing between the select and the update, although something like this might work:
• Update records where 'owner' column is null, with *my* value and timestamp now().
• Select records with my owner value *or* where timestamp is older than threshold. This catches the case where some records have gone stale because a process died or what-have-you.
• Process & delete selected records.
This way you're letting the database server handle updating the correct records. Depending on how speedily new records are added to the queue, it seems likely that processes will be working with records more than waiting for selects. Of course this approach doesn't help with splitting the records evenly between processes, but neither will a mutex.

Good solution,

I will try this approach shortly.

Thanks all!