ever thought of using an event queue? looks much like a problem solved in the past already. using mysql for it might not be the best approach though. look at amazon simple queue or something comparable, it would be a way better fit i guess.push requests to a queue and only store the results in the DB ...
cheers lenz On Mon, Jan 26, 2009 at 3:21 PM, speed3r <[email protected]> wrote: > > 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 ;) > > > > -- iWantMyName.com painless domain registration (finally) --~--~---------~--~----~------------~-------~--~----~ NZ PHP Users Group: http://groups.google.com/group/nzphpug To post, send email to [email protected] To unsubscribe, send email to [email protected] -~----------~----~----~----~------~----~------~--~---
