title: Read Committed Vs Repeatable Reads in MySQL?
body: <p>I am currently trying to understand transaction isolation in MySQL, reading the book <a href="http://shop.oreilly.com/product/9780596101718.do" rel="noreferrer">High Performance MySQL, 2nd Edition.</a> </p>
  
  <p>And here are their explanation of these two transaction isolation level. </p>
  
  <blockquote>
    <p>READ COMMITTED </p>
    
    <p>The default isolation level for most database systems
    (but not MySQL!) is READ COMMITTED . It satisfies the simple
    definition of isolation used earlier: a transaction will see only
    those changes made by transactions that were already committed when it
    began, and its changes won’t be visible to others until it has
    committed. This level still allows what’s known as a nonrepeatable
    read. This means you can run the same statement twice and see
    different data.</p>
    
    <p>REPEATABLE READ </p>
    
    <p>REPEATABLE READ solves the problems that READ
    UNCOMMITTED allows. It guarantees that any rows a transaction reads
    will “look the same” in subsequent reads within the same transaction,
    but in theory it still allows another tricky problem: phantom reads.
    Simply put, a phantom read can happen when you select some range of
    rows, another transaction inserts a new row into the range, and then
    you select the same range again; you will then see the new “phantom”
    row. InnoDB and Falcon solve the phantom read problem with
    multiversion concur- rency control, which we explain later in this
    chapter. REPEATABLE READ is MySQL’s default transaction isolation
    level. The InnoDB and Falcon storage engines respect this setting,
    which you’ll learn how to change in Chapter 6. Some other storage
    engines do too, but the choice is up to the engine.</p>
  </blockquote>
  
  <p><strong><em>Questions</em></strong>:</p>
  
  <p>1- In READ COMMITTED if this isolation level means that transaction can only see changes that were committed by other transactions, how come during the same transaction if you run same statement you can see different results ? 
  Does that mean the following ? </p>
  
  <pre><code>    START TRANSACTION;
                  SELECT balance FROM checking WHERE customer_id = 10233276;
                  UPDATE checking SET balance = balance - 200.00 WHERE customer_id = 10233276;
              # &gt;&gt;&gt; NEXT I MUST SEE THE NEW BALANCE, OR I AM WRONG ? 
              SELECT balance FROM checking WHERE customer_id = 10233276;
  COMMIT;
  </code></pre>
  
  <p>2- In REPEATABLE READ if this isolation level allows phantom read, how come it guarantees that any rows a transaction reads will "look the same" in subsequent reads ? Doesn't the phantom reads refutes the guarantee this level has ? </p>