Tengo un problema típico de productor-consumidor:

as aplicaciones de productores múltiples escriben solicitudes de trabajo en una tabla de trabajo en una base de datos PostgreSQL.

Las solicitudes de trabajo tienen un campo de estado que comienza contiene QUEUED en la creación.

Existenmúltipl aplicaciones de consumidor que son notificadas por una regla cuando un productor inserta un nuevo registro:

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

Intentarán reservar un nuevo registro estableciendo su estado en RESERVADO. Por supuesto, solo en el consumidor debe tener éxito. Todos los demás consumidores no deberían poder reservar el mismo registro. En su lugar, deberían reservar otros registros con state = QUEUED.

Ejemplo: algún productor agregó los siguientes registros a la tabla jobrecord:

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

ahora, dos consumidoresA, B quiere procesarlos. Comienzan a correr al mismo tiempo. Uno debe reservar el id 1, el otro debe reservar el id 2, luego el primero que termine debe reservar el id 3 y así sucesivamente ..

En un mundo multiproceso puro, usaría un mutex para controlar el acceso a la cola de trabajos, pero los consumidores son procesos diferentes que pueden ejecutarse en diferentes máquinas. Solo acceden a la misma base de datos, por lo que toda la sincronización debe realizarse a través de la base de datos.

Leí mucha documentación sobre el acceso concurrente y el bloqueo en PostgreSQL, p. @http: //www.postgresql.org/docs/9.0/interactive/explicit-locking.htm Seleccione la fila desbloqueada en Postgresql PostgreSQL y bloqueo

e estos temas, aprendí, que la siguiente instrucción SQL debería hacer lo que necesito:

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

esafortunadamente, cuando ejecuto esto en múltiples procesos de consumo, en aproximadamente el 50% del tiempo, todavía se reservan el mismo registro, tanto procesándolo como sobrescribiendo los cambios del otr

¿Qué me estoy perdiendo? ¿Cómo debo escribir la declaración SQL para que varios consumidores no reserven el mismo registro?