Postgresql FOR UPDATE SKIP LOCKED still selects duplicated rows

Please also note that it's essential that each thread has it's own connection if you do not want them to get in each others way.

If your application uses multiple threads of execution, they cannot share a connection concurrently. You must either explicitly control access to the connection (using mutexes) or use a connection for each thread. If each thread uses its own connection, you will need to use the AT clause to specify which connection the thread will use.

from: http://www.postgresql.org/docs/9.5/static/ecpg-connect.html

All kinds of wierd things happen if two threads share the same connection. I believe this is what is happening in your case. If you take a lock with one connection, all other threads that use the same connection will have access to the locked objects.

Permit me to suggest an alternative approach, that is really simple. The use of redis as a queue. You can either simply make use of redis-py and the lpush/rpop methods or use python-rq.

There is a chance a locking transaction is not yet issued at the time of the select, or the lock is lost by the time the results of the select are ready and the update statement begins. Have you tried explicitly beginning a transaction?

BEGIN;  WITH req AS (    SELECT id    FROM requests AS _re    WHERE _re.state = 'pending' AND _re.started_at IS NULL    LIMIT 1 FOR UPDATE SKIP LOCKED    )  UPDATE requests SET started_at = NOW(), finished_at = NULL  FROM req  WHERE requests.id = req.id;COMMIT;