title: Consistent read and update without table locking in MySQL/InnoDB
body: <p>I need to perform the following on MySQL/InnoDB.  Assume I have a table consists of 3 cols (id, counter, status)</p>
  
  <p>I need to:</p>
  
  <ol>
  <li>Select rows where status = 0</li>
  <li>Update them by setting status = 1, and increment counter by 1</li>
  <li>Return the selected/updated id to client</li>
  </ol>
  
  <p>Requirements:</p>
  
  <ul>
  <li>The rows to be update must exactly the same with selected, even new rows has been added after the 1st select</li>
  <li>No duplicated update on a row is allowed, even multiple scripts are running on the same data at the same time</li>
  </ul>
  
  <p>What would be the best solution in term of performance and scalability (work for large table)?</p>