锁定 MySQL 选择行，直到对它运行 UPDATE 为止? [英] Lock MySQL select row until UPDATE has been ran on it?

问题描述

我有一个名为 queue_items 的表，它有 3 列.

I have a table called queue_items that has 3 columns.

'item' varchar(500) the item that has been queued
'processed_at' a datetime to mark when it has started processing, and
'completed_at' a datetime to mark when it has completed in the queue worker

这是我选择项目的方式，

Here is how I am selecting items,

SELECT `id`,`item` FROM `queue_items` WHERE `processed_at` IS NULL AND `completed_at` IS NULL ORDER BY `id` ASC LIMIT 1

一旦检查它存在并且可以被处理，我就运行这个:

Once checking it exists and is valid to be processed, I run this:

UPDATE `queue_items` SET `processed_at` = @processedAt WHERE `id` = @id

问题是，在我更新 processed_at 之前，另一个队列工作人员可以拿起我刚刚选择的项目.我怎样才能阻止这种情况发生?

The issue is, another queue worker can pick up the item that I just selected, before I update processed_at. How can I stop this happening?

推荐答案

在两个查询周围放置一个事务，并使用 SELECT 查询中的 FOR UPDATE 选项来锁定它检查的行.任何其他尝试读取该行的连接都将挂起，直到事务提交.

Put a transaction around the two queries, and use the FOR UPDATE option in the SELECT query to lock the rows that it examined. Any other connection that tries to read that row will be suspended until the transaction is committed.

确保您在 WHERE 子句中测试的列上有一个索引，这样在找到您想要的行之前，它不必扫描并锁定它检查的所有行.

Make sure you have an index on the columns you test in the WHERE clause, so it won't have to do a scan and lock all the rows it checked before finding the one you want.

START TRANSACTION;

SELECT @id := `id`,`item` 
FROM `queue_items` 
WHERE `processed_at` IS NULL AND `completed_at` IS NULL 
ORDER BY `id` ASC 
LIMIT 1
FOR UPDATE;

UPDATE `queue_items` SET `processed_at` = @processedAt WHERE `id` = @id

COMMIT;

这篇关于锁定 MySQL 选择行，直到对它运行 UPDATE 为止?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！