MySQL道普請便り

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

この記事を読むのに必要な時間:およそ 5.5 分

第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)

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

著者プロフィール

木村浩一郎(きむらこういちろう)

GMOメディア株式会社 技術推進室所属のWebアプリケーションエンジニア。最近はミドルウェア・インフラ周りのことも少しずつ学習しています。趣味は将棋。好きな戦法は四間飛車。

Twitter:@kk2170

コメント

コメントの記入