ACID/トランザクション分離レベルといった概念の備忘録と、MySQLで実装する際に何を気をつけるべきかの整理


# 概要
トランザクション分離レベルの概念を整理した上で、MySQLを使ってアプリケーションを実装する際に意識するべき点をまとめます。

# 前提知識
## トランザクション
トランザクションは、アプリケーションにおいて、複数の読み書き操作を論理的な単位でまとめる方法です。トランザクションは完全に実行される(commit)か、あるいは全く実行されないか(rollback)のどちらかとなります。

## ACID特性
ACIDは、トランザクションに求められる4つの要素を指します。各データベースのACIDの実装はかなり異なるらしいです。

1. 原子性(Atomicity): エラーが発生した際にトランザクションを中断し、そのトランザクションのすべての書き込みを破棄できる
2. 一貫性(Consistency): トランザクションの開始時と終了時でデータが一貫性のある状態になっている
3. 分離性(Isolation): 進行中のトランザクションは他のトランザクションの途中の操作に影響されず、独立して実行される
4. 永続性(Durability): トランザクションのコミットが成功した場合、そのトランザクションで書き込まれたデータが失われない(ハードウェア障害やデータベースがクラッシュしたとしても)

## トランザクション分離レベル(弱いトランザクションの分離)
ほとんどのデータベースでは、単一のオブジェクトの原子性や分離性を保証します。問題となるのは、複数のトランザクションが同時に一つのデータを変更しようとしたり、一つのトランザクションが他のトランザクションによって変更されたデータを読み取ろうとする時です。

この問題を回避するための理想的な方法は、直列化可能な分離性（Serializable）を採用することです。これは、トランザクションが順番に（直列的に）実行され、その結果が同じになることを保証します。しかし、パフォーマンスの観点から、多くのデータベースでは「弱いレベルの分離性」を採用しています。

この「弱いレベルの分離性」とは、すべての並行性の問題を回避するのではなく、特定の問題に対する保護のみを提供するものです。具体的には、以下のレベルが存在します：

1. Read Uncommitted
2. Read Committed
3. Repeatable Read

### Read Uncommitted
最も弱いトランザクション分離レベルで、他のトランザクションがまだコミットしていない変更を読み取ることが許されます。

### Read Committed
このレベルでは、以下の2つを保証します。
1. ダーティリードは生じない(コミットされたデータのみ読み取り可能)
2. ダーティライトは生じない(コミットされたデータに対してのみ上書き可能)

一方で、読み取りスキューやファントムリードという問題が生じる可能性があります。

### Repeatable Read
それぞれのトランザクションがデータベースの一貫性のあるスナップショットからのみ読み取りを行います。すなわち、トランザクションが読み取るデータは、そのトランザクションの開始時点でコミット済みのものだけで、トランザクションの実行途中で別のトランザクションによってコミットされたデータは反映されません。

それにより、Read Committedでは生じる読み取りスキューやファントムリードといった問題を回避できます。

## MySQL(InnoDB)におけるトランザクション分離レベル
[デフォルトで、MySQL(InnoDB)はRepeatable Readを採用](https://dev.mysql.com/doc/refman/8.0/ja/innodb-transaction-isolation-levels.html)しています。ただし、MySQL(InnoDB)のRepeatable Readは更新のロストを自動検出しないらしい。

# 起きうる問題と対応方法
MySQL(InnoDB)のRepeatable Readの実装において起きうる問題は以下です。
1. 更新のロスト
2. 書き込みスキュー

## 更新のロスト
アプリケーションが何らかの値をデータベースから読み取り、その値を変更して書き戻す場合に生じることがあります。

```mermaid
sequenceDiagram
A->>DB: Read(X)
DB->>A: X=5

B->>DB: Read(X)
DB->>B: X=5

A->>DB: X=X+1
A->>DB: Commit
DB->>A: X=6

B->>DB: X=X+1
B->>DB: Commit
DB->>B: X=6(トランザクションAの更新がロスト)
```

### 対応方法
- DBが提供するアトミックな書き込み操作を使う

```sql
UPDATE counters SET value = value + 1 WHERE key = 'foo';
```

- アプリケーションで明示的にロックする
- compare-and-set(最後の読み取り時から値が変化していない場合のみ更新を行う)
- (実装によっては更新のロストを自動検出できる。MySQL(InnoDB)ではできないらしい)

## 書き込みスキュー
トランザクションが何らかの値を読み取り、その値に基づいて判断を行った上でそれぞれ値を更新する場合に起きえます。判断の結果が、他のトランザクションの書き込みによって真でなくなってしまう場合を指します。

例として、病院の当直管理アプリケーションを想定します。医師は、最低でも一人の同僚が当直に入っていれば(currently_on_call>=2)自分の当直を取り消せます。

```mermaid
sequenceDiagram
participant Alice
participant DB as データベース
participant Bob

Alice->>DB: begin transaction
Bob->>DB: begin transaction

Alice->>DB: select count(*) (on_call=true, shift_id=1234)
DB-->>Alice: currently_on_call=2

Bob->>DB: select count(*) (on_call=true, shift_id=1234)
DB-->>Bob: currently_on_call=2

Alice->>DB: if >=2 then update set on_call=false for Alice

Bob->>DB: if >=2 then update set on_call=false for Bob

Alice->>DB: commit transaction
Bob->>DB: commit transaction

Note over DB: 最終的にcurrently_on_callは0になってしまう

```

### 対応方法
- 自動的に回避するためには、直列化可能分離レベルが必要。
- Repeatable Readで回避するには、アプリケーションでトランザクションが依存する行を明示的にロックする。
- ユニーク制約で回避できるケースもある(ユニークなユーザ名の登録操作等)。


# 参考
- [データ志向アプリケーションデザイン](https://www.oreilly.co.jp/books/9784873118703/)
- [MySQLリファレンス トランザクション分離レベル](https://dev.mysql.com/doc/refman/8.0/ja/innodb-transaction-isolation-levels.html)
- [トランザクション分離レベルのケースと対応方法](https://christina04.hatenablog.com/entry/transaction-isolation-level)

masatora.net