今回は MySQL の InnoDB を使ってトランザクション分離レベル (Transaction Isolation Level) の違いを試してみる。
トランザクション分離レベルは、SQL を実装したシステムの ACID 特性において I (Isolation) に対応する概念となっている。
利用する分離レベルによって、複数のトランザクション間でデータの一貫性に関する振る舞いが変化する。
なお、この概念は ANSI SQL で定義されているもので、MySQL に固有というわけではない。
InnoDB では、次の 4 種類のトランザクション分離レベルがサポートされている。
下にいくほど、より厳格にトランザクションを分離できる一方で、上にいくほど処理のオーバーヘッドは少ない。
言いかえると、トランザクション分離レベルを切り替えることでパフォーマンスと一貫性のバランスを調整できる。
なお、デフォルトでは REPEATABLE READ が用いられる。
- READ UNCOMMITTED
- READ COMMITTED
- REPEATABLE READ
- SERIALIZABLE
トランザクション分離レベルを落とすと、複数のトランザクション間で分離が不十分になる現象が生じる。
古典的な定義において、生じる現象には次のようなものがある 1。
- ダーティーリード (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