scaling a hand rolled 'SELECT FOR LOCK"

[pedalpete]

I think this is pretty easy to build, but not sure how well it will scale, and therefore if it will actually work.

I am trying to attach an indexer to a request so that as a search query gets requested, it is more likely to be up to have a deeper data set (easier to see than describe, but it isn't there yet).
In a nutshell, I don't have the resources to go and scour a ton of data, but i've grabbed some starting points, and as the site gets used, those starting points direct what data should be indexed deeper.

What I'm trying to avoid is having 3 people request the same query/data to update at the same time, and have the indexer go and run and update that query's results.
Because they would be rewriting the same data to the same database at the same time.

I don't think the mysql UPDATE FOR LOCK (or something like that) will work because you can still make a request to the locked line, it just can't be updated is my understanding.

the query fields are already populated with unique id's so I can't use mysql_query_id(), as I think that is only for INSERT statements, not UPDATE.

So, what I'm thinking is two mysql calls one after the other.

$gettingIndex = "SELECT id FROM queries WHERE look_up = '0' LIMIT 0, 1 ; UPDATE queries SET look_up = '1' WHERE id = '0' LIMIT 1;"
mysql_query($gettingIndex);
 

so I think this will work with low traffic but any idea if high traffic will cause it to break?
Unfortunately for me, as traffic increases, I am exponentially increasing the server load, but I may seperate my read and writes to seperate tables, and then schedule to merge the tables on a regular basis.

Anybody ideas of another way of doing this?

[dlite922]

I'm sorry. But I still don't fully understand what you're trying to achieve. Here's what I grabbed

You have a table full of data. Users make updates. You don't want users to make the same update.

I'm lost at starting point, lock update etc. I understand what MySQL table locking is though.

Please elaborate more on what this system does if you can.

Dan

[pedalpete]

sorry it took me so long to respond to this thread again, i was busy trying to get my site launched.

As I said, easier to show than describe.
if you go to [url removed], you can enter a city and it will show you live shows playing near that city.

then I tried describing what I am trying to do here, and it got even more confusing...it needs pictures, so I'll have to sit down with an expert...thanks for trying.
Pete

[dlite922]

sorry it took me so long to respond to this thread again, i was busy trying to get my site launched.

As I said, easier to show than describe.
if you go to [url removed], you can enter a city and it will show you live shows playing near that city.

then I tried describing what I am trying to do here, and it got even more confusing...it needs pictures, so I'll have to sit down with an expert...thanks for trying.
Pete

Ok, best of luck to you. I love challenges, when you can tell us what it is you need, we'll figure out a way.

Come back soon,


Dan