title: Minimal example of using select... for update to isolate rows
body: <p><strong>UPDATE: There is now <code>SKIP LOCKED</code> and <code>NOWAIT</code> for Mysql and Postgres.</strong></p>
  
  <p>Old question follows.</p>
  
  <hr>
  
  <p>I want concurrent transactions to select a row from the table, marking it as "dirty" so that other transactions <strong>cannot select it</strong>, then performing the rest of the transaction.</p>
  
  <p>I had trouble using <code>select... for update</code> for this purpose as the second transaction contends for the same. Please provide a minimal example for <strong>different transactions to select distinct rows</strong>.</p>
  
  <p>My data is:</p>
  
  <pre><code>mysql&gt; select * from SolrCoresPreallocated;
  +----+-------------+-----+-----+
  | id | used_status | sid | cid |
  +----+-------------+-----+-----+
  |  1 |           0 |   0 | 400 |
  |  2 |           0 |   0 | 401 |
  |  3 |           0 |   0 | 402 |
  |  4 |           0 |   0 | 403 |
  |  5 |           0 |   0 | 404 |
  |  6 |           0 |   0 | 405 |
  +----+-------------+-----+-----+
  6 rows in set (0.00 sec)
  </code></pre>
  
  <p>And this stuff is not working as expected:</p>
  
  <pre><code>mysql&gt; begin;
  Query OK, 0 rows affected (0.00 sec)
  
  mysql&gt; select * from SolrCoresPreallocated order by id limit 1 for update;
  +----+-------------+-----+-----+
  | id | used_status | sid | cid |
  +----+-------------+-----+-----+
  |  1 |           0 |   0 | 400 |
  +----+-------------+-----+-----+
  1 row in set (0.00 sec)
  
  ...set the used_status to 1
  ...perform the rest of the operations
  </code></pre>
  
  <p>...as the second transaction onward</p>
  
  <pre><code>mysql&gt; begin;
  Query OK, 0 rows affected (0.00 sec)
  
  mysql&gt; select * from SolrCoresPreallocated order by id limit 1 for update;
  ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
  mysql&gt;  rollback;
  Query OK, 0 rows affected (0.00 sec)
  </code></pre>