トランザクション分離レベルを理解するために実際に試してみる

「トランザクション分離レベル」って今まで聞いたことはあったんだけど、なんのこっちゃわからんかったし、実用面で運用中に気にすることもなかったし、けど知らないままなのもなんだかなぁ、ってことで改めて調べてみました。

Wikipediaによりますと

トランザクション分離レベル (-ぶんり-)または 分離レベル (英: Isolation) とは、データベース管理システム上での一括処理(トランザクション)が複数同時に行われた場合に、どれほどの一貫性、正確性で実行するかを4段階で定義したものである。隔離レベル 、 独立性レベルとも呼ばれる。トランザクションを定義づけるACID特性のうち,I(Isolation; 分離性, 独立性)に関する概念である。 Wikipedia「トランザクション分離レベル」より

重要なのはトランザクションのACID特性のI(Isolation; 分離性, 独立性)に関する概念であるということ。
分離性・独立性とは「他の処理に影響を与えない」というもので、逆にいえば、

「他の処理に影響を与える度合い」=「トランザクション分離レベル」

ということになりますね。

また、分離レベルには4段階あるということ。
この4段階で他の処理に影響を与えるものと与えないものを理解することが「トランザクション分離レベル」を理解するということなんですな。

更に読み進めて行くと重要なワードが出てきます。「SERIALIZABLE」「REPEATABLE READ」「READ COMMITTED」「READ UNCOMMITTED」の分離レベルの定義はもちろんそうなのですが、「ファントム・リード」「非再現リード」「ダーティ・リード」と呼ばれる現象です。これらの現象がいわゆる「他の処理に影響を与えるもの」です。
以下にそれぞれの現象でどのようなことが起こるのかまとめてみます。

前提条件

使用するDBサーバーはMySQL5.6。前回macに構築したものを使います。
またターミナルを2つ立ち上げそれぞれコンソール起動し、クライアント1・クライアント2とします。

使用するテーブル作成コマンドを以下に記載します。

CREATE TABLE test (c1 INT NOT NULL AUTO_INCREMENT,c2 CHAR(1),PRIMARY KEY(c1)) ENGINE InnoDB;
INSERT INTO test (c2) VALUES('a'),('b'),('c'),('d'),('e');

ダーティ・リード

ダーティ・リードは複数で同じデータを操作していた場合に一方のCOMMIT前のデータが他に表示されてしまうことです。
MySQLはデフォルトでダーティ・リードの発生を許可しない設定になっていますので、まずはダーティ・リードが発生しない状態を確認します。

クライアント1

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO test (c2) VALUES('f');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM test;
+------+------+
| c1   | c2   |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
|    4 | d    |
|    5 | e    |
|    6 | f    |
+------+------+
6 rows in set (0.00 sec)

まずクライアント1でトランザクションを開始し、一行追加しました。まだクライアント1の接続はCOMMITしていない状態です。
この状態でクライアント2から「test」テーブルの内容を確認してみます。

クライアント2

mysql> SELECT * FROM test;
+------+------+
| c1   | c2   |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
|    4 | d    |
|    5 | e    |
+------+------+
5 rows in set (0.00 sec)

これがダーティリードではない状態です。クライアント1で挿入し確認できたデータですが、COMMIT前なのでクライアント2からは確認できません。 クライアント1でCOMMITすればクライアント2で挿入されたデータの確認ができます。

クライアント1

mysql> COMMIT;
Query OK, 0 rows affected (0.01 sec)

クライアント2

mysql> SELECT * FROM test;
+------+------+
| c1   | c2   |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
|    4 | d    |
|    5 | e    |
|    6 | f    |
+------+------+
6 rows in set (0.00 sec)

これをトランザクション分離レベルを設定し、ダーティ・リードが発生する状態にします。
ダーティ・リードを許可する分離レベルは「READ UNCOMMITTED」です。

クライアント1・クライアント2

mysql> SET SESSION tx_isolation="read-uncommitted";

トランザクション分離レベルを「READ UNCOMMITTED」に設定しました。今後すべての接続クライアントに対して分離レベルを設定する場合は”SET GLOBAL”で設定してください。
この状態で先ほどと同じことを試してみます。

クライアント1

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO test (c2) VALUES('g');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM test;
+------+------+
| c1   | c2   |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
|    4 | d    |
|    5 | e    |
|    6 | f    |
|    7 | g    |
+------+------+
7 rows in set (0.00 sec)

クライアント2

mysql> SELECT * FROM test;
+------+------+
| c1   | c2   |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
|    4 | d    |
|    5 | e    |
|    6 | f    |
|    7 | g    |
+------+------+
7 rows in set (0.01 sec)

クライアント1がCOMMITしていないにもかかわらず、クライアント2側でCOMMIT前のデータが確認できてしまいました。これがダーティ・リードな状態です。ダーティ・リードはクライアント2がロールバックしてしまった場合でもクライアント1側でデータが存在したものとして扱われてしまいます。
ダーティ・リードな状態を許可する分離レベルは「READ UNCOMMITTED」のみになります。

非再現リード

非再現リードとはトランザクション内で、他のCOMMITされた行の変更が表示されてしまう現象のことです。ノンリピータブル・リード/ファジー・リードとも言います。
実際に確認してみます。

クライアント1

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM test;
+------+------+
| c1   | c2   |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
|    4 | d    |
|    5 | e    |
|    6 | f    |
|    7 | g    |
+------+------+
7 rows in set (0.00 sec)

クライアント1でトランザクションを開始し、testテーブルの内容を確認しました。まだコミットはしていません。
この状態のままクライアント2で行を変更し、COMMITします。

クライアント2

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE test SET c2='h' WHERE c1=7;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> COMMIT;
Query OK, 0 rows affected (0.01 sec)

クライアント2で行を更新しCOMMITしましたが、クライアント1はまだCOMMITされていません。
クライアント1でtestテーブルの内容を確認します。

クライアント1

mysql> SELECT * FROM test;
+------+------+
| c1   | c2   |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
|    4 | d    |
|    5 | e    |
|    6 | f    |
|    7 | h    |
+------+------+
7 rows in set (0.00 sec)

これが非再現リードと呼ばれる現象です。InnoDBの初期設定では非再現リードは許可されない設定になっているので、通常はこのようなことは起こりません。InnoDBのデフォルトの設定は「REPEATABLE READ」です。

クライアント1・クライアント2

mysql> SET SESSION tx_isolation="repeatable-read";

クライアント1

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM test;
+------+------+
| c1   | c2   |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
|    4 | d    |
|    5 | e    |
|    6 | f    |
|    7 | h    |
+------+------+
7 rows in set (0.00 sec)

クライアント2

mysql> SET SESSION tx_isolation="repeatable-read";
Query OK, 0 rows affected (0.00 sec)

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE test SET c2='g' WHERE c1=7;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

クライアント1

mysql> SELECT * FROM test;
+------+------+
| c1   | c2   |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
|    4 | d    |
|    5 | e    |
|    6 | f    |
|    7 | h    |
+------+------+
7 rows in set (0.00 sec)

mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM test;
+------+------+
| c1   | c2   |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
|    4 | d    |
|    5 | e    |
|    6 | f    |
|    7 | g    |
+------+------+
7 rows in set (0.00 sec)

これが非再現リードを許可しない設定の動きです。トランザクション内では他のスレッドのCOMMITされたUPDATEの情報は反映されません。
非再現リードを許可する分離レベルは「READ UNCOMMITTED」「READ COMMITTED」になります。

ファントム・リード

ファントム・リードとはトランザクション内で他のスレッドがCOMMITした行の追加が反映される現象のことです。非再現リードがUPDATEに対しての現象だったのに対して、ファントム・リードはINSERTでの現象になります。

ファントム・リードを許可する分離レベルは「READ UNCOMMITTED」「READ COMMITTED」「REPEATABLE READ」ですが、InnoDBの場合は「REPEATABLE READ」でもファントム・リードは発生しないようになっているため「READ COMMITTED」に設定して現象を確認します。

クライアント1・クライアント2

mysql> SET SESSION tx_isolation="read-committed";

クライアント1

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM test;
+------+------+
| c1   | c2   |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
|    4 | d    |
|    5 | e    |
|    6 | f    |
|    7 | g    |
+------+------+
7 rows in set (0.00 sec)

クライアント2

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO test (c2) VALUES('i');
Query OK, 1 row affected (0.00 sec)

mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

非再現リードの時と同様にクライアント1でトランザクションを開始し、クライアント2でデータを挿入してCOMMITしています。
この段階でクライアント1はまだコミットしていません。

クライアント1

mysql> SELECT * FROM test;
+------+------+
| c1   | c2   |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
|    4 | d    |
|    5 | e    |
|    6 | f    |
|    7 | g    |
|    8 | i    |
+------+------+
8 rows in set (0.00 sec)

トランザクション内で他のCOMMITされた追加したデータが表示されてしまいました。これがファントム・リードです。
ファントムリードを許可しないようにするには分離レベルを「SERIALIZABLE」にします。「SERIALIZABLE」はダーティ・リード、非再現リード、ファントム・リード全てを許可しません。

クライアント1・クライアント2

mysql> SET SESSION tx_isolation="serializable";

クライアント1

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM test;
+------+------+
| c1   | c2   |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
|    4 | d    |
|    5 | e    |
|    6 | f    |
|    7 | g    |
|    8 | i    |
+------+------+
8 rows in set (0.00 sec)

クライアント2

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO test (c2) VALUES('j');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

クライアント2からデータを挿入しようとしたところエラーとなってしまいました。
「SERIALIZABLE」の場合はクエリによって得られる行の範囲にロックをかけてしまうようです。そもそもクライアント1のトランザクションが完了するまでロックをかけてファントム・リードを防ぐという仕組みみたいです。

一応、InnoDBでファントム・リードが許可されていない検証結果を以下に載せて起きます。

クライアント1・クライアント2

mysql> SET SESSION tx_isolation="repeatable-read";

クライアント1

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM test;
+------+------+
| c1   | c2   |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
|    4 | d    |
|    5 | e    |
|    6 | f    |
|    7 | g    |
|    8 | i    |
+------+------+
8 rows in set (0.00 sec)

クライアント2

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO test (c2) VALUES('j');
Query OK, 1 row affected (0.00 sec)

mysql> COMMIT;
Query OK, 0 rows affected (0.01 sec)

クライアント1

mysql> SELECT * FROM test;
+------+------+
| c1   | c2   |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
|    4 | d    |
|    5 | e    |
|    6 | f    |
|    7 | g    |
|    8 | i    |
+------+------+
8 rows in set (0.00 sec)

mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM test;
+------+------+
| c1   | c2   |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
|    4 | d    |
|    5 | e    |
|    6 | f    |
|    7 | g    |
|    8 | i    |
|    9 | j    |
+------+------+
9 rows in set (0.00 sec)

まとめ

各トランザクション分離レベルと許可する現象をまとめれば以下の様になります。

ダーティ・リード 非再現リード ファントム・リード
SERIALIZABLE
REPEATABLE READ
READ COMMITTED
READ UNCOMMITTED
  • InnoDBのデフォルトは「REPEATABLE READ」
  • InnoDBは「REPEATABLE READ」でもファントム・リードされない
  • SERIALIZABLEはクエリの検索範囲にロックがかかる

コメントを残す