title: Can a field change during the execution of a MySQL query and both values be present in the result set?
body: <p>I've come across some odd defensive code. Basically it does a query like this:</p>
  
  <pre><code>select * from A join B on (A.b_id=B.id)
  </code></pre>
  
  <p>Then it iterates through the result set and whenever it meets a new row from table <code>B</code>, it caches it (by id). Afterwards only the cached copy is used, even for subsequent rows.</p>
  
  <p>It looks like it was trying to safeguard against a result set like this:</p>
  
  <pre><code> A.id | A.value | B.id | B.value
  ------+---------+------+---------
   1    | First   | 1    | Yay
   2    | Second  | 1    | Nay
  </code></pre>
  
  <p>But is this even possible? Even if the row in table <code>B</code> is updated while the <code>select</code> query is fetched half way, will it really be visible? Can the update even proceed while someone is querying the table?</p>
  
  <p>For what it's worth, I think the table at the time was MyISAM, although it's been since converted to InnoDB. Also, the code which is running the query is written in PHP. As far as I can tell, it uses the default transaction isolation level and fetch mode.</p>
  
  <hr>
  
  <p>OK, it seems I need some clarifications. Here's a code, similar to what I've found:</p>
  
  <pre><code>$sql = "select A.id a_id, A.value a_value, B.id b_id, B.value b_value from A join B on (A.b_id=B.id)";
  $res = mysql_query($sql);
  $cacheB = array();
  $A = new classA();
  $B = new classB();
  while ($row = mysql_fetch_assoc($res)) {
      $A-&gt;setData($row);
      if ( !isset($cacheB[$row['b_id']]) ) {
          $cacheB[$row['b_id']] = $row;
      }
      $B-&gt;setData($cacheB[$row['b_id']]);
  
      // Do some processing depending on $A and $B
  }
  </code></pre>
  
  <p>This code is a CLI application running from a cron job. The data from <code>$A</code> and <code>$B</code> isn't returned to anything, but depending on the contents, some external services may be called and some other DB tables may be modified. The contents of <code>classA</code>, <code>classB</code> and the processing are not relevant to this question.</p>
  
  <p>My question is - is there a point for this "safeguard", or is it a deadweight that can be deleted? Let's assume that the processing part would actually be sensitive to a change in the values of <code>B</code> (although in reality I doubt it, but still).</p>