MySQLのREPEATABLE READとREAD COMMITTEDの違いを知るために色々試した

MySQLのトランザクション分離レベルについてふんわりとした理解しかないなと感じた。もう少し理解するために、とくにREPEATABLE READとREAD COMMITTEDの違いを手を動かして色々確認してみた。

以下の記事を参考にした。

大まかな違い

公式ドキュメントを見る限り

ノンリピータブルリード、ファントムリードが発生するか
範囲に含まれるギャップへのほかのセッションによる挿入をブロックするか

の違いがありそうに見える。

ノンリピータブルリード、ファントムリードが発生するかを試す

以下のテーブルを作る。

CREATE TABLE `posts` (
  `title` varchar(255) NOT NULL,
  `body` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

REPEATABLE READの場合

まずデフォルトのREPEATABLE READから。

# トランザクション1
begin;

# トランザクション2
begin;

# トランザクション1
insert into posts (title, body) values('title1', 'body1');

# トランザクション2
# ダーティリードが発生していない
select * from posts;
Empty set (0.01 sec)
select count(*) from posts;
+----------+
| count(*) |
+----------+
|        0 |
+----------+

# トランザクション1
commit;

# トランザクション2
# ノンリピータブルリードが発生していない
select * from posts;
Empty set (0.01 sec)
# ファントムリードが発生していない
select count(*) from posts;
+----------+
| count(*) |
+----------+
|        0 |
+----------+

# トランザクション2
rollback;
# トランザクション1のCOMMITが見えるようになる
select * from posts;
+--------+-------+
| title  | body  |
+--------+-------+
| title1 | body1 |
+--------+-------+
select count(*) from posts;
+----------+
| count(*) |
+----------+
|        1 |
+----------+

ノンリピータブルリードもファントムリードも発生していないことが確認できる。REPEATABLE READの説明ではファントムリードは起こると書いていることが多いが実装次第であり、MySQLにおいてはファントムリードも起こらなくなっている。

READ COMMITTEDの場合

# トランザクション2
set session transaction isolation level read committed;
begin;

# トランザクション1
begin;
insert into posts (title, body) values('title1', 'body1');

# トランザクション2
# ダーティリードが発生していない
select * from posts;
Empty set (0.01 sec)

# トランザクション1
commit;

# トランザクション2
# ノンリピータブルリードが発生した
> select * from posts;
+--------+-------+
| title  | body  |
+--------+-------+
| title1 | body1 |
+--------+-------+

ちゃんとノンリピータブルリードが起こることが確認できる。

範囲に含まれるギャップへのほかのセッションによる挿入をブロックするか

続いて、ロック範囲についても検証する。テーブル構造をこのようにする。

CREATE TABLE `posts` (
  `id` int NOT NULL,
  `title` varchar(255) NOT NULL,
  `body` text NOT NULL,
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

両方ともREPEATABLE READ

> select * from posts;
+----+--------+-------+
| id | title  | body  |
+----+--------+-------+
|  1 | title1 | body1 |
|  2 | title2 | body2 |
+----+--------+-------+

# トランザクション1
begin;

# トランザクション2
begin;

# トランザクション1
UPDATE posts SET title = 'title2updated' WHERE id >= 2 and id < 5;

# トランザクション2
insert into posts (id, title, body) values (10, 'title10', 'body10');
# -> lockされる

特定範囲をUPDATEかけた場合、他のトランザクションからギャップ範囲に対してINSERTをしたときにロックされることが確認できた。

UPDATE側がREPEATABLE READ, INSERT側がREAD COMMITTED

# トランザクション1
set session transaction isolation level read committed;
begin;

# トランザクション2
begin;
UPDATE posts SET title = 'title2updated' WHERE id >= 2 AND id < 5;

# トランザクション1
insert into posts (id, title, body) values (10, 'title10', 'body10');
# -> lockされる

UPDATE側がREPEATABLE READなら、INSERT側がREAD COMMITTEDだとしてもロックされることが確認できた。

UPDATE側がREAD COMMITTED, INSERT側がREPEATABLE READ

# トランザクション1
set session transaction isolation level read committed;
begin;

# トランザクション2
begin;

# トランザクション1
UPDATE posts SET title = 'title2updated' WHERE id >= 2 AND id < 5;

# トランザクション2
insert into posts (id, title, body) values (10, 'title10', 'body10');
# -> lockされない

UPDATE側がREAD COMMITTEDなら、INSERT側がロックされないことを確認できた。

まとめ

MySQLにおいてはREPEATABLE READとREAD COMMITTEDの違いは大まかに以下がありそうだ。

ノンリピータブルリード、ファントムリードが発生するか
範囲に含まれるギャップへのほかのセッションによる挿入をブロックするか

実際に手を動かして試したところ、

ノンリピータブルリード・ファントムリードは、REPEATABLE READでは発生せず、READ COMMITTEDでは発生する
REPEATABLE READではUPDATEやDELETEの範囲のギャップロックがかかるのでほかセッションによるギャップ範囲への挿入はブロックされる。READ COMMITTEDではギャップロックがかからない

ということを確認できた。