title: Job queue as SQL table with multiple consumers (PostgreSQL)
body: <p>I have a typical producer-consumer problem:</p>
  <p>Multiple producer applications write job requests to a job-table on a PostgreSQL database.</p>
  <p>The job requests have a state field that starts contains QUEUED on creation.</p>
  <p>There are <strong>multiple</strong> consumer applications that are notified by a rule when a producer inserts a new record:</p>
  <pre><code>CREATE OR REPLACE RULE &quot;jobrecord.added&quot; AS
    ON INSERT TO jobrecord DO 
    NOTIFY &quot;jobrecordAdded&quot;;
  </code></pre>
  <p>They will try to reserve a new record by setting its state to RESERVED. Of course, only one consumer should succeed. All other consumers should not be able to reserve the same record. They should instead reserve other records with state=QUEUED.</p>
  <p>Example:
  some producer added the following records to table <em>jobrecord</em>:</p>
  <pre><code>id state  owner  payload
  ------------------------
  1 QUEUED null   &lt;data&gt;
  2 QUEUED null   &lt;data&gt;
  3 QUEUED null   &lt;data&gt;
  4 QUEUED null   &lt;data&gt;
  </code></pre>
  <p>now, two consumers <em>A</em>, <em>B</em> want to process them. They start running at the same time.
  One should reserve id 1, the other one should reserve id 2, then the first one who finishes should reserve id 3 and so on..</p>
  <p>In a pure multithreaded world, I would use a mutex to control access to the job queue, but the consumers are different processes that may run on different machines. They only access the same database, so all synchronization must happen through the database.</p>
  <p>I read a lot of documentation about concurrent access and locking in PostgreSQL, e.g. <a href="http://www.postgresql.org/docs/9.0/interactive/explicit-locking.html" rel="nofollow noreferrer">http://www.postgresql.org/docs/9.0/interactive/explicit-locking.html</a>
  <a href="https://stackoverflow.com/questions/389541/select-unlocked-row-in-postgresql/5616714#5616714">Select unlocked row in Postgresql</a>
  <a href="https://stackoverflow.com/questions/5636006/postgresql-and-locking/5638753#5638753">PostgreSQL and locking</a></p>
  <p>From these topics, I learned, that the following SQL statement should do what I need:</p>
  <pre><code>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
  </code></pre>
  <p>Unfortunately, when I run this in multiple consumer processes, in about 50% of the time, they still reserve the same record, both processing it and one overwriting the changes of the other.</p>
  <p>What am I missing? How do I have to write the SQL statement so that multiple consumers will not reserve the same record?</p>