title: Postgresql FOR UPDATE SKIP LOCKED still selects duplicated rows
body: <p>I am using PostgreSQL as a job queue. Following is my query to retrieve a job and update its state:</p>
  
  <pre><code>        UPDATE requests AS re
          SET
            started_at = NOW(),
            finished_at = NULL
          FROM (
            SELECT
              _re.*
            FROM requests AS _re
            WHERE
              _re.state = 'pending'
            AND
              _re.started_at IS NULL
            LIMIT 1
            FOR UPDATE SKIP LOCKED
          ) AS sub
          WHERE re.id = sub.id
          RETURNING
            sub.*
  </code></pre>
  
  <p>Now, I have several machines, on each machine I have 1 process with several threads, and on each thread I have a worker. All workers in the same process shared a connection pool, typically having 10 - 20 connections.</p>
  
  <p>The problem is, the above query will return some rows more than once!</p>
  
  <p>I cannot find any reasons. Could anyone help?</p>
  
  <p>To be more detailed, I am using Python3 and psycopg2.</p>
  
  <hr>
  
  <p>Update:</p>
  
  <p>I have tried @a_horse_with_no_name's answer, but seems not work.</p>
  
  <p>I noticed that, one request is retrieved by two queries with the <code>started_at</code> updated to:</p>
  
  <blockquote>
    <p>2016-04-21 14:23:06.970897+08 </p>
  </blockquote>
  
  <p>and</p>
  
  <blockquote>
    <p>2016-04-21 14:23:06.831345+08</p>
  </blockquote>
  
  <p>which are only differed by 0.14s.</p>
  
  <p>I am wondering if at the time those two connections executes the inner SELECT subquery, both locks are not established yet?</p>
  
  <hr>
  
  <p>Update:</p>
  
  <p>To be more precise, I have 200 workers (i.e. 200 threads) in 1 process on 1 machine.</p>