> A general-case good design for this is to have status fields, as
> suggested, and who-owns-this-row fields. Set the rows initially to
> un-owned (NULL), and status 'new' or similar.
The "get_lock" approach was ultimately successful, and I think better than
using a status field as it is entirely self-managing. If one of the
compute clients die, its locks are immediately released and another
compute client can immediately pick up where it left off. I just had to
separate the "get_lock" query from the "select" query for it to work.
I also optimized the select query by adding a "distance" column, and
setting that to "sqrt(pow(x, 2) + pow(y, 2))" and then ordering the select
clause by that column as opposed to running the function each time and
that sped up the select query to less than a second per run.
I also plan on changing the limit clause from "limit %d, 1" to something
like "limit 500" and then iterating through the results until I get one
that succeeds, as that is more efficient than running multiple select
queries.
Anyhow, thanks for all the good suggestions and pointers - it all worked
out in the end!
Tim Gustafson
831-332-1496
tjg@stripped
http://tgustafson.com/
