MySQL道普請便り

第50回トランザクション分離レベルを試してみる

第47回では、トランザクション分離レベルの変更方法を確認してみました。今回はトランザクション分離レベルを変更することによって、トランザクションの挙動がどのように変わるかを紹介していきたいと思います。

検証環境

第10回 yum, rpmインストールにおけるMySQL 5.6とMySQL 5.7の違いで紹介されたyumリポジトリーを使用したインストールを利用しています。また、MySQLのバージョンは5.7.18を使用しています。

また今回は、CentOS 7.3上で確認を行っており、テスト用のデータベースとして以下のようなSQLを実行してから検証を行っています。

mysql> CREATE DATABASE test;
mysql> use test
mysql> CREATE TABLE user(name varchar(20), point int );
mysql> INSERT INTO user(name, point) VALUE('sato', 0);

トランザクション分離レベルの種類

MySQLでは前回説明したとおり、SERIALIZABLE, REPEATABLE READ, READ COMMITTED, READ UNCOMMITTEDの4種類のトランザクション分離レベルが設定できます。これらの種類によって挙動がどの様に変わるかを紹介していきたいと思います。

SERIALIZABLE

このトランザクション分離レベルは、4つの分離レベルの中で一番整合性を重視した設定になります。名前が示すとおりに、並列に実行すると整合性に関して問題が発生するトランザクション処理を、直列的に実行します。その他のトランザクション分離レベルとは違い、ロックが取られることに注意が必要です。そのため、その他のトランザクション分離レベルに比べてスケーラビリティが下がってしまいます。結果として速度が遅くなってしまうことが多いので、本番環境で厳密性をかなり求める場合を除いて、この設定を利用することはあまりないでしょう。

SERIALIZABLEの挙動

SERIALIZABLEに設定した時のトランザクションの挙動を確認してみましょう。

2つのトランザクションを実行して確認をしていくのですが、通常のMySQLのプロンプトの状態だと判別がしにくいです。以下のようにpromptコマンドを使ってトランザクションを実行するプロンプトを書き換えてわかりやすくしましょう。

mysql> prompt txA> 
PROMPT set to 'txA> '
txA>

別のコンソールから

mysql> prompt txB> 
PROMPT set to 'txB> '
txB>

また、書き換えたpromptの表示をデフォルトに戻したい時は、promptコマンドを引数を付けずに実行しましょう。

txB> prompt
Returning to default PROMPT of mysql>
mysql>

それでは、どのような挙動になるのか確認してみましょう。

txA> SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
txB> SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

まずはじめに両方のコンソールで設定を行います。続けてtxAでトランザクションを開始します。

txA> BEGIN; 
Query OK, 0 rows affected (0.00 sec)

txA> SELECT * from user;
+------+-------+
| name | point |
+------+-------+
| sato |     0 |
+------+-------+
1 row in set (0.00 sec)

一件だけ入っていることがわかります。この状態で、txBで新たにトランザクションを開始してuserテーブルに挿入をしようとすると、どうなるでしょうか。

SERIALIZABLEではロック指定なしのSELECTステートメントも共有ロックを取ります。具体的には、SERIALIZABLESELECTはその他のトランザクション分離レベルでSELECT .. LOCK IN SHARE MODEを指定した場合と同じロックを取ります。そのことを確認してみましょう。

txB> BEGIN;
Query OK, 0 rows affected (0.00 sec)

txB> INSERT INTO user (name, point) VALUE('suzuki', 0);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

結果はtxAのトランザクションのロックが解放されるのを待ってしまうため帰ってきません。ロックの解放待ちの時間はデフォルトでは50秒ですので、50秒ほど待つとエラーが帰ってきます。この待ち時間が長過ぎる場合は、innodb_lock_wait_timeoutの時間を設定することで変更できます。

では、その待ち時間の最中にtxAのトランザクションが終了するとどうなるでしょうか。確認してみましょう。

txB> BEGIN;
txB> INSERT INTO user (name, point) VALUE('suzuki', 0);
# ロック待ちが発生

ロック待ちになったtxBを確認したら、続けてtxAに対してコミットをして、トランザクションを終了させてみましょう。

txA> COMMIT;

この時にtxBを確認してみると、以下のようにロック待ちをしたINSERT文の下にクエリが実行されたログが出力されます。

txB> INSERT INTO user (name, point) VALUE('suzuki', 0); # ロック待ちをしたコマンド
Query OK, 1 row affected (4.26 sec)  # この行が増えている
txB> COMMIT;

コミットした後にSELECTを行い確認してみると、INSERT文で実行された要素が追加されていることがわかります。

txB> SELECT * from user;
+--------+-------+
| name   | point |
+--------+-------+
| sato   |     0 |
| suzuki |     0 |
+--------+-------+
2 rows in set (0.00 sec)

このように、SERIALIZABLEを設定した場合はロック待ちが発生してしまうため、遅くなってしまう場合があります。しかし、この分離レベルを使うとトランザクション毎に整合性を保つことが出来ます。

REPEATABLE READ

このトランザクション分離レベルは、MySQLのデフォルトのトランザクション分離レベルになっています。名前が示すとおり、トランザクション中で一度SELECTを発行したテーブルに関しては、外部のテーブルで変更されたとしても同じ結果が得られます。

また、この分離レベルをInnoDB以外の他のストレージエンジンを使っていると、ファントムリードという不整合が起こる場合があります。たとえば、対象ユーザの数を調べてから付与したい場合などに、最初に対象ユーザを調べてから付与するまでに時間があいていると、別トランザクションで挿入・削除されたユーザの分だけズレてしまうことがあります。この不整合が発生する問題をファントムリードと呼びます。

しかし、MySQLでInnoDBを使っている場合は起こりません。これはネクストキーロックという仕組みで防いでいるのですが、今回は説明しません。

REPEATABLE READの挙動

ここではファントムリードが起こらないことを確認してみましょう。トランザクション分離レベルをREPEATABLE READに変更します。

txA> SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
txB> SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

続けてtxAでトランザクションを開始して、現在のuserの数を確認してみます。先ほどユーザを追加したので結果は2件となっています。

txA> BEGIN;
txA> SELECT * FROM user;
+--------+-------+
| name   | point |
+--------+-------+
| sato   |     0 |
| suzuki |     0 |
+--------+-------+
2 rows in set (0.00 sec)

この状態でtxBでユーザを追加してみましょう。

txB> BEGIN;
txB> INSERT INTO user (name, point) VALUE('tanaka', 0);
Query OK, 1 row affected (0.00 sec)
txB> COMMIT;

txB> SELECT * from user;
+--------+-------+
| name   | point |
+--------+-------+
| sato   |     0 |
| suzuki |     0 |
| tanaka |     0 |
+--------+-------+
3 rows in set (0.00 sec)

ユーザが3件に増えていることがわかります。この状態で、もう一度txAに戻ってカウントしてみましょう。

txA> SELECT * FROM user;
+--------+-------+
| name   | point |
+--------+-------+
| sato   |     0 |
| suzuki |     0 |
+--------+-------+
2 rows in set (0.00 sec)


txA> COMMIT;

2件のままになっていることがわかります。最後にコミットをしてトランザクションを終了しましょう。

txA> SELECT * from user;
+--------+-------+
| name   | point |
+--------+-------+
| sato   |     0 |
| suzuki |     0 |
| tanaka |     0 |
+--------+-------+
3 rows in set (0.00 sec)

トランザクションを終了すると件数が増えていることがわかります。このようにファントムリードが発生しないことがわかります。

READ COMMITTED

このトランザクションは、名前の通り他のトランザクションでコミットされた値が読めるという挙動になります。この設定にはデータの不整合が起こる場合があります。それはファジーリードと先ほどREPEATABLE READで説明したファントムリードになります。

ファジーリードはファントムリードとよく似ています。たとえば、対象ユーザの数を調べてから付与したい場合などに、最初に対象ユーザを調べてから付与するまでに時間があいていると、別トランザクションで更新されたユーザの分だけズレてしまうことがあります。このように、トランザクション中に同じ読み取りをした際に結果が異なる問題をファジーリードと呼びます。

READ COMMITTEDの挙動

ここではファントムリードとファジーリードが起こることを確認してみましょう。以下のようにトランザクション分離レベルを変更します。

txA> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
txB> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

ファントムリードから確認してみましょう。txAでトランザクションを開始します。

txA> BEGIN;
Query OK, 0 rows affected (0.00 sec)

txA> SELECT * FROM user;
+--------+-------+
| name   | point |
+--------+-------+
| sato   |     0 |
| suzuki |     0 |
| tanaka |     0 |
+--------+-------+
3 rows in set (0.00 sec)

userテーブルに3件のデータが入ってることがわかります。ここでtrBでデータの挿入を行います。

txB> INSERT INTO user (name, point) VALUE('takahashi', 0);
Query OK, 1 row affected (0.00 sec)

この時にtrAに戻り、もう一度結果を見てみましょう。

txA> SELECT COUNT(*) FROM user;
+-----------+-------+
| name      | point |
+-----------+-------+
| sato      |     0 |
| suzuki    |     0 |
| tanaka    |     0 |
| takahashi |     0 |
+-----------+-------+
4 rows in set (0.00 sec)

こちらではユーザが4件となっていて、同じトランザクションの中で結果が違う事からファントムリードが発生していることがわかります。

続いてファジーリードを試していきます。txAで一旦コミットをしてもう一度トランザクションを作成し、satoさんのpointの値を確認します。

txA> COMMIT;
txA> BEGIN;

txA> SELECT point FROM user WHERE name = 'sato';
+-------+
| point |
+-------+
|     0 |
+-------+
1 row in set (0.00 sec)

現在satoさんのpointは0であることがわかりました。ここで、txBで以下のようにpointを更新してみます。satoさんに対して100ポイントを付与しています。

txB> UPDATE user SET point=100 WHERE name='sato';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

ここでtxAに戻り確認してみましょう。

txA> SELECT point FROM user WHERE name = 'sato';
+-------+
| point |
+-------+
|   100 |
+-------+
1 row in set (0.00 sec)

txA> COMMIT;

txAのトランザクションの中でも更新された値が入っていることから、ファジーリードが発生していることがわかります。最後にコミットをしてトランザクションを終了しましょう。

最後に、REPEATABLE READで同じことを行った場合にどうなるかを簡単に確認してみましょう。

txA> SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
txB> SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
txA> BEGIN;
txA> SELECT point FROM user WHERE name = 'sato';
+-------+
| point |
+-------+
|     0 |
+-------+
1 row in set (0.00 sec)

txB> BEGIN;
txB> UPDATE user SET point=100 WHERE name='sato';
txB> COMMIT;

txA> SELECT point FROM user WHERE name = 'sato';
+-------+
| point |
+-------+
|     0 |
+-------+
1 row in set (0.00 sec)
txA> COMMIT;
txA> SELECT point FROM user WHERE name = 'sato';
+-------+
| point |
+-------+
|   100 |
+-------+
1 row in set (0.00 sec)

上記のように、REPEATABLE READの場合はトランザクション中ではSELECTステートメントが同じ値を返していることがわかります。

READ UNCOMMITTED

このトランザクション分離レベルは、名前の通り他のトランザクションでコミットされる前の変更が読めるという挙動になります。そのため、トランザクションがロールバックされた場合に、データに不整合が起こってしまう可能性が高いです。その代わりに、トランザクションの並列度が他の分離レベルに対して高くなるので、高速で動作します。

ただ、やっぱりコミットされる前の値が読めてしまうのは、データの整合性を保つ上で問題が多いため本番環境ではあまり使われることはありません。

また、コミットされる前の値が読めてしまう問題をダーティリードと呼びます。

READ UNCOMMITTEDの挙動

ここではダーティリードについて確認してみましょう。以下のようにトランザクション分離レベルを設定します。

txA> SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
txB> SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

続いて、txAでトランザクションを作成します。そして今回は、suzukiさんのポイントを確認してみます。

txA> BEGIN;

txA> SELECT point FROM user WHERE name = 'suzuki';
+-------+
| point |
+-------+
|     0 |
+-------+
1 row in set (0.00 sec)

現在0ポイントであることがわかりました。そこでtxBでトランザクションを作成し、その中でsuzukiさんのポイントを2000に更新をしてみます。

txB> BEGIN;
txB> UPDATE user SET point=2000 WHERE name='suzuki';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

まだコミットをしていないため更新は確定していませんが、この状態でtxAに戻り、もう一度suzukiさんのポイントを確認してみましょう。

txA> SELECT point FROM user WHERE name = 'suzuki';
+-------+
| point |
+-------+
|  2000 |
+-------+
1 row in set (0.00 sec)

上記のようにtxAの中で確定されていない変更も読めてしまうため、ダーティリードが起こっていることがわかります。

各トランザクション分離レベルのまとめ

最後にMySQLのInnoDBで、各トランザクション分離レベルでどのような問題が発生するかを簡単に以下の表にまとめてみました。

ダーティリードファジーリードファントムリード
SERIALIZABLE発生しない発生しない発生しない
REPEATABLE READ発生しない発生しない発生しない
READ COMMITTED発生しない発生する発生する
READ UNCOMMITTED発生する発生する発生する

まとめ

今回はトランザクション分離レベルの挙動について紹介しました。トランザクション分離レベルでは、絶対にこれが正しいという設定はありません。プロジェクトの種類やどこまで副作用を許容できるかというによって、適切なトランザクション分離レベルは変わってくるため、それぞれがどのような挙動になるか試してみてはいかがでしょうか。

おすすめ記事

記事・ニュース一覧