MySQL の InnoDB でトランザクション分離レベルの違いを試す

今回は MySQL の InnoDB を使ってトランザクション分離レベル (Transaction Isolation Level) の違いを試してみる。 トランザクション分離レベルは、SQL を実装したシステムの ACID 特性において I (Isolation) に対応する概念となっている。 利用する分離レベルによって、複数のトランザクション間でデータの一貫性に関する振る舞いが変化する。 なお、この概念は ANSI SQL で定義されているもので、MySQL に固有というわけではない。

InnoDB では、次の 4 種類のトランザクション分離レベルがサポートされている。 下にいくほど、より厳格にトランザクションを分離できる一方で、上にいくほど処理のオーバーヘッドは少ない。 言いかえると、トランザクション分離レベルを切り替えることでパフォーマンスと一貫性のバランスを調整できる。 なお、デフォルトでは REPEATABLE READ が用いられる。

• READ UNCOMMITTED
• READ COMMITTED
• REPEATABLE READ
• SERIALIZABLE

トランザクション分離レベルを落とすと、複数のトランザクション間で分離が不十分になる現象が生じる。 古典的な定義において、生じる現象には次のようなものがある ¹。

• ダーティーリード (Dirty Read)
• ノンリピータブルリード (Non-repeatable Read / Fuzzy Read)
• ファントムリード (Phantom Read)

トランザクション分離レベルと現象には、次のような関係性がある。 表で「X」になっている項目は、起こる可能性があることを示す。 ただし、MySQL の InnoDB では、例外的に REPEATABLE READ でもファントムリードが生じない。

	ダーティーリード	ノンリピータブルリード	ファントムリード
READ UNCOMMITTED	X	X	X
READ COMMITTED	-	X	X
REPEATABLE READ	-	-	X 1
SERIALIZABLE	-	-	-

今回、検証に使った環境は次のとおり。

$ sw_vers
ProductName:    Mac OS X
ProductVersion: 10.14.6
BuildVersion:   18G4032
$ mysql --version
mysql  Ver 8.0.19 for osx10.14 on x86_64 (Homebrew)

下準備

まずは Homebrew を使って MySQL をインストールしておく。

$ brew install mysql

インストールしたら、次に MySQL のサービスを開始する。

$ brew services start mysql
$ brew services list | grep mysql   
mysql   started amedama /Users/amedama/Library/LaunchAgents/homebrew.mxcl.mysql.plist

MySQL クライアントを起動して、MySQL サーバにログインする。

$ mysql -u root

ログインできたら、サンプルとして使うデータベースを用意する。

mysql> CREATE DATABASE example;
Query OK, 1 row affected (0.01 sec)

mysql> USE example;
Database changed

続いて、サンプルとして使うテーブルを用意する。

mysql> CREATE TABLE users (
    ->     id INTEGER PRIMARY KEY,
    ->     name VARCHAR(32) NOT NULL
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> DESC users;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(32) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

テーブルにレコードを 1 つ追加しておく。

mysql> INSERT INTO users VALUES (1, "Alice");
Query OK, 1 row affected (0.01 sec)

データベースエンジンを確認する

続いて、テーブルで利用されているデータベースエンジンが InnoDB になっていることを確認する。

mysql> SELECT
    ->     TABLE_NAME,
    ->     ENGINE
    -> FROM
    ->     information_schema.TABLES
    -> WHERE
    ->     TABLE_SCHEMA = "example";
+------------+--------+
| TABLE_NAME | ENGINE |
+------------+--------+
| users      | InnoDB |
+------------+--------+
1 row in set (0.00 sec)

もし InnoDB でなければデータベースエンジンを変更しておく。

mysql> ALTER TABLE users ENGINE = "InnoDB";
Query OK, 0 rows affected (0.21 sec)
Records: 0  Duplicates: 0  Warnings: 0

なお、テーブルを作る段階でエンジンを指定することもできる。

トランザクション分離レベルの変更について

トランザクション分離レベルはグローバル変数とセッション変数で管理されている。 一時的に変更するときはセッション変数を変更すれば良い。

mysql> SELECT
    ->     @@global.transaction_isolation,
    ->     @@session.transaction_isolation;
+--------------------------------+---------------------------------+
| @@global.transaction_isolation | @@session.transaction_isolation |
+--------------------------------+---------------------------------+
| REPEATABLE-READ                | REPEATABLE-READ                 |
+--------------------------------+---------------------------------+
1 row in set (0.00 sec)

ここではセッション変数を使って変更する。 たとえば、READ UNCOMMITTED に変更するときは次のようにする。

mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT
    ->     @@global.transaction_isolation,
    ->     @@session.transaction_isolation;
+--------------------------------+---------------------------------+
| @@global.transaction_isolation | @@session.transaction_isolation |
+--------------------------------+---------------------------------+
| REPEATABLE-READ                | READ-UNCOMMITTED                |
+--------------------------------+---------------------------------+
1 row in set (0.00 sec)

プロンプトを 2 つ用意する

ここからは複数のトランザクションを扱うので、ターミナルを 2 つ用意してどちらも MySQL サーバにログインしておく。

それぞれのプロンプトを区別するために、次のようにして表示を切りかえる。 ひとつ目は "mysql1" という名前にする。

mysql> PROMPT mysql1> 
PROMPT set to 'mysql1> '

もうひとつは "mysql2" にしておく。

mysql> PROMPT mysql2> 
PROMPT set to 'mysql2> '

ダーティーリード

前置きが長くなったけど、ここから実際の検証に入る。 ダーティーリードを一言でいうと、あるトランザクションでコミットしていない変更が、他のトランザクションから見えてしまうというもの。

あらかじめ、両方のプロンプトのトランザクション分離レベルを READ UNCOMMITTED に変更しておく。

mysql1> SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
Query OK, 0 rows affected (0.00 sec)

mysql2> SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
Query OK, 0 rows affected (0.00 sec)

両方のプロンプトでトランザクションを開始する。

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

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

片方のトランザクションで、既存のレコードのカラムを変更してみよう。

mysql1> UPDATE
    ->     users
    -> SET
    ->     name = 'Bob'
    -> WHERE
    ->     id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

そして、もう一方のトランザクションから読み取ってみる。 すると、先ほど "mysql1" でレコードに加えた変更が "mysql2" から見えてしまっている。 この現象をダーティーリードという。

mysql2> SELECT
    ->     *
    -> FROM
    ->     users;
+----+------+
| id | name |
+----+------+
|  1 | Bob  |
+----+------+
1 row in set (0.00 sec)

振る舞いを確認できたら、両方のトランザクションをロールバックしておく。

mysql1> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)

mysql2> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)

ノンリピータブルリード

続いてはノンリピータブルリード、もしくはファジーリードと呼ばれる現象について。 この現象を一言で表すと、あるトランザクションでコミットした変更が、他のトランザクションから見えてしまうというもの。

はじめに、プロンプトのトランザクション分離レベルを READ COMMITTED に変更しておく。

mysql1> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK, 0 rows affected (0.00 sec)

mysql2> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK, 0 rows affected (0.00 sec)

そして、トランザクションを開始する。

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

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

片方のトランザクションで、先ほどと同じようにレコードに変更を加えてみよう。

mysql1> UPDATE
    ->     users
    -> SET
    ->     name = 'Carol'
    -> WHERE
    ->     id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

この時点では、変更はコミットされていない。 もう一方のトランザクションから変更は見えていないので、今回はダーティーリードは生じていない。

mysql2> SELECT
    ->     *
    -> FROM
    ->     users;
+----+-------+
| id | name  |
+----+-------+
|  1 | Alice |
+----+-------+
1 row in set (0.00 sec)

それでは、トランザクションをコミットしてみよう。

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

すると、もう一方のトランザクションから変更が見えてしまった。 この現象をノンリピータブルリード、またはファジーリードという。

mysql2> SELECT
    ->     *
    -> FROM
    ->     users;
+----+-------+
| id | name  |
+----+-------+
|  1 | Carol |
+----+-------+
1 row in set (0.00 sec)

コミットしていない方のトランザクションはロールバックしておこう。

mysql2> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)

ファントムリード

続いてはファントムリードについて。 この現象を一言で表すと、あるトランザクションでコミットしたレコードの追加や削除が、別のトランザクションから見えてしまうというもの。

なお、ファントムリードは、本来であればトランザクション分離レベルが REPEATABLE READ 以下のときに生じる。 しかし、MySQL の InnoDB では REPEATABLE READ でも例外的にファントムリードが生じない。 そのため、この検証は先ほどに引き続き READ COMMITTED を使って行う。

はじめに、トランザクションを開始する。

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

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

そして、片方のトランザクションでレコードを削除する。

mysql1> DELETE
    -> FROM
    ->     users
    -> WHERE
    ->     id = 1;
Query OK, 1 row affected (0.00 sec)

トランザクションはコミットされていない。 この時点では、もう一方のトランザクションからレコードの削除は見えていない。

mysql2> SELECT
    ->     *
    -> FROM
    ->     users;
+----+-------+
| id | name  |
+----+-------+
|  1 | Carol |
+----+-------+
1 row in set (0.00 sec)

それでは、トランザクションをコミットしてみよう。

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

すると、もう一方のトランザクションからレコードが見えなくなった。 つまり、あるトランザクションでレコードを削除した内容が、別のトランザクションから見えてしまっている。

mysql2> SELECT
    ->     *
    -> FROM
    ->     users;
Empty set (0.00 sec)

動作が確認できたらコミットしていないトランザクションをロールバックしておこう。

mysql2> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)

REPEATABLE READ のときの挙動を確認しておく

念のため、トランザクション分離レベルを REPEATABLE READ にしたときの振る舞いも確認しておこう。

両方のプロンプトのトランザクション分離レベルを REPEATABLE READ に変更する。

mysql1> SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Query OK, 0 rows affected (0.00 sec)

mysql2> SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Query OK, 0 rows affected (0.00 sec)

サンプルとなるレコードをあらためて追加しておく。

mysql1> INSERT INTO users VALUES (1, "Alice");
Query OK, 1 row affected (0.00 sec)

ダーティーリードが生じないことを確認する

両方のプロンプトでトランザクションを開始する。

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

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

一方のプロンプトからレコードのカラムに変更を加える。

mysql1> UPDATE
    ->     users
    -> SET
    ->     name = 'Bob'
    -> WHERE
    ->     id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

この時点でレコードを確認してもカラムは変更されていない。 つまり、ダーティーリードが生じていないことがわかる。

mysql2> SELECT
    ->     *
    -> FROM
    ->     users;
+----+-------+
| id | name  |
+----+-------+
|  1 | Alice |
+----+-------+
1 row in set (0.00 sec)

ノンリピータブルリードが生じないことを確認する

先ほどの状況から、変更を加えたトランザクションをコミットする。

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

この状態でもう一方のトランザクションから確認してもレコードのカラムは変更されていない。 つまり、ノンリピータブルリードが生じていないことがわかる。

mysql2> SELECT
    ->     *
    -> FROM
    ->     users;
+----+-------+
| id | name  |
+----+-------+
|  1 | Alice |
+----+-------+
1 row in set (0.00 sec)

ファントムリードが生じないことを確認する

トランザクションをあらためて開始した上でレコードを削除してコミットする。

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

mysql1> DELETE
    -> FROM
    ->     users
    -> WHERE
    ->     id = 1;
Query OK, 1 row affected (0.00 sec)

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

この状況でもう一方のトランザクションから確認してもレコードは存在しているように見える。 つまり、ファントムリードが生じていないことがわかる。

mysql2> SELECT
    ->     *
    -> FROM
    ->     users;
+----+-------+
| id | name  |
+----+-------+
|  1 | Alice |
+----+-------+
1 row in set (0.00 sec)

参考

https://www.microsoft.com/en-us/research/wp-content/uploads/2016/02/tr-95-51.pdfwww.microsoft.com

dev.mysql.com

Linuxで動かしながら学ぶTCP/IPネットワーク入門

---

1. ただし、ANSI SQL における定義の曖昧さから、厳密にはより様々な現象が生じることが知られている。↩

2. MySQL の InnoDB では生じない。↩