分離レベルが異なるトランザクション間での独立性について実験（MySQL/InnoDB）

DBの[トランザクション分離レベル](https://ja.wikipedia.org/wiki/トランザクション分離レベル)を勉強していた際、これは「他のトランザクションの**影響を受けない**」のか「他のトランザクションに**影響を与えない**」のか（あるいはその両方なのか）が気になりました。

結果を推測できるほどの知識はまだ全然ないため、ひとまず実際に MySQL (InnoDB) で試して確認しました。 <br> ※他のDBだと異なる結果になるかもしれません。

## TL;DR
「他のトランザクションの影響を受けない」だけのようです。

* 他のトランザクションの分離レベルが低くても、自分の処理内容は自分の分離レベルに応じて安全が守られる（たぶん）
	* 自分の読み込みのためにスナップショットを作ることがある
	* 必要に応じて排他制御が働き、相手か自分自身が処理を待機する
* 自分の処理内容が他のトランザクションから見られてしまうことがある
	* **実際に `SERIALIZABLE` でのコミット前の内容が `READ UNCOMMITTED` から見えた**

## 実験準備
### サンプルデータ
MySQL のリファレンスマニュアルから適当に持ってきました。

```sql:samples.sql
-- https://dev.mysql.com/doc/refman/8.0/ja/innodb-transaction-isolation-levels.html
CREATE TABLE t (a INT NOT NULL, b INT) ENGINE = InnoDB;
INSERT INTO t VALUES (1,2),(2,3),(3,2),(4,3),(5,2);
COMMIT;
```

### サーバとクライアント
MySQLサーバを用意し、2つのクライアントから接続してクエリを投入できるようにします。

簡単に試せるといいので、今回はDockerでコンテナをひとつ立ち上げてその中で完結させます。

```bash
# サーバを起動
docker run --rm -d --name mysql-test-server \
    -e MYSQL_ROOT_PASSWORD= \
    -e MYSQL_ALLOW_EMPTY_PASSWORD=yes \
    -e MYSQL_DATABASE=tx_test_db \
    mysql:8.0.27
# （起動完了するのを確認）
docker logs -f mysql-test-server
# サンプルデータを投入
docker exec -i mysql-test-server  mysql tx_test_db < samples.sql

# 同じコンテナ内でクライアントを実行して接続（別のターミナルからが良い）
docker exec -it mysql-test-server  mysql tx_test_db
# --> 自由にクエリを投入して実験する

# サーバを停止し、コンテナを破棄
docker stop mysql-test-server
```

## 実験
以下では connection A と connection B から並行してクエリを投入します。

### 同じ分離レベル（確認）
#### `READ COMMITTED` 同士
まずはよくあるパターンを試しておきます。

`READ COMMITTED` は名前通り、コミットされたデータを読みます。言い換えるとコミット前のデータは読むことがありません。

これは**非再現リード（non-repeatable read）**という現象が起こります。行儀よくひとりずつトランザクションを実行した際は、トランザクション内で連続して `SELECT` すると常に同じ結果が返ります。しかし他の人が並行作業していると途中でデータが書き換わり、連続して `SELECT` してもタイミング次第で異なる結果となり得ます。

```sql
--        connection A        /        connection B        --
SET SESSION TRANSACTION
    ISOLATION LEVEL READ COMMITTED;

                                SET SESSION TRANSACTION
                                    ISOLATION LEVEL READ COMMITTED;

SELECT * FROM t WHERE b = 2;
--> 3 rows in set  (#1)

                                BEGIN;

                                UPDATE t SET b = 2 WHERE b != 2;
                                --> 2 rows affected
                                --> Rows matched: 2  Changed: 2  Warnings: 0

BEGIN;

SELECT * FROM t WHERE b = 2;
--> 3 rows in set  (#2)

                                COMMIT;

SELECT * FROM t WHERE b = 2;
--> 5 rows in set  (#3)  !! non-repeatable read !!

COMMIT;
```

A側はトランザクション内で2回 `SELECT` しました。B側がトランザクション内で `UPDATE` しただけではA側には影響が出ませんでしたが（ `#2` ）、B側がコミットするとA側に影響が出ました（ `#3` ）。

#### `REPEATABLE READ` 同士
前節の現象は `REPEATABLE READ` では起きないことも確認しておきます。

```sql
--        connection A        /        connection B        --
SET SESSION TRANSACTION
    ISOLATION LEVEL REPEATABLE READ;

                                SET SESSION TRANSACTION
                                    ISOLATION LEVEL REPEATABLE READ;

SELECT * FROM t WHERE b = 2;
--> 3 rows in set  (#1)

                                BEGIN;

                                UPDATE t SET b = 2 WHERE b != 2;
                                --> 2 rows affected
                                --> Rows matched: 2  Changed: 2  Warnings: 0

BEGIN;

SELECT * FROM t WHERE b = 2;
--> 3 rows in set  (#2)

                                COMMIT;

SELECT * FROM t WHERE b = 2;
--> 3 rows in set  (#3)

COMMIT;

SELECT * FROM t WHERE b = 2;
--> 5 rows in set  (#4)
```

B側でコミットした後の `#3` でも `#2` と同じ結果が返りました。トランザクションを終えた後の `#4` では、B側のコミットが反映されています。

同じ結果が返る仕組みは、トランザクションの最初の `#2` でスナップショットを作ってそこから読み取っているからだそうです。（一方で `READ COMMITTED` では毎回スナップショットを作り直していて、 `#2` と `#3` とで異なっています）

### 異なる分離レベル
#### `SERIALIZABLE` と `READ UNCOMMITTED` (1)
これらは分離レベルの最も高いものと最も低いものです。 `SERIALIZABLE` なトランザクションの中の作業は恐らく外部の影響を受けないでしょうが、逆に**中の作業が外部に漏れる（影響を与える）ことがあるのか**が気になります。

これまでと同じものをやってみます。本節はA側（ `SELECT` ）が `SERIALIZABLE` 、B側（ `UPDATE` ）が `READ UNCOMMITTED` です。

```sql
--        connection A        /        connection B        --
SET SESSION TRANSACTION
    ISOLATION LEVEL SERIALIZABLE;

                                SET SESSION TRANSACTION
                                    ISOLATION LEVEL READ UNCOMMITTED;

SELECT * FROM t WHERE b = 2;
--> 3 rows in set  (#1)

                                BEGIN;

                                UPDATE t SET b = 2 WHERE b != 2;
                                --> 2 rows affected
                                --> Rows matched: 2  Changed: 2  Warnings: 0

BEGIN;

SELECT * FROM t WHERE b = 2;
--> (wait)  (#2)

                                COMMIT;
--> 5 rows in set  (#2')

COMMIT;
```

A側は `SELECT` が待機状態になり、B側のトランザクションが完了してから `UPDATE` 反映後の結果が返りました。

【理屈】 <br> `SERIALIZABLE` で普通の `SELECT` を実行すると、 `SELECT ... FOR SHARE` と扱われ共有ロックを取得します。しかし `#2` のタイミングではB側の `UPDATE` が排他ロックを取得していて競合するため、排他ロックがなくなるまで待つことになります。

#### `SERIALIZABLE` と `READ UNCOMMITTED` (2)
(1)では `UPDATE` が先だったため `SELECT` が待たされました。逆順も試してみます。

```sql
--        connection A        /        connection B        --
SET SESSION TRANSACTION
    ISOLATION LEVEL SERIALIZABLE;

                                SET SESSION TRANSACTION
                                    ISOLATION LEVEL READ UNCOMMITTED;

BEGIN;

SELECT * FROM t WHERE b = 2;
--> 3 rows in set  (#1)

                                BEGIN;

                                UPDATE t SET b = 2 WHERE b != 2;
                                --> (wait)

COMMIT;
                                --> 2 rows affected
                                --> Rows matched: 2  Changed: 2  Warnings: 0

                                COMMIT;
```

今度はB側の `UPDATE` が待機状態になり、A側のトランザクションが終了してから処理されました。

#### `SERIALIZABLE` と `READ UNCOMMITTED` (3)
(1)と分離レベルを入れ替えてみます。A側（ `SELECT` ）が `READ UNCOMMITTED` 、B側（ `UPDATE` ）が `SERIALIZABLE` です。

※ついでに今回だけB側はトランザクションをロールバックします。

```sql
--        connection A        /        connection B        --
SET SESSION TRANSACTION
    ISOLATION LEVEL READ UNCOMMITTED;

                                SET SESSION TRANSACTION
                                    ISOLATION LEVEL SERIALIZABLE;

SELECT * FROM t WHERE b = 2;
--> 3 rows in set  (#1)

                                BEGIN;

                                UPDATE t SET b = 2 WHERE b != 2;
                                --> 2 rows affected
                                --> Rows matched: 2  Changed: 2  Warnings: 0

BEGIN;

SELECT * FROM t WHERE b = 2;
--> 5 rows in set  (#2)  !! dirty read !!

                                ROLLBACK;

SELECT * FROM t WHERE b = 2;
--> 3 rows in set  (#3)

COMMIT;
```

`READ UNCOMMITTED` は `#2` のタイミングで、 **`SERIALIZABLE` がコミットしていないデータを読み取れました**（ダーティリード）。 `SERIALIZABLE` の中は外部から隠蔽される、というわけではないようです。

#### `SERIALIZABLE` と `READ COMMITTED`
念のため(3)からA側の分離レベルを `READ COMMITTED` に上げた場合も試してみます。

```sql
--        connection A        /        connection B        --
SET SESSION TRANSACTION
    ISOLATION LEVEL READ COMMITTED;

                                SET SESSION TRANSACTION
                                    ISOLATION LEVEL SERIALIZABLE;

SELECT * FROM t WHERE b = 2;
--> 3 rows in set  (#1)

                                BEGIN;

                                UPDATE t SET b = 2 WHERE b != 2;
                                --> 2 rows affected
                                --> Rows matched: 2  Changed: 2  Warnings: 0

BEGIN;

SELECT * FROM t WHERE b = 2;
--> 3 rows in set  (#2)

                                COMMIT;

SELECT * FROM t WHERE b = 2;
--> 5 rows in set  (#3)  !! non-repeatable read !!

COMMIT;
```

`SEREALIZABLE` のコミット前のデータが読まれることはなく、最初の「 `READ COMMITTED` 同士」と同じようになりました。

## まとめ
自分へ適切な分離レベルを設定した際、「他のトランザクションの影響を受けない」のは正しそうですが、「他のトランザクションに影響を与えない」というわけではありませんでした。影響を与えるかどうかは、相手の分離レベルが他から影響を受けるかどうか次第であり、自分が完全に制御できるわけではなさそうです。

別の見方をすれば、 `READ UNCOMMITTED` に設定すれば他のトランザクション内の様子を覗き放題ということでしょうか。

## 参考

* MySQL 8.0 リファレンスマニュアル
	* [トランザクション分離レベル](https://dev.mysql.com/doc/refman/8.0/ja/innodb-transaction-isolation-levels.html)
	* [一貫性非ロック読み取り](https://dev.mysql.com/doc/refman/8.0/ja/innodb-consistent-read.html)
	* [InnoDB ロック](https://dev.mysql.com/doc/refman/8.0/ja/innodb-locking.html)
* DockerHub
	* [mysql (Official Image)](https://hub.docker.com/_/mysql)