LoginSignup
53
40

More than 5 years have passed since last update.

@noonworks

トランザクション分離レベルの勉強記録(1) 4つのレベルを実験する

Posted at

トランザクション分離レベル（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);
  1. ターミナルを2つ開き、両方でSET SESSION TRANSACTION ISOLATION LEVELを設定する
  2. トランザクションTx1を開始する
  3. Tx1でid = 1の行のaを20に書き換える
  4. トランザクションTx2を開始する
  5. Tx2でid = 1の行を読み取る
    • a = 20ならDirty Readが起きている

READ UNCOMMITTED

Tx1
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)
Tx2
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

Tx1
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)
Tx2
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

Tx1
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)
Tx2
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

Tx1
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)
Tx2
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);
  1. ターミナルを2つ開き、両方でSET SESSION TRANSACTION ISOLATION LEVELを設定する
  2. トランザクションTx1を開始する
  3. Tx1でid = 1の行を読み取る
  4. トランザクションTx2を開始する
  5. Tx2でid = 1の行のaを20に書き換える
  6. Tx2でコミットする
  7. Tx1でid = 1の行を読み取る
    • a = 20ならNon-Repeatable Readが起きている

READ UNCOMMITTED

Tx1
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)
Tx2
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)
Tx1
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

Tx1
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
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)
Tx1
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

Tx1
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
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)
Tx1
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

Tx1
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
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);
  1. ターミナルを2つ開き、両方でSET SESSION TRANSACTION ISOLATION LEVELを設定する
  2. トランザクションTx1を開始する
  3. Tx1で全行を読み取る
  4. トランザクションTx2を開始する
  5. Tx2でid = 2の行を挿入する
  6. Tx2でコミットする
  7. Tx1で全行を読み取る
    • id = 2の行が得られたならPhantom Readが起きている

READ UNCOMMITTED

Tx1
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)
Tx2
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)
Tx1
mysql> SELECT * FROM test;
+----+----+
| id | a  |
+----+----+
|  1 | 10 |
|  2 | 20 |
+----+----+
2 rows in set (0.00 sec)

Tx1がid = 2の行を読み取っているので、Phantom Readが起きている。

READ COMMITTED

Tx1
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)
Tx2
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)
Tx1
mysql> SELECT * FROM test;
+----+----+
| id | a  |
+----+----+
|  1 | 10 |
|  2 | 20 |
+----+----+
2 rows in set (0.00 sec)

Tx1がid = 2の行を読み取っているので、Phantom Readが起きている。

REPEATABLE READ

Tx1
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)
Tx2
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)
Tx1
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

Tx1
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)
Tx2
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があるらしいので読む

脚注

  1. A Critique of ANSI SQL Isolation Levels - Microsoft Research ↩

  2. MySQL InnoDBのネクストキーロック おさらい - SH2の日記 ↩

53
40
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
53
40