title: MySQL SELECT returning old value
body: <p>I have a application that unfortunately uses legacy mysql_* functions with MyISAM tables (bleagh...), so I cannot use transactions. I have code that gets a current balance, checks whether this balance is okay, if yes, it will subtract a quantity and save the new balance.</p>
  
  <p>The problem is, I have recently seen an instance where two queries grab the same starting balance, subtract a quantity, then record a new balance. Since they both grabbed the same starting balance, the ending balance after both UPDATES is wrong.</p>
  
  <pre><code>100 - 10 = 90
  100 - 15 = 85
  </code></pre>
  
  <p>When it should be...</p>
  
  <pre><code>100 - 10 = 90
  90 - 15 = 75
  </code></pre>
  
  <p>These requests executed a several minutes apart, so I do not believe the discrepancy is due to race conditions. My initial though is that MySQL cache is storing the result of the identical initial query that gets the balance. I read however that this type of cache is deleted if any relevant tables are modified.</p>
  
  <p>I will most likely fix by putting everything into one query, but I still would like to figure this out. It mystifies me. If cache is deleted when a table is modified, then what happened shouldn't have happened. Has anyone heard of something like this, or have any ideas as to why it may have happened?</p>