前のページ
Featured image of post mysql で repeatable read の挙動確認 ~ファジーリード編~

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
transction a1

b1

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

transction b1
transction b1

a2

aで select する。

transction a2
transction a2

b2

b で select する。

transction b2
transction b2

a3

a で update 文を実行する。

transction a3
transction a3

b3-1

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

transction b3-1
transction b3-1

b3-2

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

transction b3-2
transction b3-2

a4

a の結果を commit する。

transction a4
transction a4

b4

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

transction b4
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) (日本語)

このページでは、Google Analyticsを利用しています。
Built with Hugo
Theme Stack designed by Jimmy