a test about mysql innodb mvcc

Question

I am learning InnoDB mvcc now and i have try a test show as follows:

Mysql version:

[root@mysql-test ~]# mysql --version
mysql  Ver 15.1 Distrib 5.5.52-MariaDB, for Linux (x86_64) using readline 5.1

table schema:

MariaDB [liruifeng]> show create table test_a;
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                          |
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
| test_a | CREATE TABLE `test_a` (
  `id` int(11) NOT NULL DEFAULT '0',
  `a` char(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

then init with data like this:

MariaDB [liruifeng]> select * from test_a;
+----+------+
| id | a    |
+----+------+
|  1 | 1    |
|  2 | 2    |
|  3 | 3    |
+----+------+
3 rows in set (0.00 sec)

and at first i have open two session in different terminals, the test step show as bellows:

t1:

MariaDB [liruifeng]> start transaction;
Query OK, 0 rows affected (0.00 sec)

MariaDB [liruifeng]> select * from test_a;
+----+------+
| id | a    |
+----+------+
|  1 | 1    |
|  2 | 2    |
|  3 | 3    |
+----+------+
3 rows in set (0.00 sec)

t2:

MariaDB [liruifeng]> insert into test_a values (4,4);
Query OK, 1 row affected (0.01 sec)

MariaDB [liruifeng]> select * from test_a;
+----+------+
| id | a    |
+----+------+
|  1 | 1    |
|  2 | 2    |
|  3 | 3    |
|  4 | 4    |
+----+------+
4 rows in set (0.00 sec)

t1:

MariaDB [liruifeng]> select * from test_a;
+----+------+
| id | a    |
+----+------+
|  1 | 1    |
|  2 | 2    |
|  3 | 3    |
+----+------+
3 rows in set (0.00 sec)

MariaDB [liruifeng]> update test_a set a = 444 where id = 4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [liruifeng]> select * from test_a;
+----+------+
| id | a    |
+----+------+
|  1 | 1    |
|  2 | 2    |
|  3 | 3    |
|  4 | 444  |
+----+------+
4 rows in set (0.00 sec)

it makes me puzzled that why a t1 can update the row insert by t2 before t1 has committed? my tx_isolation level is repeatable read and why will this update sql works?

my isolation show as bellows:

MariaDB [liruifeng]> show variables like 'tx_isolation';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.00 sec)

thanks in advances :)

Answer 1

REPEATABLE-READ says that the select * from test_a; will say the same thing until t1 COMMITs. The fact that the UPDATE can see row 4 but the identical SELECT cannot is weird, but valid.