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

## トランザクション分離レベル（Transaction Isolation Level）

* 特定のデータベース製品に固有の概念ではない
* SQL標準に**4つのレベル**が定められている
  - SERIALIZABLE
  - REPEATABLE READ
  - READ COMMITTED
  - READ UNCOMMITTED
* [トランザクション分離レベル - Wikipedia](https://ja.wikipedia.org/wiki/%E3%83%88%E3%83%A9%E3%83%B3%E3%82%B6%E3%82%AF%E3%82%B7%E3%83%A7%E3%83%B3%E5%88%86%E9%9B%A2%E3%83%AC%E3%83%99%E3%83%AB)にだいたい書いてある


## 分離レベルと起こり得る現象の関係

|  分離レベル        |  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で実験。

```sql:準備
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`でセッションごとに変更できる

```sql
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の実験

```sql:準備
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

```sql: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)
```
```sql: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

```sql: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)
```
```sql: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

```sql: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)
```
```sql: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

```sql: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)
```
```sql: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は起きない。**

ロック待機のタイムアウトまでにかかる時間は以下のようにすると確認できる。

```sql
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の実験

```sql:準備
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

```sql: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)
```
```sql: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)
```
```sql: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

```sql: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)
```
```sql: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)
```
```sql: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

```sql: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)
```
```sql: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)
```
```sql: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

```sql: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)
```
```sql: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の実験

```sql:準備
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

```sql: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)
```
```sql: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)
```
```sql: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

```sql: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)
```
```sql: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)
```
```sql: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

```sql: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)
```
```sql: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)
```
```sql: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

```sql: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)
```
```sql: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](https://www.microsoft.com/en-us/research/publication/a-critique-of-ansi-sql-isolation-levels/)
[^2]: [MySQL InnoDBのネクストキーロック おさらい - SH2の日記](http://d.hatena.ne.jp/sh2/20090112)