title: Can Lost Update happen in read committed isolation level in PostgreSQL?
body: <p>I have a query like below in PostgreSQL:</p>
  
  <pre><code>UPDATE 
       queue 
  SET 
    queue.status   = 'PROCESSING' 
  WHERE 
      queue.status   = 'WAITING' AND
      queue.id       = (SELECT id FROM queue WHERE STATUS = 'WAITING' LIMIT 1 )
  RETURNING 
          queue.id
  </code></pre>
  
  <p>and many workers try to process one work at a time (that's why I have sub-query with limit 1). After this update, each worker grabs information about the id and processes the work, but sometimes they grab the same work and process it twice or more. The isolation level is Read Committed. </p>
  
  <p>My question is how can I guarantee one work is going to be processed once? I know there is so many post out there but I can say I have tried most of them and it didn't help () ;</p>
  
  <ul>
  <li>I have tried SELECT FOR UPDATE, but it caused <em>deadlocked</em> situation. </li>
  <li>I have tried pg_try_advisory_xact_lock, but it caused <em><a href="https://stackoverflow.com/questions/3132533/postgresql-out-of-shared-memory">out of shared
  memory</a></em></li>
  <li>I tried adding <code>AND pg_try_advisory_xact_lock(queue.id)</code> to the outer query's WHERE clause, but ... [?]</li>
  </ul>
  
  <p>Any help would be appreciated.</p>