Mysql – Read Committed Vs Repeatable Reads in MySQL

isolation-levelMySQLtransactions

I am currently trying to understand transaction isolation in MySQL, reading the book High Performance MySQL, 2nd Edition.

And here are their explanation of these two transaction isolation level.

READ COMMITTED

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.

REPEATABLE READ

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.

Questions:

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 ? 

    START TRANSACTION;
                SELECT balance FROM checking WHERE customer_id = 10233276;
                UPDATE checking SET balance = balance - 200.00 WHERE customer_id = 10233276;
            # >>> NEXT I MUST SEE THE NEW BALANCE, OR I AM WRONG ? 
            SELECT balance FROM checking WHERE customer_id = 10233276;
COMMIT;

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 ?

Best Answer

http://ronaldbradford.com/blog/understanding-mysql-innodb-transaction-isolation-2009-09-24/

REPEATABLE-READ

session 1 : 

    MariaDB [test]> DROP TABLE IF EXISTS transaction_test;
    Query OK, 0 rows affected (0.22 sec)

    MariaDB [test]> CREATE TABLE transaction_test(
        ->   id   INT UNSIGNED NOT NULL AUTO_INCREMENT,
        ->   val  VARCHAR(20) NOT NULL,
        ->   created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
        -> PRIMARY KEY(id)
        -> ) ENGINE=InnoDB DEFAULT CHARSET latin1;
    Query OK, 0 rows affected (0.29 sec)

    MariaDB [test]>
    MariaDB [test]> INSERT INTO transaction_test(val) VALUES ('a'),('b'),('c');
    Query OK, 3 rows affected (0.08 sec)
    Records: 3  Duplicates: 0  Warnings: 0

    MariaDB [test]> START TRANSACTION;
    Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> SELECT * FROM transaction_test;
+----+-----+---------------------+
| id | val | created             |
+----+-----+---------------------+
|  1 | a   | 2016-04-01 10:09:33 |
|  2 | b   | 2016-04-01 10:09:33 |
|  3 | c   | 2016-04-01 10:09:33 |
+----+-----+---------------------+
3 rows in set (0.00 sec)

MariaDB [test]> select sleep(50);

then user2 run next code : 

 MariaDB [test]> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> INSERT INTO transaction_test(val) VALUES ('x'),('y'),('z');

commit;

then user 1 

MariaDB [test]> SELECT * FROM transaction_test;
+----+-----+---------------------+
| id | val | created             |
+----+-----+---------------------+
|  1 | a   | 2016-04-01 10:09:33 |
|  2 | b   | 2016-04-01 10:09:33 |
|  3 | c   | 2016-04-01 10:09:33 |
+----+-----+---------------------+
3 rows in set (0.00 sec)

MariaDB [test]>

READ-COMMITTED

user1 

SET SESSION tx_isolation='READ-COMMITTED';
TRUNCATE TABLE transaction_test;
INSERT INTO transaction_test(val) VALUES ('a'),('b'),('c');
MariaDB [test]> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> select sleep(60);

then user2 run next code : 

MariaDB [test]> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> INSERT INTO transaction_test(val) VALUES ('x'),('y'),('zwfwfw');

Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

MariaDB [test]> commit;

then user1 finish query :

MariaDB [test]> SELECT * FROM transaction_test;
+----+--------+---------------------+
| id | val    | created             |
+----+--------+---------------------+
|  1 | a      | 2016-04-01 10:28:08 |
|  2 | b      | 2016-04-01 10:28:08 |
|  3 | c      | 2016-04-01 10:28:08 |
|  4 | x      | 2016-04-01 10:29:00 |
|  5 | 
y      | 2016-04-01 10:29:00 |
|  6 | zwfwfw | 2016-04-01 10:29:00 |
+----+--------+---------------------+
6 rows in set (0.00 sec)
Related Topic