MySQL 阻止两个线程选择相同的行

Question

我有处理表中json数据的php脚本：

id, data, processed_at

我同时运行多个处理未处理行的脚本：

SELECT * FROM data WHERE processed_at IS NULL

我需要确保脚本不会获取和处理另一个脚本已经在处理的相同行。因此，我又添加了两行，我使用以下查询而不是上面的简单查询：

id, data, processed_at, processing_uuid, processing_at
UPDATE data SET 
processing_uuid = '<a uuid>',
processing_at = NOW() 
WHERE processed_at IS NULL 
AND processing_uuid IS NULL;
SELECT * FROM data WHERE processing_uuid = '<a uuid>';

现在我可以处理返回的行，并且不会发生冲突。

不幸的是，我不能在我的某些表中添加这些额外的必需列，我需要一种方法来实现相同的目标。我想到了一个processing_locks表：

id, processable_table, processable_id, processing_uuid, processing_at
1, data, 1, <a uuid>, '2019-01-01 00:00:00'

不幸的是，我认为我不能使用上述方法，我可以先更新然后选择。

我希望得到一些反馈或提示，说明通常如何处理这种情况，其中多个脚本从同一表中获取和处理行。

Answer 1

假设您的数据表是

create table data (
id bigint not null,
data text,
processed_at timestamp,
primary key (id)
);

您可以按以下方式创建锁表：

create table processing_locks (
processable_table varchar(50) not null,
processable_id bigint not null,
processing_uuid varchar(50),
processing_at timestamp,
primary key (processable_table, processable_id),
index (processing_uuid)
);

现在，您可以插入未处理的行，而不是更新查询

insert ignore into processing_locks (
processable_table,
processable_id,
processing_uuid,
processing_at
)
select 'data', id, '<process_uuid>', now()
from data
where processed_at is null;

由于(processable_table, processable_id)的组合是唯一的(主键(，因此没有其他进程可以插入相同的行。

使用 UPDATE-JOIN 查询在data表中设置processed_at列：

update data d
join processing_locks l
on  l.processable_table = 'data'
and l.processable_id = d.id
set d.processed_at = l.processing_at
where l.processing_uuid = '<process_uuid>'
and d.processing_at is null;

获取要处理的所有行：

select d.*
from data d
join processing_locks l
on  l.processable_table = 'data'
and l.processable_id = d.id
where l.processing_uuid = '<process_uuid>';

准备好处理数据后，清理锁表：

delete processing_locks
where processing_uuid = '<process_uuid>';