onemuri.space

# 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 を使って手を動かしながら理解していくことにしましょう。

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

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

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

a1

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

b1

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

a2

aで select する。
transction a2

b2

b で select する。
transction b2

a3

a で update 文を実行する。
transction a3

b3-1

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

b3-2

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

a4

a の結果を commit する。
transction a4

b4

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

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

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

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

まとめ

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

参考書籍

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

自己紹介用画像

Riki Akagi

2019年からDeNAで働いています。GCP(CloudSQL・GAE・Cloud Function etc)とGoでAPI開発に勤んでいます。睡眠やエンジニアリングに関することに興味を持って過ごしているのでその情報を皆さんに共有していけたらなと思っています。

自己紹介の詳細