MySQL 行锁和原子更新

Question

我正在使用 MySQL 构建一个“穷人排队系统”。它是一个包含需要执行的作业的表(表名是 queue)。我在多台机器上有多个进程，它们的工作是调用 fetch_next2 存储过程以从队列中取出一个项目。

此过程的全部意义在于确保我们永远不会让 2 个客户获得相同的工作。我认为通过使用 SELECT .. LIMIT 1 FOR UPDATE 可以让我锁定一行，这样我就可以确定它只被 1 个调用者更新(更新后它不再适合SELECT 的标准用于过滤“准备好”待处理的作业)。

谁能告诉我我做错了什么？我只是遇到过一些情况，相同的工作被分配给了 2 个不同的进程，所以我知道它不能正常工作。 :)

CREATE DEFINER=`masteruser`@`%` PROCEDURE `fetch_next2`()
BEGIN
    SET @id = (SELECT q.Id FROM queue q WHERE q.State = 'READY' LIMIT 1 FOR UPDATE);

    UPDATE queue
    SET State = 'PROCESSING', Attempts = Attempts + 1
    WHERE Id = @id;

    SELECT Id, Payload
    FROM queue
    WHERE Id = @id;
END

Answer 1

答案代码:

CREATE DEFINER=`masteruser`@`%` PROCEDURE `fetch_next2`()
BEGIN
    SET @id := 0; 
    UPDATE queue SET State='PROCESSING', Id=(SELECT @id := Id) WHERE State='READY' LIMIT 1;

    #You can do an if @id!=0 here
    SELECT Id, Payload
    FROM queue
    WHERE Id = @id;
END

您正在做的事情的问题是操作没有原子分组。您正在使用 SELECT ... FOR UPDATE syntax .文档说它阻止“在某些事务隔离级别读取数据”。但不是所有级别(我认为)。在您的第一个 SELECT 和 UPDATE 之间，另一个 SELECT 可以从另一个线程发生。您使用的是 MyISAM 还是 InnoDB？ MyISAM 可能不支持它。

确保其正常工作的最简单方法是 lock the table .

---

[编辑] 我在这里描述的方法比在上面的代码中使用 Id=(SELECT @id := Id) 方法更耗时。

另一种方法是执行以下操作:

1. 有一列通常设置为 0。
2. 执行“UPDATE ... SET ColName=UNIQ_ID WHERE ColName=0 LIMIT 1。这将确保只有 1 个进程可以更新该行，然后通过 SELECT 获取它。(UNIQ_ID 不是 MySQL 功能，只是一个变量)

如果您需要一个唯一的 ID，您可以使用带有 auto_increment 的表。

---

您也可以有点对事务执行此操作。如果您在表上启动事务，从一个线程运行 UPDATE foobar SET LockVar=19 WHERE LockVar=0 LIMIT 1;，然后在另一个线程上执行完全相同的操作，第二个线程将等待第一个线程在它获得它的行之前提交。不过，这最终可能会成为一个完整的表阻塞操作。