トランザクション分離レベル(Transaction Isolation Level)
- 特定のデータベース製品に固有の概念ではない
- SQL標準に4つのレベルが定められている
- SERIALIZABLE
- REPEATABLE READ
- READ COMMITTED
- READ UNCOMMITTED
- トランザクション分離レベル - Wikipediaにだいたい書いてある
分離レベルと起こり得る現象の関係
分離レベル | Dirty Read | Non-Repeatable Read | Phantom Read |
---|---|---|---|
READ UNCOMMITTED | 起きる | 起きる | 起きる |
READ COMMITTED | 起きない | 起きる | 起きる |
REPEATABLE READ | 起きない | 起きない | 起きる |
SERIALIZABLE | 起きない | 起きない | 起きない |
Dirty Read
参考論文1 section 2.2 より、抄訳:
トランザクションT1がデータを変更し、COMMITかROLLBACKをする前に、トランザクションT2がそのデータを読む。その後T1がROLLBACKした場合、T2はCOMMITされていない=実在しないデータを読んだことになる。
Non-Repeatable Read
Fuzzy Readともいう。
参考論文1 section 2.2 より、抄訳:
トランザクションT1がデータを読んだ後、トランザクションT2がそのデータを変更もしくは削除してCOMMITする。その後T1がデータを再度読もうとすると、データが変更されている、もしくは削除されていることが検知される。
Phantom Read
参考論文1 section 2.2 より、抄訳:
トランザクションT1が、ある検索条件に基づいてデータ集合を読む。その後、トランザクションT2がその検索条件を満たすデータを作成しCOMMITする。T1が再度同じ検索条件で読み取りを行うと、最初に得られたデータ集合と異なるデータ集合が得られる。
注意点
- READ UNCOMMITTED / READ COMMITTED / REPEATABLE READ は上記の3つの問題が起きる/起きないにより区別されているが、**SERIALIZABLEの定義は「3つ全部起きない」ではなく「直列化可能である」**のようだ(参考論文1 section 2.2 p3)
- 直列化可能であれば上記3つの問題は起きない
- ……が、「上記3つの問題が起きなければ直列化可能である」わけではない、と参考論文1は言っている様子?
- 次回以降調査する
- MySQLでは、都合(互換性の問題らしい2)によりREPEATABLE READでもPhantom Readが起きない実装になっているらしい
- 次回以降調査する
MySQLで実験
手元にあったMySQL-5.6.14で実験。
CREATE DATABASE TIL;
USE TIL;
CREATE TABLE test (
id INTEGER NOT NULL DEFAULT 0,
a INTEGER NOT NULL DEFAULT 0,
PRIMARY KEY (id)
);
分離レベルを変える
-
SHOW VARIABLES LIKE 'tx_isolation';
で現在の値を確認できる -
SET SESSION TRANSACTION ISOLATION LEVEL
でセッションごとに変更できる
mysql> SHOW VARIABLES LIKE 'tx_isolation';
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| tx_isolation | REPEATABLE-READ |
+---------------+-----------------+
mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
mysql> SHOW VARIABLES LIKE 'tx_isolation';
+---------------+------------------+
| Variable_name | Value |
+---------------+------------------+
| tx_isolation | READ-UNCOMMITTED |
+---------------+------------------+
以下、結果を貼っていくので長い。
Dirty Readの実験
DELETE FROM test;
INSERT INTO test (id, a) VALUES (1, 10);
- ターミナルを2つ開き、両方で
SET SESSION TRANSACTION ISOLATION LEVEL
を設定する - トランザクション
Tx1
を開始する -
Tx1
でid = 1
の行のa
を20
に書き換える - トランザクション
Tx2
を開始する -
Tx2
でid = 1
の行を読み取る-
a = 20
ならDirty Readが起きている
-
READ UNCOMMITTED
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> UPDATE test SET a = 20 WHERE id = 1;
Query OK, 1 row affected (0.06 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM test WHERE id = 1;
+----+----+
| id | a |
+----+----+
| 1 | 20 |
+----+----+
1 row in set (0.00 sec)
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM test WHERE id = 1;
+----+----+
| id | a |
+----+----+
| 1 | 20 |
+----+----+
1 row in set (0.00 sec)
Tx2
が読み取った値がa = 20
なので、Dirty Readが起きている。
READ COMMITTED
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> UPDATE test SET a = 20 WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM test WHERE id = 1;
+----+----+
| id | a |
+----+----+
| 1 | 20 |
+----+----+
1 row in set (0.00 sec)
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM test WHERE id = 1;
+----+----+
| id | a |
+----+----+
| 1 | 10 |
+----+----+
1 row in set (0.00 sec)
Tx2
が読み取った値がa = 10
なので、Dirty Readは起きていない。
REPEATABLE READ
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> UPDATE test SET a = 20 WHERE id = 1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM test WHERE id = 1;
+----+----+
| id | a |
+----+----+
| 1 | 20 |
+----+----+
1 row in set (0.00 sec)
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM test WHERE id = 1;
+----+----+
| id | a |
+----+----+
| 1 | 10 |
+----+----+
1 row in set (0.00 sec)
Tx2
が読み取った値がa = 10
なので、Dirty Readは起きていない。
SERIALIZABLE
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> UPDATE test SET a = 20 WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM test WHERE id = 1;
+----+----+
| id | a |
+----+----+
| 1 | 20 |
+----+----+
1 row in set (0.00 sec)
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM test WHERE id = 1;
/* 待たされる */
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
Tx2
がエラーになったので、Dirty Readは起きない。
ロック待機のタイムアウトまでにかかる時間は以下のようにすると確認できる。
mysql> SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50 |
+--------------------------+-------+
1 row in set (0.00 sec)
Tx2
で再度SELECT * FROM test WHERE id = 1;
を実行し、このタイムアウト時間が過ぎる前にTx1
でROLLBACK
すると、エラーにはならずa = 10
が得られる。(これはDirty Readがどうこうではなく、a
を変更したTx1
がROLLBACK
されているからである。)
Non-Repeatable Readの実験
DELETE FROM test;
INSERT INTO test (id, a) VALUES (1, 10);
- ターミナルを2つ開き、両方で
SET SESSION TRANSACTION ISOLATION LEVEL
を設定する - トランザクション
Tx1
を開始する -
Tx1
でid = 1
の行を読み取る - トランザクション
Tx2
を開始する -
Tx2
でid = 1
の行のa
を20
に書き換える -
Tx2
でコミットする -
Tx1
でid = 1
の行を読み取る-
a = 20
ならNon-Repeatable Readが起きている
-
READ UNCOMMITTED
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM test WHERE id = 1;
+----+----+
| id | a |
+----+----+
| 1 | 10 |
+----+----+
1 row in set (0.03 sec)
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> UPDATE test SET a = 20 WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> COMMIT;
Query OK, 0 rows affected (0.09 sec)
mysql> SELECT * FROM test WHERE id = 1;
+----+----+
| id | a |
+----+----+
| 1 | 20 |
+----+----+
1 row in set (0.00 sec)
Tx1
が読み取った値がa = 20
なので、Non-Repeatable Readが起きている。
READ COMMITTED
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM test WHERE id = 1;
+----+----+
| id | a |
+----+----+
| 1 | 10 |
+----+----+
1 row in set (0.00 sec)
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> UPDATE test SET a = 20 WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> COMMIT;
Query OK, 0 rows affected (0.09 sec)
mysql> SELECT * FROM test WHERE id = 1;
+----+----+
| id | a |
+----+----+
| 1 | 20 |
+----+----+
1 row in set (0.00 sec)
Tx1
が読み取った値がa = 20
なので、Non-Repeatable Readが起きている。
REPEATABLE READ
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM test WHERE id = 1;
+----+----+
| id | a |
+----+----+
| 1 | 10 |
+----+----+
1 row in set (0.00 sec)
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> UPDATE test SET a = 20 WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> COMMIT;
Query OK, 0 rows affected (0.09 sec)
mysql> SELECT * FROM test WHERE id = 1;
+----+----+
| id | a |
+----+----+
| 1 | 10 |
+----+----+
1 row in set (0.00 sec)
Tx1
が読み取った値がa = 10
なので、Non-Repeatable Readは起きていない。
Tx1
をROLLBACK
してトランザクションを終了させてから、再度SELECT
をすると、a = 20
が読み取れる。
SERIALIZABLE
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM test WHERE id = 1;
+----+----+
| id | a |
+----+----+
| 1 | 10 |
+----+----+
1 row in set (0.00 sec)
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> UPDATE test SET a = 20 WHERE id = 1;
/* 待たされる */
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
エラーが発生し、Tx2
でid = 1
の行を更新できないので、Non-Repeatable Readは起きない。
Phantom Readの実験
DELETE FROM test;
INSERT INTO test (id, a) VALUES (1, 10);
- ターミナルを2つ開き、両方で
SET SESSION TRANSACTION ISOLATION LEVEL
を設定する - トランザクション
Tx1
を開始する -
Tx1
で全行を読み取る - トランザクション
Tx2
を開始する -
Tx2
でid = 2
の行を挿入する -
Tx2
でコミットする -
Tx1
で全行を読み取る-
id = 2
の行が得られたならPhantom Readが起きている
-
READ UNCOMMITTED
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM test;
+----+----+
| id | a |
+----+----+
| 1 | 10 |
+----+----+
1 row in set (0.00 sec)
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO test (id, a) VALUES (2, 20);
Query OK, 1 row affected (0.00 sec)
mysql> COMMIT;
Query OK, 0 rows affected (0.05 sec)
mysql> SELECT * FROM test;
+----+----+
| id | a |
+----+----+
| 1 | 10 |
| 2 | 20 |
+----+----+
2 rows in set (0.00 sec)
Tx1
がid = 2
の行を読み取っているので、Phantom Readが起きている。
READ COMMITTED
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM test;
+----+----+
| id | a |
+----+----+
| 1 | 10 |
+----+----+
1 row in set (0.00 sec)
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO test (id, a) VALUES (2, 20);
Query OK, 1 row affected (0.01 sec)
mysql> COMMIT;
Query OK, 0 rows affected (0.05 sec)
mysql> SELECT * FROM test;
+----+----+
| id | a |
+----+----+
| 1 | 10 |
| 2 | 20 |
+----+----+
2 rows in set (0.00 sec)
Tx1
がid = 2
の行を読み取っているので、Phantom Readが起きている。
REPEATABLE READ
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM test;
+----+----+
| id | a |
+----+----+
| 1 | 10 |
+----+----+
1 row in set (0.00 sec)
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO test (id, a) VALUES (2, 20);
Query OK, 1 row affected (0.00 sec)
mysql> COMMIT;
Query OK, 0 rows affected (0.13 sec)
mysql> SELECT * FROM test;
+----+----+
| id | a |
+----+----+
| 1 | 10 |
+----+----+
1 row in set (0.00 sec)
Tx1
がid = 2
の行を読み取っていないので、Phantom Readが起きていない。
Tx1
をROLLBACK
してトランザクションを終了させてから、再度SELECT
をすると、id = 2
が読み取れる。
SERIALIZABLE
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM test;
+----+----+
| id | a |
+----+----+
| 1 | 10 |
+----+----+
1 row in set (0.00 sec)
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO test (id, a) VALUES (2, 20);
/* 待たされる */
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
エラーが発生し、Tx2
でid = 2
の行を挿入できないので、Phantom Readは起きない。
今回のまとめと感想
- SQL標準のトランザクション分離レベルは3つの現象ベースで定義されている(SERIALIZABLE以外)
- 「現象が起きない」点が同じでも、「どのように現象を防ぐか」は複数考えられる
- 前のバージョンの値を使う(MVCC?次回調査)
- 操作自体をエラーにする(ロック?次回調査)
- 実際にアプリケーションを作る際は、「どのように現象を防ぐか」まで理解する必要がある
- 前のバージョンの値でいいのか、エラーになるべきなのかを決めるのはアプリケーションの仕様
- エラーになるならエラー処理が必要
- (感想)現象の起きる/起きないベースよりも詳細な、どのように現象を防ぐかベースで考えたら、もっと細かい分類ができるのでは……?
- 次回以降もう少し考えてみたい
次回以降の予定
- 今回の実験結果について、MySQLが実際にどんな技術(ロック、MVCC)を使っているか調べてまとめる
- SQL標準の4つのトランザクション分離レベルを批判している論文1があるらしいので読む