MySQLの分離レベルについて
概要
各トランザクションをどれだけ分離 / 独立させるかを選べる
分離性を妥協することで、パフォーマンスを高められる
前提知識
分離レベルが低いことにより起きる問題
他トランザクションの確定してない段階のデータを読み取ってしまう、読み取り方法のこと。
例
トランザクション A で、データ A を INSERT する
トランザクション B で、データ A を読み取る
トランザクション A で、ロールバックする
この時、データ A は存在しないデータとなり、トランザクション B が読み取ったデータは不整合を起こす
１回目と２回目で、読み取り結果の内容が更新されてしまう可能性のある、読み取り方法のこと。
例
トランザクション A で、データ A を読み取る（１回目）
トランザクション B で、データ A の内容を UPDATE する
トランザクション A で、データ A を読み取る（２回目）
この時、１回目と２回目の結果は同じであるべき。
しかし、１回目と２回目の結果が異なってしまう場合、ノンリピータブルリードと呼ばれる。
１回目と２回目で、読み取り結果にレコードが追加 / 削除されてしまう可能性のある、読み取り方法のこと。
例
トランザクション A で、範囲 A のデータを読み取る（１回目）
トランザクション B で、範囲 A の中に新たにデータを追加
トランザクション A で、範囲 A のデータを読み取る（２回目）
この時、１回目と２回目は同じ結果であるべき。
しかし、１回目と２回目の結果が異なってしまう場合、ファントムリードと呼ばれる。
ロックの種類
レコードロック
ギャップロック
ネクストキーロック
レコードのスナップショットを読むことで、変更前のデータをロックなしで確実に読み取れる方法のこと
分離レベル
上の方が分離レベルが低く、下の方が分離レベルが高い。
READ UNCOMMITTED
どれくらいの分離をするか？
ダーティーリード、ノンリピータブルリード、ファントムリードの全てが発生する
どういう動作をするか？
何もロックしない
READ COMMITTED
どれくらいの分離をするか？
ダーティーリード、ノンリピータブルリードは発生しない
ファントムリードは発生する
どういう動作をするか？
ロックして読み取ると、レコードロックのみを使用し、ギャップロックは使用しない。
そのためファントムリードが発生する可能性があります。
REPEATABLE READ
どれくらいの分離をするか？
ダーティーリード、ノンリピータブルリード、ファントムリードは発生しない、はず
どういう動作をするか？
ロックして読み取ると、
一意の検索条件の場合、レコードロック
その他の検索条件の場合、条件を満たすかどうかスキャンした範囲をネクストキーロックする
code:sql
UPDATE t SET c = 5 WHERE c = 3;
上記で c カラムにインデックスが貼られておらず、テーブルスキャンをした場合、全てのレコードとギャップが排他ロックされる。
途中で c = 3 のレコードが増えると困るため、スキャン範囲を全てロックする
SERIALIZABLE
どれくらいの分離をするか？
ダーティーリード、ノンリピータブルリード、ファントムリードは発生しない
どういう動作をするか？
autocommit が有効（デフォルト）な場合は、REPEATABLE READ と同じ動作になる、はず。
autocommit が無効な場合は、SELECT を SELECT ... FOR SHARE に暗黙的に変換します。
その他
ANSI/ISO SQL標準は名前と実態が一致しているが、MySQL の方は、名前と実態が一致していない笑
MySQL の方は「何を保証するか」よりも「どれくらいロックをするか」でレベルを分けている印象
参考リンク
トランザクションの動作については、上記を参考にしました。
ダーティーリードなど、問題のある読み取り方法については、上記を参考にしました。
ロックについては、上記を参考にしました。
分離性については、上記を参考にしました。