postgresql - Concurrent queries give out unexpected data

Question

I execute several processes to continuously (update + select) data from postgresql (with autocommit option enabled), and get data which should already be filtered out by executing previous update done by other processes.

This is essentially a queue, with all rows initially having status=0, and selected row-by-row in some complicated (not shown) order. Each pending row is set status=1, pid=(process id) and uniqueid=(some iterating id), but different processes for some reason share same rows.

Oversimplified (but still incorrectly working) example is below:

Table definition:

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

Update (gets next element in queue, sets process pid and iterating 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

Select (selects by process id and incrementable uniqueid):

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

I made a test bench in perl, which writes values selected to logs/$$.log and as a result different logs share same entries (though with different pid and uniqueid).

Answer 1

The answer is to use (select .. for update) like:

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