トランザクション分離レベル

March 08, 2020

トランザクション分離レベルとは

トランザクション分離レベルとは、トランザクション処理中に、ほかのユーザーによる操作がどの程度見えるのかを定めるもの。
分離レベルが低いほど、ほかのユーザーの処理を待たずにトランザクションを実行できるようになり、パフォーマンスが向上する。一方、他のユーザーに処理中のデータが見えてしまい、誤動作の原因となることがある。

READ UNCOMMITTED
一番低い分離レベルであり、SELECT 文を実行する際、まだコミットされていないデータを読み込む可能性がある (ダーティリード) 。
正しい結果が出るとは言えないため、概算レポートをだしたいなど、データの正確性を犠牲にしてでもパフォーマンスを向上したいときだけに使われる。
MySQL ではそこまでパフォーマンスが向上しないため、使われることはほとんどない。
READ COMMITTED
ダーティリードを解決したもの。コミットされたデータしか読み取らない。MySQL 以外の多くのデータベースではこれがデフォルトとなる。
あくまでもコミットされたデータしか読み取らないというだけなので、レコードを参照したあとには他のユーザーが書き換えれば、その書き換えた内容はすぐに反映される。
つまり、同じ SELECT 文を実行しても、ほかのユーザーの処理が割り込むことによって結果が変わる可能性がある (ノンリピータブルリード/ファジーリード)。
REPEATABLE READ
MySQL のデフォルトのトランザクション分離レベル。ノンリピータブルリードの問題を解決したもの。
トランザクションを開始する際に、現在のテーブルの状態をそのまま取り出したスナップショットを作ることで、トランザクションの処理中にほかのユーザーがそのトランザクションに対して行った変更の影響を受けないようにしたもの。
ただし、トランザクションの処理中に他のユーザーが追加したレコードが現れることはある (ファントムリード)。
InnoDB/XtraDB では MVCC によりファントムリードを防止しているため、問題とはならない。
SERIALIZABLE
トランザクションを完全に分離して処理する。
もっとも高い分離レベルだが、読み込んだ全てのレコードに行ロックをかけるため、ロック競合が多発し、パフォーマンスが大きく低下する可能性があり、あまり使われない。

トランザクション分離レベルの確認

以下のようにグローバル* セッションレベルにおけるトランザクション分離レベルが確認可能。

mysql> SELECT @@GLOBAL.tx_isolation, @@tx_isolation;  
+-----------------------+-----------------+  
| @@GLOBAL.tx_isolation | @@tx_isolation  |  
+-----------------------+-----------------+  
| REPEATABLE-READ       | REPEATABLE-READ |  
+-----------------------+-----------------+  
1 row in set, 2 warnings (0.00 sec)

分離レベル毎における影響の確認

それぞれの分離レベルにおける影響を確認するために以下のテーブルを用いる。

mysql> SHOW CREATE TABLE users;  
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+  
| Table | Create Table                                                                                                                                                |  
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+  
| users | CREATE TABLE `users` ( 
  `id` int(11) DEFAULT NULL,  
  `name` varchar(255) DEFAULT NULL,  
  `age` int(11) DEFAULT NULL  
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |  
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+  
1 row in set (0.01 sec)  
  
mysql> SELECT * FROM users;  
+------+---------+------+  
| id   | name    | age  |  
+------+---------+------+  
|    0 | Taro    |   20 |  
|    1 | Yoshiko |   32 |  
+------+---------+------+  
2 rows in set (0.00 sec)

READ UNCOMMITTED

[ Session A ]

SessionA > SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;  
Query OK, 0 rows affected (0.00 sec)  
  
SessionA > START TRANSACTION;  
Query OK, 0 rows affected (0.00 sec)  
  
SessionA > SELECT * FROM users;  
+------+---------+------+  
| id   | name    | age  |  
+------+---------+------+  
|    0 | Taro    |   20 |  
|    1 | Yoshiko |   32 |  
+------+---------+------+  
2 rows in set (0.00 sec)

[ Session B ]

SessionB > SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;  
Query OK, 0 rows affected (0.00 sec)  
  
SessionB > START TRANSACTION;  
Query OK, 0 rows affected (0.00 sec)  
  
SessionB > UPDATE users SET age = 21 WHERE id = 0;  
Query OK, 1 row affected (0.00 sec)  
Rows matched: 1  Changed: 1  Warnings: 0  
  
SessionB > SELECT * FROM users;  
+------+---------+------+  
| id   | name    | age  |  
+------+---------+------+  
|    0 | Taro    |   21 |  
|    1 | Yoshiko |   32 |  
+------+---------+------+  
2 rows in set (0.00 sec)

[ Session A ]

SessionA > SELECT * FROM users;  
+------+---------+------+  
| id   | name    | age  |  
+------+---------+------+  
|    0 | Taro    |   21 |  
|    1 | Yoshiko |   32 |  
+------+---------+------+  
2 rows in set (0.00 sec)

という具合に SessionB におけるトランザクションの COMMIT 前に、SessionA から変更が見えてしまう (ダーティリード) 。

READ COMMITTED

[ Session A ]

SessionA > SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;  
Query OK, 0 rows affected (0.00 sec)  
  
SessionA > START TRANSACTION;  
Query OK, 0 rows affected (0.00 sec)  
  
SessionA > SELECT * FROM users;  
+------+---------+------+  
| id   | name    | age  |  
+------+---------+------+  
|    0 | Taro    |   21 |  
|    1 | Yoshiko |   32 |  
+------+---------+------+  
2 rows in set (0.00 sec)

[ Session B ]

SessionB > SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;  
Query OK, 0 rows affected (0.00 sec)  
  
SessionB > START TRANSACTION;  
Query OK, 0 rows affected (0.00 sec)  
  
SessionB > UPDATE users SET age = 22 WHERE id = 0;  
Query OK, 1 row affected (0.00 sec)  
Rows matched: 1  Changed: 1  Warnings: 0  
  
SessionB > SELECT * FROM users;  
+------+---------+------+  
| id   | name    | age  |  
+------+---------+------+  
|    0 | Taro    |   22 |  
|    1 | Yoshiko |   32 |  
+------+---------+------+  
2 rows in set (0.00 sec)

[ Session A ]

SessionA > SELECT * FROM users;  
+------+---------+------+  
| id   | name    | age  |  
+------+---------+------+  
|    0 | Taro    |   21 |  
|    1 | Yoshiko |   32 |  
+------+---------+------+  
2 rows in set (0.00 sec)

上記のように、READ COMITTED ではダーティリードが発生しないことがわかる。
しかし、Session B のトランザクションを COMMIT すると…

[ Session B ]

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

[ Session A ]

SessionA > SELECT * FROM users;  
+------+---------+------+  
| id   | name    | age  |  
+------+---------+------+  
|    0 | Taro    |   22 |  
|    1 | Yoshiko |   32 |  
+------+---------+------+  
2 rows in set (0.00 sec)

Session A のトランザクションが COMMIT されていないにも関わらず、Session A から Session B による更新結果が見えてしまう (ファジーリード)。
これは 2 つのトランザクションが隔離されていないことを意味する。例えば Taro の age の値を元に行う処理が Session A のトランザクション内に複数存在した場合、何らかの不整合が発生する可能性がある。

REPEATABLE READ

[ Session A ]

SessionA > SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;  
Query OK, 0 rows affected (0.00 sec)  
  
SessionA > START TRANSACTION;  
Query OK, 0 rows affected (0.00 sec)  
  
SessionA > SELECT * FROM users;  
+------+---------+------+  
| id   | name    | age  |  
+------+---------+------+  
|    0 | Taro    |   22 |  
|    1 | Yoshiko |   32 |  
+------+---------+------+  
2 rows in set (0.00 sec)

[ Session A ]

SessionB > SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;  
Query OK, 0 rows affected (0.00 sec)  
  
SessionB > START TRANSACTION;  
Query OK, 0 rows affected (0.00 sec)  
  
SessionB > SELECT * FROM users;  
+------+---------+------+  
| id   | name    | age  |  
+------+---------+------+  
|    0 | Taro    |   22 |  
|    1 | Yoshiko |   32 |  
+------+---------+------+  
2 rows in set (0.00 sec)  
  
SessionB > UPDATE users SET age = 23 WHERE id = 0;  
Query OK, 1 row affected (0.00 sec)  
Rows matched: 1  Changed: 1  Warnings: 0  
  
SessionB > SELECT * FROM users;  
+------+---------+------+  
| id   | name    | age  |  
+------+---------+------+  
|    0 | Taro    |   23 |  
|    1 | Yoshiko |   32 |  
+------+---------+------+  
2 rows in set (0.00 sec)  
  
SessionB > COMMIT;  
Query OK, 0 rows affected (0.00 sec)

[ Session A ]

SessionA > SELECT * FROM users;  
+------+---------+------+  
| id   | name    | age  |  
+------+---------+------+  
|    0 | Taro    |   22 |  
|    1 | Yoshiko |   32 |  
+------+---------+------+  
2 rows in set (0.00 sec)  
  
SessionA > COMMIT;  
Query OK, 0 rows affected (0.00 sec)  
  
SessionA > SELECT * FROM users;  
+------+---------+------+  
| id   | name    | age  |  
+------+---------+------+  
|    0 | Taro    |   23 |  
|    1 | Yoshiko |   32 |  
+------+---------+------+  
2 rows in set (0.00 sec)

上記のように、REPEATABLE READ ではファジーリードが発生しない。Session A のトランザクション内では Session B のトランザクションが COMMIT された後でも、その結果を反映しない形となる。
また、Session A のトランザクション COMMIT 後は Session B におけるトランザクションの結果が参照可能。

[ Session A ]

SessionA > START TRANSACTION;  
Query OK, 0 rows affected (0.01 sec)  
  
SessionA > SELECT COUNT(id), AVG(age) FROM users;  
+-----------+----------+  
| COUNT(id) | AVG(age) |  
+-----------+----------+  
|         2 |  27.5000 |  
+-----------+----------+  
1 row in set (0.00 sec)

[ Session B ]

SessionB > START TRANSACTION;  
Query OK, 0 rows affected (0.00 sec)  
  
SessionB > INSERT INTO users (id, name, age) VALUES (2, 'Hanako', 35);  
Query OK, 1 row affected (0.00 sec)  
  
SessionB > COMMIT;  
Query OK, 0 rows affected (0.00 sec)

[ Session A ]

SessionA > SELECT COUNT(id), AVG(age) FROM users;  
+-----------+----------+  
| COUNT(id) | AVG(age) |  
+-----------+----------+  
|         2 |  27.5000 |  
+-----------+----------+  
1 row in set (0.00 sec)  
  
SessionA > COMMIT;  
Query OK, 0 rows affected (0.00 sec)  
  
SessionA > SELECT COUNT(id), AVG(age) FROM users;  
+-----------+----------+  
| COUNT(id) | AVG(age) |  
+-----------+----------+  
|         3 |  30.0000 |  
+-----------+----------+  
1 row in set (0.00 sec)

上記のようにファントムリードも発生しない。INSERT された行による影響は COMMIT 後に初めて受けるようになる。