I had a little think about how I might solve this problem if I didn't
have ways to control database synchronisation, and came up with this:
(described in MongrelCode (TM) ;)
-------------
// Get all available records
get recordset result for "SELECT a,b WHERE x, y and z and DONE=no"
into $rs
// Grab one record
loop through rows in $rs
$myId = unique identifier from row
run UPDATE table set DONE=doing WHERE id=$myId and DONE=no against
database
$affectedRows = mysql_affected_rows()
until $affectedRows > 0
// Be nice
call mysql_free_result() on $rs
// at this point, $myId is the unique row id for 'your' row, which has
been marked LOCK=yes
// We own the row: no other process should attempt to update or even
'use' this row
// Load the data and do our work with it
SELECT a,b WHERE id=$myId
do yada yada with the data (Can take a few seconds to a few minutes)
// Mark the record 'complete'
run UPDATE table set DONE=done WHERE id=$myId against database
-------------
This uses the MySQL database's built-in transaction and
synchronisation features to manage multi-process access. The only
place it can fall down is if MySQL allowed an incomplete UPDATE query
to be 'interrupted' and another process to UPDATE on the same row.
Which it should never do and its my understanding and experience that
it does not. The correct behaviour in a collision is for the 2nd
UPDATE query to 'fail' because behind its back the value of DONE in
the row has changed from 'do' to 'doing' by the 1st UPDATE query.
This is not a catastrophic failure, though, since it will simply step
to the next record, and the next, and the next, until it succeeds in
updating the row. I've omitted the necessary exception-handling for
when no record could be 'owned' in which case the process should just
clean up and die quietly.
Note the 3-stage 'state-machine' values in DONE - no, doing, yes: no
need for an additional LOCK field.
Hope this is interesting, and maybe even useful ;)
--~--~---------~--~----~------------~-------~--~----~
NZ PHP Users Group: http://groups.google.com/group/nzphpug
To post, send email to [email protected]
To unsubscribe, send email to
[email protected]
-~----------~----~----~----~------~----~------~--~---
