MySQLのREPEATABLE READでLocking Readの挙動を見る

MySQLのREPEATABLE READでLocking Readはphantom readを防げるのか実験。

 mysql[20:33:28] >  select version();
+-----------------------+
| version()             |
+-----------------------+
| 5.7.22-0ubuntu18.04.1 |
+-----------------------+
1 row in set (0.00 sec)d

Table定義

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c1` int(11) DEFAULT NULL,
  `c2` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

準備

 mysql[20:10:44] >  select * from t1;
+----+------+------+
| id | c1   | c2   |
+----+------+------+
|  1 |    1 |    1 |
|  2 |    1 |   10 |
|  3 |    1 |   20 |
|  4 |    2 |    2 |
|  5 |    3 |    3 |
+----+------+------+
5 rows in set (0.00 sec)

プロンプトを変えておく

prompt mysql[\R:\m:\s] > \_

実験 (promptの時刻はpromptが表示された時刻なので、実行した順序に整理)

-- Session1                                                                   -- Session2

                                                                               mysql[20:09:53] >  select @@global.tx_isolation, @@session.tx_isolation;
                                                                              +-----------------------+------------------------+
                                                                              | @@global.tx_isolation | @@session.tx_isolation |
                                                                              +-----------------------+------------------------+
                                                                              | REPEATABLE-READ       | REPEATABLE-READ        |
                                                                              +-----------------------+------------------------+
                                                                              1 row in set, 2 warnings (0.00 sec)


 mysql[20:11:26] >  select @@global.tx_isolation, @@session.tx_isolation;
+-----------------------+------------------------+
| @@global.tx_isolation | @@session.tx_isolation |
+-----------------------+------------------------+
| REPEATABLE-READ       | REPEATABLE-READ        |
+-----------------------+------------------------+
1 row in set, 2 warnings (0.00 sec)

                                                                               mysql[20:11:32] >  begin;
                                                                              Query OK, 0 rows affected (0.00 sec)

 mysql[20:11:31] >  begin;
Query OK, 0 rows affected (0.00 sec)

 mysql[20:11:37] >  select c2 from t1 where c1 = 1 for update;
+------+
| c2   |
+------+
|    1 |
|   10 |
|   20 |
+------+
3 rows in set (0.00 sec)


                                                                               mysql[20:11:40] >  insert into t1 (c1, c2) values (1, 15); -- <- blockされる

 mysql[20:12:04] >  commit;
Query OK, 0 rows affected (0.01 sec)

                                                                              Query OK, 1 row affected (25.26 sec) -- session1がcommitされるとinsert完了する



                                                                               mysql[20:13:02] >  commit;
                                                                              Query OK, 0 rows affected (0.01 sec)

                                                                               mysql[20:13:14] >  select * from t1 where c1 = 1;
                                                                              +----+------+------+
                                                                              | id | c1   | c2   |
                                                                              +----+------+------+
                                                                              |  1 |    1 |    1 |
                                                                              |  2 |    1 |   10 |
                                                                              |  3 |    1 |   20 |
                                                                              |  6 |    1 |   15 |
                                                                              +----+------+------+
                                                                              4 rows in set (0.00 sec)

結論

MySQLのREPEATABLE READでLocking Readはphantom readを防げる

補足

整理する前のターミナルコピペ

session1

 mysql[20:11:26] >  select @@global.tx_isolation, @@session.tx_isolation;
+-----------------------+------------------------+
| @@global.tx_isolation | @@session.tx_isolation |
+-----------------------+------------------------+
| REPEATABLE-READ       | REPEATABLE-READ        |
+-----------------------+------------------------+
1 row in set, 2 warnings (0.00 sec)

 mysql[20:11:31] >  begin;
Query OK, 0 rows affected (0.00 sec)

 mysql[20:11:37] >  select c2 from t1 where c1 = 1 for update;
+------+
| c2   |
+------+
|    1 |
|   10 |
|   20 |
+------+
3 rows in set (0.00 sec)

 mysql[20:12:04] >  commit;
Query OK, 0 rows affected (0.01 sec)

session2

 mysql[20:09:53] >  select @@global.tx_isolation, @@session.tx_isolation;
+-----------------------+------------------------+
| @@global.tx_isolation | @@session.tx_isolation |
+-----------------------+------------------------+
| REPEATABLE-READ       | REPEATABLE-READ        |
+-----------------------+------------------------+
1 row in set, 2 warnings (0.00 sec)

 mysql[20:11:32] >  begin;
Query OK, 0 rows affected (0.00 sec)

 mysql[20:11:40] >  insert into t1 (c1, c2) values (1, 15);
Query OK, 1 row affected (25.26 sec)

 mysql[20:13:02] >  commit;
Query OK, 0 rows affected (0.01 sec)

 mysql[20:13:14] >  select * from t1 where c1 = 1;
+----+------+------+
| id | c1   | c2   |
+----+------+------+
|  1 |    1 |    1 |
|  2 |    1 |   10 |
|  3 |    1 |   20 |
|  6 |    1 |   15 |
+----+------+------+
4 rows in set (0.00 sec)