postgresql - 并发查询给出意外数据

Question

我执行几个进程以连续（更新+选择）来自 postgresql 的数据（启用自动提交选项），并通过执行其他进程完成的先前更新来获取应该已经被过滤掉的数据。

这本质上是一个队列，所有行最初的状态=0，并以某种复杂（未显示）的顺序逐行选择。每个待处理的行都设置为 status=1，pid=(process id) 和 uniqueid=(some iterating id)，但不同的进程出于某种原因共享相同的行。

过度简化（但仍无法正常工作）的示例如下：

表定义：

create table Todq (
  id bigserial primary key,
  url varchar(1024),
  status integer not null,
  pid integer,uniqueid bigint,
  priority integer
);
create index Iodq1 on Todq (status,priority,id);
create index Iodq2 on Todq (pid,uniqueid);

更新（获取队列中的下一个元素，设置进程 pid 并迭代 uniqueid）：

update Todq odq2
set status=1,pid=?,uniqueid=?
from (
    select odq.id
    from Todq odq
    where odq.status=0 
    order by odq.priority desc,odq.id
    limit 1
) odq1
where odq2.id=odq1.id

选择（按进程 ID 和可递增的唯一 ID 选择）：

select odq.id,odq.url,odq.pid,odq.uniqueid
from Todq odq
where odq.status=1 and pid=? and uniqueid=?

我在 perl 中创建了一个测试台，它将选择的值写入 logs/$$.log，因此不同的日志共享相同的条目（尽管具有不同的 pid 和 uniqueid）。

标签： postgresql

Answer 1

答案是使用（选择 .. 进行更新），例如：

update Todq odq2
set status=1,pid=?,uniqueid=?
from (
    select odq.id
    from Todq odq
    where odq.status=0 
    order by odq.priority desc,odq.id
    limit 1
    for update
) odq1
where odq2.id=odq1.id