Postgresql FOR UPDATE SKIP LOCKED still selects duplicated rows
<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>