mysql で repeatable read の挙動確認 ~ファジーリード編~

RDBの分離レベルの話でよく出てくる分離レベルの表を以下に示します。用語は知っているけど、実際に手を動かしたことがなかったので一つずつ理解していこうと思います。

今回は特にファジーリードに着目して調べてみました。

	ダーティリード	ファジーリード	ファントムリード	ロストアップデート
READ UNCOMMITTED	発生する	発生する	発生する	発生する
READ COMMITTED	-	発生する	発生する	発生する
REPEATABLE READ	-	-	発生する	発生する
SERIALIZABLE	-	-	-	-

ファジーリードとは？

トランザクションAとは異なるトランザクションBにて更新した値がトランザクションAで更新した値が見えることです。

例えば、以下のようなテーブルがあるとします。

+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| id        | int          | NO   | PRI | NULL    | auto_increment |
| parent_id | int          | NO   | PRI | NULL    |                |
| age       | int          | NO   |     | NULL    |                |
| name      | varchar(255) | NO   | MUL | NULL    |                |
+-----------+--------------+------+-----+---------+----------------+

以下のデータがあるとします。

id=1, parent_id=1, age=10, name='ten'

この時、ファジーリードは以下のような場合に発生します。

トランザクションA: id=1 のデータを age=30 として更新する
トランザクションA: id=1 のデータを select する
トランザクションA: 結果=> `id=1, parent_id=1, age=30, name='ten'`

トランザクションB: id=1 のデータを select する
トランザクションB: 結果=> `id=1, parent_id=1, age=30, name='ten'`
=> トランザクションA が commit していないのに更新された値が見える

逆にファジーリードが発生しない時は以下のようになります。

トランザクションA: id=1 のデータを age=30 として更新する
トランザクションA: id=1 のデータを select する
トランザクションA: 結果=> `id=1, parent_id=1, age=30, name='ten'`

トランザクションB: id=1 のデータを select する
トランザクションB: 結果=> `id=1, parent_id=1, age=10, name='ten'`
=> トランザクションA が commit していないのでトランザクションBがトランザクションを開始したときの値を取得します

トランザクションA: commit;

トランザクションB: 結果=> `id=1, parent_id=1, age=30, name='ten'`
=> トランザクションA が commit したので更新された値が見える

RDBの分離レベルのちがい

無償利用できるRDBの mysql と postgresql ではデフォルトの分離レベルが異なります。

postgresql: READ COMMITTED
mysql: REPEATABLE READ

postgresql ではファジーリードが発生して、 mysql ではファジーリードが発生しません。(デフォルトの設定の場合です)

サンプルコード

では、せっかくなので mysql を使って手を動かしながら理解していくことにしましょう。

環境
- DB: mysql:8.0.19
- 参照: https://github.com/mergitto/doker-dummydata-sample
  - 以前作ったダミーデータ作成用のリポジトリです。使いたい人は使ってみてください。

テーブルは先ほど説明に用いた child テーブルを使うことにします。

mysql を立てたら、異なるターミナルでログインして進めます。

今回はトランザクションAを a、トランザクションBを b、として表記しています。

トランザクションを貼ります。

トランザクションを貼ります。

aで select する。

b で select する。

a で update 文を実行する。

b3-1

b で update 文が実行されたデータを select してみる。と、更新される前の値が取得できる。(ファジーリードが発生していない!!)

b3-2

b で update 文が実行されたデータを select ~ for update してみる。と、データの読み取りがブロックされ、a の commit を待ちます。 (行レベルでロックを実行する)

a の結果を commit する。

b3-2 ブロックされていた select 文が実行され、更新された後の値が取得できる。

本ブログで検証したかったファジーリードについては、 b3-1 で確認できました。 mysql ではデフォルトが REPEATABLE READ なのでファジーリードが発生していませんでしたね。

ついでに、悲観ロック (RDB ではよく SELECT ~ FOR UPDATE がそれにあたる)の挙動も確認しました。

mysql では REPEATABLE READ の分離レベルのため、ファジーリードが発生しません。なので整合性を保つために悲観ロックを使うことがしばしばあります。ただ、デッドロックを引き起こす可能性や、ロックを解放せずにいると占有状態が続いて他の人が使えなくなることが考えられるので気をつけなければいけません。

まとめ

mysql はデフォルトで REPEATABLE READ なので、異なるトランザクションの更新を select する処理がある場合には整合性が取れるかどうかに気をつけましょう
select ~ for udpate は悲観ロックで整合性を保つために使えますが、資源をロック（占有）したままにならないように気をつけましょう

参考書籍

失敗から学ぶRDBの正しい歩き方 (Software Design plus) (日本語)