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)

MySQL(InnoDB)におけるデフォルトのトランザクション分離レベルにおいて起きうる問題

RDSの接続トラブルの原因を切り分ける手順をAWSの動画を参考にメモ


RDSにうまく接続できない時に考えられる原因と切り分け方のメモ。MySQLを例にする。MySQLのポートは3306と仮定する。

## (現象1) MySQLコマンドの結果が帰ってこないとき
別ターミナルで以下のコマンドを打つ。
```sh
netstat -an | grep 3306
```

### 結果パターン1
```sh
(略) SYN_SENT
```
上記のような出力の場合、リクエストを送ったがレスポンスが帰ってきていないことがわかる。その場合は、以下に問題がないか見直す。
1. セキュリティグループの設定
2. 接続元のFWの設定

## (現象2) Access Denied
→IDとパスワードが正しいことを確認する。

## (現象3) Unkown MySQL Server Host
→DNSの名前解決ができていない状態。この場合は、以下を確認する。
1. ホスト名が正しいか
2. 利用しているDNSが名前解決できる状態か(nslookup,dig,ncなどのコマンドを利用する)

## (現象4) 上記がすべて問題ないにも関わらず接続に失敗するとき
以下の可能性を確認する。
1. DBインスタンスのステータスが正常であること
2. 同時接続数の上限に達していないこと
3. DBのリソースが飽和していないこと

# 参考
- https://www.youtube.com/watch?v=_Ox2lul0cI8

RDSの接続トラブルの原因を切り分ける

GlueからAuroraのMySQLにJDBC接続する方法とハマったポイントについてまとめます


# 概要
AWS GlueからAuroraのMySQLにJDBC接続してデータを取得する方法を紹介します。

# 環境
- python==3.7
- pyspark==2.4.3

# 手順
1. VPCの設定
2. AWS Glue接続を追加する
3. コード

### VPCの設定
[こちら](https://docs.aws.amazon.com/ja_jp/glue/latest/dg/setup-vpc-for-glue-access.html)を参考に、接続したいAuroraクラスタが所属するセキュリティグループの設定をします。

### AWS Glue接続を追加する
[こちら](https://docs.aws.amazon.com/ja_jp/glue/latest/dg/console-connections.html)を参考に、接続したいAuroraクラスタに対して`AWS Glue`接続を追加します。

### コード
PySparkでJDBC接続する例を記載します。

```python
sc = SparkContext()
glueContext = GlueContext(sc)
spark = glueContext.spark_session

jdbc_url = f"jdbc:mysql://{ホスト}:{ポート}/{データベース名}"

df = spark.read.jdbc(jdbc_url, {テーブル名}, properties={
                "user": {ユーザ名},
                "password": {パスワード}
            })
```

JDBCに接続するためのURLの形式は以下

```python
"jdbc:mysql://{ホスト}:{ポート}/{データベース名}"
```

# 参考
- [JDBC データストアに接続するための VPC の設定 AWS Glue](https://docs.aws.amazon.com/ja_jp/glue/latest/dg/setup-vpc-for-glue-access.html)
- [AWS Glue 接続の追加](https://docs.aws.amazon.com/ja_jp/glue/latest/dg/console-connections.html)
- [Sparkのドキュメント](https://spark.apache.org/docs/2.4.3/sql-data-sources-jdbc.html)

masatora.net