title: Why does this select for update example work?
body: <p>I'm running some simple scripts to test possible solutions for an integrity problem I'm solving. Suppose I have a table <code>my_table</code></p>
  
  <pre><code>|foo     |
  |1       |
  </code></pre>
  
  <p>And I have these two snippets:</p>
  
  <pre><code>// db_slow.php
  &lt;?php
  $db = new PDO('mysql:host=localhost;dbname=my_playground;charset=utf8', 'root', '');
  echo 'starting transaction&lt;br /&gt;';
  $db-&gt;beginTransaction();
  $stmt = $db-&gt;query('select * from my_table for update');
  $rows = $stmt-&gt;fetchAll();
  echo 'count tables: ', count($rows), '&lt;br /&gt;';
  if (count($rows) == 1) {
      sleep(10);
      $db-&gt;query('insert into my_table(foo) VALUES(2)');
  }
  $db-&gt;commit();
  echo 'done';
  
  // db_fast.php
  &lt;?php
  $db = new PDO('mysql:host=localhost;dbname=my_plyaground;charset=utf8', 'root', '');
  echo 'starting transaction&lt;br /&gt;';
  $db-&gt;beginTransaction();
  $stmt = $db-&gt;query('select * from my_table for update');
  $rows = $stmt-&gt;fetchAll();
  echo 'count tables: ', count($rows), '&lt;br /&gt;';
  if (count($rows) == 1) {
      $db-&gt;query('insert into my_table(foo) VALUES(3)');
  }
  $db-&gt;commit();
  echo 'done';
  </code></pre>
  
  <p><code>db_slow.php</code> has a 10 second delay to simulate a race condition.</p>
  
  <p>As I understand, <code>select ... for update</code> locks all rows it selects. If I run <code>db_slow</code> then <code>db_fast</code>, <code>db_fast</code> also has a 10 second delay, as it's waiting for <code>db_slow</code> as I expect.</p>
  
  <p>However, what I don't get is this is the output:</p>
  
  <pre><code>// db_slow.php
  starting transaction
  count tables: 1
  done
  
  // db_fast.php
  starting transaction
  count tables: 2
  done
  </code></pre>
  
  <p>And <code>my_table</code></p>
  
  <pre><code>|foo      |
  |1        |
  |2        |
  </code></pre>
  
  <hr>
  
  <p>As I understand, <code>select ... for update</code> locks all rows that are selected for that transaction. So this is what I expect:</p>
  
  <ol>
  <li>db_slow: select row 1 and lock it</li>
  <li>db_slow: see that it's only 1 row and wait</li>
  <li>db_fast: try to select row 1, see that it's locked, wait</li>
  <li>db_slow: insert row with '2'</li>
  <li>db_fast: continues because row 1 is unlocked</li>
  <li>db_fast: only selected 1 row, so it inserts '3'</li>
  <li>End up with <code>foo: 1, 2, 3</code></li>
  </ol>
  
  <p>The output and delay described above seems to confirm steps 1, 2, 3, 4. It seems like <code>db_fast</code> is running select after trying to obtain a lock? I thought it would select the one row, then lock or wait.</p>
  
  <hr>
  
  <p>Somewhat related question:</p>
  
  <p>When I run this with <code>select ... lock in share mode</code> I end up with</p>
  
  <pre><code>// db_slow.php
  starting transaction
  count tables: 1
  done
  
  // db_fast.php
  starting transaction
  count tables: 1
  done
  </code></pre>
  
  <p>And <code>my_table</code></p>
  
  <pre><code>|foo      |
  |1        |
  |3        |
  </code></pre>
  
  <p>Why is <code>db_slow</code> not inserting a row '2' even when it thinks there's only 1 row in the table (the condition to insert a row)?</p>