How do I select the next row in SELECT FOR UPDATE?

Question

I am modifying my web crawler program to use SELECT FOR UPDATE to avoid multiple crawlers (with multiple children) pulling the same row to process.

How do I do this?

select * from visited where machine='1' order by revisit asc limit 1 for update

Lets say this query pulls website id # 12345

If I run this query on two crawler children, the second one waits for the first one to finish before selecting a row - is there a way for the second query to pull the next row? 12346 for example?

Each instance will select, update revisit to a future time, do work, and then commit.

Answer 1

I think you are using the wrong model of concurrency to solve your problem- SELECT... FOR UPDATE (or modifying the transaction isolation level) is used for those cases where you want to manage a row in isolation of other transactions (concurrency), creating a queue, not to coordinate executing different queries/jobs at the same time (parallelism).

The reason for this is that when you run SELECT... FOR UPDATE (and then supposedly UPDATE the row, then finish your transaction), other concurrent queries over the same query do not fail immediately- they get "locked" until the first one finishes and then lock the row again. This would be a pattern needed, for example, for a counter. You could of course add a column "processed", that changes to 1 when it finishes, so the other queries do not run again (I suggested such design on this other answer: https://dba.stackexchange.com/a/98353/30545 )- but you are wasting precious time being locked for finally not doing anything.

They way parallel processing is handled is to generate a unique list of rows in a concurrent way (with a granularity larger than one, to avoid high concurrency; but not too large so there cannot be idle processors), and then process them without worrying about overlaps. If for any reason you need to process rows in perfect order, you will have to read them in a single thread, then send those to different job runners.

If for any reason you want to still use a "try to lock the row, then move on" model, you can use a very low innodb lock timeout and handle the error, or better, GET_LOCK() / RELEASE_LOCK() with the row id and a very low timeout; but I would highly discourage that for the reasons I just said.

Sorry to be a bit vague, but your questions does not show any code to work with.