第47回では、トランザクション分離レベルの変更方法を確認してみました。今回はトランザクション分離レベルを変更することによって、トランザクションの挙動がどのように変わるかを紹介していきたいと思います。
検証環境
第10回 yum, rpmインストールにおけるMySQL 5.6とMySQL 5.7の違いで紹介されたyumリポジトリーを使用したインストールを利用しています。また、MySQLのバージョンは5.7.18を使用しています。
また今回は、CentOS 7.3上で確認を行っており、テスト用のデータベースとして以下のようなSQLを実行してから検証を行っています。
トランザクション分離レベルの種類
MySQLでは前回説明したとおり、SERIALIZABLE
, REPEATABLE READ
, READ COMMITTED
, READ UNCOMMITTED
の4種類のトランザクション分離レベルが設定できます。これらの種類によって挙動がどの様に変わるかを紹介していきたいと思います。
SERIALIZABLE
このトランザクション分離レベルは、4つの分離レベルの中で一番整合性を重視した設定になります。名前が示すとおりに、並列に実行すると整合性に関して問題が発生するトランザクション処理を、直列的に実行します。その他のトランザクション分離レベルとは違い、ロックが取られることに注意が必要です。そのため、その他のトランザクション分離レベルに比べてスケーラビリティが下がってしまいます。結果として速度が遅くなってしまうことが多いので、本番環境で厳密性をかなり求める場合を除いて、この設定を利用することはあまりないでしょう。
SERIALIZABLEの挙動
SERIALIZABLE
に設定した時のトランザクションの挙動を確認してみましょう。
2つのトランザクションを実行して確認をしていくのですが、通常のMySQLのプロンプトの状態だと判別がしにくいです。以下のようにpromptコマンドを使ってトランザクションを実行するプロンプトを書き換えてわかりやすくしましょう。
別のコンソールから
また、書き換えたpromptの表示をデフォルトに戻したい時は、prompt
コマンドを引数を付けずに実行しましょう。
それでは、どのような挙動になるのか確認してみましょう。
まずはじめに両方のコンソールで設定を行います。続けてtxAでトランザクションを開始します。
一件だけ入っていることがわかります。この状態で、txBで新たにトランザクションを開始してuserテーブルに挿入をしようとすると、どうなるでしょうか。
SERIALIZABLE
ではロック指定なしのSELECT
ステートメントも共有ロックを取ります。具体的には、SERIALIZABLE
のSELECT
はその他のトランザクション分離レベルでSELECT .. LOCK IN SHARE MODE
を指定した場合と同じロックを取ります。そのことを確認してみましょう。
結果はtxAのトランザクションのロックが解放されるのを待ってしまうため帰ってきません。ロックの解放待ちの時間はデフォルトでは50秒ですので、50秒ほど待つとエラーが帰ってきます。この待ち時間が長過ぎる場合は、innodb_lock_wait_timeout
の時間を設定することで変更できます。
では、その待ち時間の最中にtxAのトランザクションが終了するとどうなるでしょうか。確認してみましょう。
ロック待ちになったtxBを確認したら、続けてtxAに対してコミットをして、トランザクションを終了させてみましょう。
この時にtxBを確認してみると、以下のようにロック待ちをしたINSERT文の下にクエリが実行されたログが出力されます。
コミットした後にSELECTを行い確認してみると、INSERT文で実行された要素が追加されていることがわかります。
このように、SERIALIZABLE
を設定した場合はロック待ちが発生してしまうため、遅くなってしまう場合があります。しかし、この分離レベルを使うとトランザクション毎に整合性を保つことが出来ます。
REPEATABLE READ
このトランザクション分離レベルは、MySQLのデフォルトのトランザクション分離レベルになっています。名前が示すとおり、トランザクション中で一度SELECT
を発行したテーブルに関しては、外部のテーブルで変更されたとしても同じ結果が得られます。
また、この分離レベルをInnoDB以外の他のストレージエンジンを使っていると、ファントムリードという不整合が起こる場合があります。たとえば、対象ユーザの数を調べてから付与したい場合などに、最初に対象ユーザを調べてから付与するまでに時間があいていると、別トランザクションで挿入・削除されたユーザの分だけズレてしまうことがあります。この不整合が発生する問題をファントムリードと呼びます。
しかし、MySQLでInnoDBを使っている場合は起こりません。これはネクストキーロックという仕組みで防いでいるのですが、今回は説明しません。
REPEATABLE READの挙動
ここではファントムリードが起こらないことを確認してみましょう。トランザクション分離レベルをREPEATABLE READ
に変更します。
続けてtxAでトランザクションを開始して、現在のuserの数を確認してみます。先ほどユーザを追加したので結果は2件となっています。
この状態でtxBでユーザを追加してみましょう。
ユーザが3件に増えていることがわかります。この状態で、もう一度txAに戻ってカウントしてみましょう。
2件のままになっていることがわかります。最後にコミットをしてトランザクションを終了しましょう。
トランザクションを終了すると件数が増えていることがわかります。このようにファントムリードが発生しないことがわかります。
READ COMMITTED
このトランザクションは、名前の通り他のトランザクションでコミットされた値が読めるという挙動になります。この設定にはデータの不整合が起こる場合があります。それはファジーリードと先ほどREPEATABLE READ
で説明したファントムリードになります。
ファジーリードはファントムリードとよく似ています。たとえば、対象ユーザの数を調べてから付与したい場合などに、最初に対象ユーザを調べてから付与するまでに時間があいていると、別トランザクションで更新されたユーザの分だけズレてしまうことがあります。このように、トランザクション中に同じ読み取りをした際に結果が異なる問題をファジーリードと呼びます。
READ COMMITTEDの挙動
ここではファントムリードとファジーリードが起こることを確認してみましょう。以下のようにトランザクション分離レベルを変更します。
ファントムリードから確認してみましょう。txAでトランザクションを開始します。
userテーブルに3件のデータが入ってることがわかります。ここでtrBでデータの挿入を行います。
この時にtrAに戻り、もう一度結果を見てみましょう。
こちらではユーザが4件となっていて、同じトランザクションの中で結果が違う事からファントムリードが発生していることがわかります。
続いてファジーリードを試していきます。txAで一旦コミットをしてもう一度トランザクションを作成し、satoさんのpointの値を確認します。
現在satoさんのpointは0であることがわかりました。ここで、txBで以下のようにpointを更新してみます。satoさんに対して100ポイントを付与しています。
ここでtxAに戻り確認してみましょう。
txAのトランザクションの中でも更新された値が入っていることから、ファジーリードが発生していることがわかります。最後にコミットをしてトランザクションを終了しましょう。
最後に、REPEATABLE READ
で同じことを行った場合にどうなるかを簡単に確認してみましょう。
上記のように、REPEATABLE READ
の場合はトランザクション中ではSELECTステートメントが同じ値を返していることがわかります。
READ UNCOMMITTED
このトランザクション分離レベルは、名前の通り他のトランザクションでコミットされる前の変更が読めるという挙動になります。そのため、トランザクションがロールバックされた場合に、データに不整合が起こってしまう可能性が高いです。その代わりに、トランザクションの並列度が他の分離レベルに対して高くなるので、高速で動作します。
ただ、やっぱりコミットされる前の値が読めてしまうのは、データの整合性を保つ上で問題が多いため本番環境ではあまり使われることはありません。
また、コミットされる前の値が読めてしまう問題をダーティリードと呼びます。
READ UNCOMMITTEDの挙動
ここではダーティリードについて確認してみましょう。以下のようにトランザクション分離レベルを設定します。
続いて、txAでトランザクションを作成します。そして今回は、suzukiさんのポイントを確認してみます。
現在0ポイントであることがわかりました。そこでtxBでトランザクションを作成し、その中でsuzukiさんのポイントを2000に更新をしてみます。
まだコミットをしていないため更新は確定していませんが、この状態でtxAに戻り、もう一度suzukiさんのポイントを確認してみましょう。
上記のようにtxAの中で確定されていない変更も読めてしまうため、ダーティリードが起こっていることがわかります。
各トランザクション分離レベルのまとめ
最後にMySQLのInnoDBで、各トランザクション分離レベルでどのような問題が発生するかを簡単に以下の表にまとめてみました。
| ダーティリード | ファジーリード | ファントムリード |
SERIALIZABLE | 発生しない | 発生しない | 発生しない |
REPEATABLE READ | 発生しない | 発生しない | 発生しない |
READ COMMITTED | 発生しない | 発生する | 発生する |
READ UNCOMMITTED | 発生する | 発生する | 発生する |
まとめ
今回はトランザクション分離レベルの挙動について紹介しました。トランザクション分離レベルでは、絶対にこれが正しいという設定はありません。プロジェクトの種類やどこまで副作用を許容できるかというによって、適切なトランザクション分離レベルは変わってくるため、それぞれがどのような挙動になるか試してみてはいかがでしょうか。