Zenn
Open12

MySQL 8: 分離レベル

ほげさんほげさん

問題

  • ダーティリード
  • ファジーリード
  • ノンリピータブルリード
  • ファントムリード

これらとどう付き合うか

ほげさんほげさん

READ UNCOMMITTED

コミットされていない別セッションの更新が見える

Reader> select * from item;
+----+------+-------+
| id | name | price |
+----+------+-------+
|  1 | foo  |  1000 |
|  2 | bar  |  2000 |
+----+------+-------+
2 rows in set (0.01 sec)

Reader> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)

Reader> select * from item;
+----+------+-------+
| id | name | price |
+----+------+-------+
|  1 | foo  |  1000 |
|  2 | bar  |  5000 |
+----+------+-------+
2 rows in set (0.00 sec)

ダーティーリードが起きる

ほげさんほげさん

READ COMMITTED

コミットされたものなら見える

Reader> select * from item;
+----+------+-------+
| id | name | price |
+----+------+-------+
|  1 | foo  |  1000 |
|  2 | bar  |  2000 |
+----+------+-------+
2 rows in set (0.00 sec)

commit

Reader> select * from item;
+----+------+-------+
| id | name | price |
+----+------+-------+
|  1 | foo  |  1000 |
|  2 | bar  |  5000 |
+----+------+-------+
2 rows in set (0.00 sec)

ファジーリードが起きる

ほげさんほげさん

REPEATABLE READ

一度参照した結果が繰り返される
( 一度参照というか、トランザクション開始前のものが繰り返し参照できるっぽい )

Reader> select * from item;
+----+------+-------+
| id | name | price |
+----+------+-------+
|  1 | foo  |  1000 |
|  2 | bar  |  2000 |
+----+------+-------+
2 rows in set (0.00 sec)

update

Reader> select * from item;
+----+------+-------+
| id | name | price |
+----+------+-------+
|  1 | foo  |  1000 |
|  2 | bar  |  2000 |
+----+------+-------+
2 rows in set (0.00 sec)

commit

Reader> select * from item;
+----+------+-------+
| id | name | price |
+----+------+-------+
|  1 | foo  |  1000 |
|  2 | bar  |  5000 |
+----+------+-------+
2 rows in set (0.01 sec)

トランザクション開始時にスナップショットを取得する

読み取り時にロックはしない

ほげさんほげさん

SERIALIZABLE

参照もロックする

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

Reader> select * from item;
+----+------+-------+
| id | name | price |
+----+------+-------+
|  1 | foo  |  1000 |
|  2 | bar  |  2000 |
+----+------+-------+
2 rows in set (0.01 sec)

Writer> update item set price = 5000 where id = 2;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
ほげさんほげさん

Dirty Read

コミットしてないものが見える

Reader> begin;

Reader> select * from kv;
+----+-------+
| id | value |
+----+-------+
|  0 | zero  |
|  5 | five  |
| 10 | ten   |
+----+-------+

Writer> begin;

Writer> insert into kv (id, value) values (20, 'twenty');

Reader> select * from kv;
+----+--------+
| id | value  |
+----+--------+
|  0 | zero   |
|  5 | five   |
| 10 | ten    |
| 20 | twenty |
+----+--------+
ほげさんほげさん

Fuzzy Read

取得した値が変わる

Reader> begin;

Reader> select * from kv;
+----+-------+
| id | value |
+----+-------+
|  0 | zero  |
|  5 | five  |
| 10 | ten   |
+----+-------+

Writer> begin;

Writer> update kv set value = 'V' where id = 5;

Reader> select * from kv;
+----+-------+
| id | value |
+----+-------+
|  0 | zero  |
|  5 | V     |
| 10 | ten   |
+----+-------+
ほげさんほげさん

Phantom Read

取得した行が変わる

Reader> begin;

Reader> select * from kv;
+----+-------+
| id | value |
+----+-------+
|  0 | zero  |
|  5 | five  |
| 10 | ten   |
+----+-------+

Writer> begin;

Writer> delete from kv where id = 5;

Reader> select * from kv;
+----+-------+
| id | value |
+----+-------+
|  0 | zero  |
| 10 | ten   |
+----+-------+
ほげさんほげさん

Repeatable Read における Fuzzy Read

予想通り発生しない

Reader> select * from kv where id = 5;
+----+-------+
| id | value |
+----+-------+
|  5 | five  |
+----+-------+

Writer> begin;

Writer> update kv set value = 'V' where id = 5;

Reader> select * from kv where id = 5;
+----+-------+
| id | value |
+----+-------+
|  5 | five  |
+----+-------+

Writer> commit;

Reader> select * from kv where id = 5;
+----+-------+
| id | value |
+----+-------+
|  5 | V     |        -- read committed は包含する
+----+-------+
ほげさんほげさん

Repeatable Read における Phantom Read

予想に反して発生しない
なんか InnoDB だと起きないってやつだっけ？

Reader> select * from kv order by id limit 1;
+----+-------+
| id | value |
+----+-------+
|  0 | zero  |
+----+-------+

Writer> begin;

Writer> insert into kv (id, value) values (-1, 'minus one');

Reader> select * from kv order by id limit 1;
+----+-------+
| id | value |
+----+-------+
|  0 | zero  |
+----+-------+