title: Consistency in postgresql with locking and select for update
body: <p>I have an application that can support a certain number of concurrent actions.   This is represented by a table of "slots" in postgres. When nodes come online, they insert a number of rows into the table, one per slot.  As jobs claim the slots, they update a row in the table claiming one of the slots and release it again as they finish.</p>
  
  <p>The slots table looks like this:</p>
  
  <pre><code>CREATE TABLE slots (
      id INT8 PRIMARY KEY DEFAULT nextval('slots_seq'),
      node_name TEXT NOT NULL,
      job_name TEXT
  );
  </code></pre>
  
  <p>At any time it has some semi-fixed number of rows each of which may or may not have a job_name filled in.</p>
  
  <p>When a new job wants to start up, it runs these queries to get the name of the node it should run on:</p>
  
  <pre><code>BEGIN;
  LOCK TABLE slots IN ACCESS EXCLUSIVE MODE;
  SELECT id, node_name
      FROM slots
      WHERE job_name IS NULL
      LIMIT 1
      FOR UPDATE;
  </code></pre>
  
  <p>(the node_name and id are read out of the cursor)</p>
  
  <pre><code>UPDATE slots
      SET job_name = %(job_name)s
      WHERE id = %(slot_id)s;
  COMMIT;
  </code></pre>
  
  <p>This is often able to claim rows without losing any updates but with higher levels of concurrency, only a few rows will be claimed while many SELECT ... FOR UPDATE and UPDATE queries have been executed.  The net result is that we end up with far more jobs running than there are slots for them.</p>
  
  <p>Am I making a locking error? Is there a better way to go about this? Something that doesn't use table locks?  </p>
  
  <p>Transaction level SERIALIZABLE does not cut it, only a handful of rows are ever filled.</p>
  
  <p>I'm using postgresql version 8.4.</p>