选择并锁定一行，然后更新UPDATE

Question

I have a script that selects a row from MySQL database. Then updates this row. Like this:

$statement = $db->prepare("SELECT id, link from persons WHERE processing = 0");
$statement->execute();
$row = $statement->fetch();

$statement = $db->prepare("UPDATE persons SET processing = 1 WHERE id = :id");
$success = $statement->execute(array(':id' => $row['id']));

The script calls this php code multiple times simultaneously. And sometimes it SELECTS the row eventhough it should be "processing = 1" because the other script call it at the exact time.

How can I avoid this?

Answer 1

What you need to do is add some kind of lock here to prevent race conditions like the one you've created:

UPDATE persons SET processing=1 WHERE id=:id AND processing=0

That will avoid double-locking it.

To improve this even more, create a lock column you can use for claiming:

UPDATE persons
  SET processing=:processing_uuid
  WHERE processing IS NULL
  LIMIT 1

This requires a VARCHAR, indexed processing column used for claiming that has a default of NULL. If you get a row modified in the results, you've claimed a record and can go and work with it by using:

SELECT * FROM persons WHERE processing=:processing_uuid

Each time you try and claim, generate a new claim UUID key.