Tenho um problema típico de produtor-consumidor:

s aplicativos produtores múltiplos gravam solicitações de trabalho em uma tabela de trabalho em um banco de dados PostgreSQ

As solicitações de trabalho têm um campo de estado iniciado que contém QUEUED na criaçã

Temmúltipl aplicativos de consumidor que são notificados por uma regra quando um produtor insere um novo registro:

CREATE OR REPLACE RULE "jobrecord.added" AS
  ON INSERT TO jobrecord DO 
  NOTIFY "jobrecordAdded";

Eles tentarão reservar um novo registro definindo seu estado para RESERVADO. Obviamente, apenas o consumidor deve ter sucesso. Todos os outros consumidores não devem poder reservar o mesmo registro. Em vez disso, eles devem reservar outros registros com state = QUEUED.

Exemplo: algum produtor adicionou os seguintes registros à tabela jobrecord:

id state  owner  payload
------------------------
1 QUEUED null   <data>
2 QUEUED null   <data>
3 QUEUED null   <data>
4 QUEUED null   <data>

agora, dois consumidoresA, B quer processá-los. Eles começam a correr ao mesmo tempo. Um deve reservar o id 1, o outro deve reservar o id 2, depois o primeiro que terminar deve reservar o id 3 e assim por diante ..

Em um mundo puro multithread, eu usaria um mutex para controlar o acesso à fila de trabalhos, mas os consumidores são processos diferentes que podem ser executados em máquinas diferentes. Eles acessam apenas o mesmo banco de dados, portanto, toda a sincronização deve ocorrer através do banco de dado

Li muita documentação sobre acesso e bloqueio simultâneos no PostgreSQL, por exemplo.http: //www.postgresql.org/docs/9.0/interactive/explicit-locking.htm Selecione a linha desbloqueada no Postgresql PostgreSQL e bloqueio

prendi que, a partir desses tópicos, a seguinte instrução SQL deve fazer o que eu precis

UPDATE jobrecord
  SET owner= :owner, state = :reserved 
  WHERE id = ( 
     SELECT id from jobrecord WHERE state = :queued 
        ORDER BY id  LIMIT 1 
     ) 
  RETURNING id;  // will only return an id when they reserved it successfully

nfelizmente, quando executo isso em vários processos do consumidor, em cerca de 50% do tempo, eles ainda reservam o mesmo registro, processando-o e um substituindo as alterações do outr

O que estou perdendo? Como preciso escrever a instrução SQL para que vários consumidores não reservem o mesmo registr