title: atomic compare and swap in a database
body: <p>I am working on a work queueing solution.  I want to query a given row in the database, where a status column has a specific value, modify that value and return the row, and I want to do it atomically, so that no other query will see it:</p>
  
  <pre>
  <code>
  begin transaction
  select * from table where pk = x and status = y
  update table set status = z where pk = x
  commit transaction
  --(the row would be returned)
  </code>
  </pre>
  
  <p>it must be impossible for 2 or more concurrent queries to return the row (one query execution would see the row while its status = y) -- sort of like an interlocked CompareAndExchange operation.</p>
  
  <p>I know the code above runs (for SQL server), but will the swap always be atomic?</p>
  
  <p>I need a solution that will work for SQL Server and Oracle</p>