Trying to Select and Update the Same Rows Quickly

Question

I have a MySQL table that's being updated very frequently. In essence, I'm trying to grab 500 rows with multiple PHP scripts at once, and I don't want the PHP scripts to grab the same rows. I don't to use ORDER BY RAND() due to its server load with thousands of rows.

So, I thought of simply having each script set every row's status as "1" (so it wouldn't be grabbed again). So, I want to grab 500 rows where status = 0 (I use SELECT order by asc), and then have those exact 500 rows set to status "1" so that another script doesn't grab those.

Since the table is being updated all the time, I can't select 500 rows by asc order, and then update 500 rows by asc rows, because by the time it takes to start the script and do SELECT, more rows might be added.

Therefore, I need a way to SELECT 500 rows and then somehow "remember" which rows I selected and update them.

How would I go about doing SELECT and UPDATE quickly like I described?

Answer 1

Generate a unique ID for each script (just a random integer usually works fine for these purposes).

Run an UPDATE table SET status = <my random id> WHERE status = 0 LIMIT 500 query from each process.

Then have each process run a SELECT ... FROM table WHERE status = <my random id> to actually get the rows.

In essence, you "lock" the rows to your current script first, and then go retrieve the ones you successfully locked.