REPEATABLE READ の一貫性読み取りが保証されないことがある

前提

• MySQL 8.0
• 公式ドキュメントにあるように、一貫性読み取りが保証されないケースについて

注記
データベースの状態のスナップショットは、トランザクション内の SELECT ステートメントに適用されますが、DML ステートメントには必ずしも適用されるとは限りません。 一部の行を挿入または変更してから、そのトランザクションをコミットする場合は、そのセッションでクエリーが実行される可能性がない場合でも、別の並列実行 REPEATABLE READ トランザクションから発行された DELETE または UPDATE ステートメントによって、コミットされたばかりの行が影響を受ける可能性があります。 トランザクションによって別のトランザクションでコミットされた行が更新または削除されると、これらの変更を現在のトランザクションに表示できるようになります。 たとえば、次のような状況が発生する可能性があります。

サマリー

• REPEATABLE READ の MVCC によって、一貫性読み取りが行われる
  • 該当トランザクションの非ロック参照クエリにおいて、クエリ実行時のスナップショット結果を返す
  • ※ ロッキングリード/DML の場合は、その時点の最新データを読み取る
• しかし、別トランザクションのコミット影響によって、一貫性読み取りが保証されないことがある
  • 別トランザクションで挿入/更新コミットされたレコードを、該当トランザクションで更新/削除した場合の影響
• 意図していないと、データ不整合が起きるので気を付けましょう
  • 一貫性読み取り結果に依存している処理など？

検証

-- セッションA
mysql> SELECT @@GLOBAL.transaction_isolation, @@transaction_isolation;
+--------------------------------+-------------------------+
| @@GLOBAL.transaction_isolation | @@transaction_isolation |
+--------------------------------+-------------------------+
| REPEATABLE-READ                | REPEATABLE-READ         |
+--------------------------------+-------------------------+
1 row in set (0.01 sec)

mysql> begin;
Query OK, 0 rows affected (0.01 sec)

-- MVCC によってスナップショットが取られる
mysql> select * from tests;
+----+------+-----------+
| id | id2  | str       |
+----+------+-----------+
|  1 |    1 | test      |
|  2 |    2 | テスト    |
+----+------+-----------+
2 rows in set (0.00 sec)

-- ここでセッションBが実行される



-- セッションB
mysql> SELECT @@GLOBAL.transaction_isolation, @@transaction_isolation;
+--------------------------------+-------------------------+
| @@GLOBAL.transaction_isolation | @@transaction_isolation |
+--------------------------------+-------------------------+
| REPEATABLE-READ                | REPEATABLE-READ         |
+--------------------------------+-------------------------+
1 row in set (0.01 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from tests;
+----+------+-----------+
| id | id2  | str       |
+----+------+-----------+
|  1 |    1 | test      |
|  2 |    2 | テスト    |
+----+------+-----------+
2 rows in set (0.00 sec)

-- 別トランザクションでレコードを挿入する
mysql> INSERT INTO tests (id2, str) VALUES (2,'テスト2');
Query OK, 1 row affected (0.00 sec)

mysql> select * from tests;
+----+------+------------+
| id | id2  | str        |
+----+------+------------+
|  1 |    1 | test       |
|  2 |    2 | テスト     |
|  6 |    2 | テスト2    |
+----+------+------------+
3 rows in set (0.01 sec)

mysql> commit;
Query OK, 0 rows affected (0.01 sec)



-- セッションA
-- 一貫性読み取り結果が返ってくる
mysql> select * from tests;
+----+------+-----------+
| id | id2  | str       |
+----+------+-----------+
|  1 |    1 | test      |
|  2 |    2 | テスト    |
+----+------+-----------+
2 rows in set (0.00 sec)

-- 別トランザクションの挿入レコードを更新する
mysql> update tests set str = 'test' where id2 = 2;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

-- 読み取り結果が変わる（別トランザクションの挿入レコードが参照される）
mysql> select * from tests;
+----+------+------+
| id | id2  | str  |
+----+------+------+
|  1 |    1 | test |
|  2 |    2 | test |
|  6 |    2 | test |
+----+------+------+
3 rows in set (0.00 sec)