mysql - MySQL 中的读取提交与可重复读取？

Question

我目前正在尝试了解 MySQL 中的事务隔离，正在阅读这本书 High Performance MySQL, 2nd Edition.

下面是他们对这两个事务隔离级别的解释。

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.

问题:

1- 在 READ COMMITTED 中，如果此隔离级别意味着事务只能看到其他事务提交的更改，那么如果您运行相同的语句，在同一事务中为什么会看到不同的结果？这是否意味着以下内容？

    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- 在 REPEATABLE READ 中，如果此隔离级别允许幻读，它如何保证事务读取的任何行在后续读取中“看起来相同”？幻读不是反驳了这个级别的保证吗？

Answer 1

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

可重复阅读

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);

然后 user2 运行下一个代码:

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

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

commit;

然后是用户 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]>

已提交读

用户 1

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);

然后 user2 运行下一个代码:

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;

然后 user1 完成查询:

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)