SELECT 并锁定一行，然后 UPDATE [英] SELECT and lock a row and then UPDATE

问题描述

我有一个从 MySQL 数据库中选择一行的脚本.然后更新这一行.像这样:

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']));

脚本同时多次调用此 php 代码.有时它会选择该行，即使它应该是processing = 1"，因为另一个脚本在确切的时间调用它.

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?

推荐答案

您需要做的是在此处添加某种锁以防止像您创建的那样的竞争条件:

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

这将避免双重锁定它.

为了进一步改善这一点，请创建一个可用于声明的锁定列:

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

这需要一个 VARCHAR、索引的 processing 列，用于声明默认值为 NULL.如果您在结果中修改了一行，则您已经声明了一条记录，可以使用以下方法处理它:

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

每次尝试声明时，都会生成一个新的声明 UUID 密钥.

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

这篇关于SELECT 并锁定一行，然后 UPDATE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！