MySQL道普請便り

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

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

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発生する発生する発生する

まとめ

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

著者プロフィール

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

株式会社オプティム 技術統括本部のエンジニア。最近はミドルウェア・インフラ周りのことも少しずつ学習しています。趣味は将棋。好きな戦法は四間飛車。

Twitter:@kk2170