CODEWITHSUNDEEP
phpmysql
Nguyn
Nguyn

Reputation: 11

SELECT and lock a row and then UPDATE

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?

Upvotes: 1

Views: 1303

Answers (3)

Jaydee
Jaydee

Reputation: 4158

Use SELECT FOR UPDATE

http://dev.mysql.com/doc/refman/5.0/en/innodb-locking-reads.html

eg

SELECT counter_field FROM child_codes FOR UPDATE; UPDATE child_codes SET counter_field = counter_field + 1;

Wrap this in a transaction and the locks will be released when the transaction ends.

Upvotes: 0

RichardBernards
RichardBernards

Reputation: 3097

Try using transactions for your queries. Read about them at the mysql dev site

You can wrap your code with:

$dbh->beginTransaction();

// ... your transactions here

$dbh->commit();

You'll find the documentation here.

Upvotes: 2

tadman
tadman

Reputation: 211720

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.

Upvotes: 3

Related Questions