MySQL/Postgres におけるトランザクション分離レベル

MySQL/Postgres におけるトランザクション分離レベルと発生するアノマリーを整理する
https://zenn.dev/mpyw/articles/rdb-transaction-isolations

上記のスライドの具体的な結論に至るまでの導入として，知識があまりない状態でも段階的に読み込んでいけるように心がけたスライドで，株式会社ゆめみの社内勉強会にて発表しました。

スライドの途中の URL などは PDF としてダウンロードするとクリックできると思います。

事前のレビュー協力者
- https://twitter.com/neko_han25
- https://twitter.com/KentarouTakeda

Transcript

1. MySQL/Postgres における トランザクション分離レベル 2022/08/19 Presented by @mpyw 2022/11/10 一部改定
2. 目次 1. そもそも排他制御ってなんだっけ？ 2. 2-Phase Locking とは？ 3. 古典的なトランザクション分離レベルとアノマリー 4. 現代的なトランザクション分離レベルへ a. 既存の問題点と新発見されたアノマリー b. MVCC および Snapshot Isolation の導入 5. MySQL/Postgres における実装 6. MySQL/Postgres におけるベストプラクティス
3. 目次 1. そもそも排他制御ってなんだっけ？ 2. 2-Phase Locking とは？ 3. 古典的なトランザクション分離レベルとアノマリー 4. 現代的なトランザクション分離レベルへ a. 既存の問題点と新発見されたアノマリー b. MVCC および Snapshot Isolation の導入 5. MySQL/Postgres における実装 6. MySQL/Postgres におけるベストプラクティス
4. 排他制御ってなんだっけ？ 排他制御（はいたせいぎょ）とは、コンピュータ・プログラムの実行におい て、複数のプロセスが利用出来る共有資源に対し、複数のプロセスからの同時 アクセスにより競合が発生する場合に、あるプロセスに資源を独占的に利用さ せている間は、他のプロセスが利用できないようにする事で整合性を保つ処理 の事をいう。
5. 排他制御が無くても正常に動く場合 今何個ある？ 1個です 1個ください どうぞ 今何個ある？ 0 個です
6. 排他制御が無いとバグる場合 今何個ある？ 1個です 1個ください どうぞ 今何個ある？ 1 個です 1個ください もう無いです
7. 排他制御で不整合を防ぐ 今何個ある？ 1個です 1個ください どうぞ 今何個ある？ 待ってね 0 個です 排他ロック取得 ロック解放 排他ロック取得待機 排他ロック取得
8. 悲観的排他制御 • 共有ロック (Shared) ◦ 読み取るためだけにロックする ◦ 読み取るために共有ロックを取る人は 複数人居てもよい ◦ 排他ロックは誰にも取らせない • 排他ロック (Exclusive) ◦ 読み書きするためにロックする ◦ 共有ロック・排他ロック問わず，他の誰にもロックは取らせない 先行＼後続 共有ロック 排他ロック 共有ロック ✅ ❌ 排他ロック ❌ ❌ バグったら嫌だから ちゃんと防いでおこう…
9. 楽観的排他制御 • そもそもあまり競合しない前提でしか使えない • 気づいた時点でそれまでの処理を 全部キャンセルできれば OK という考え方 途中でバグったことに 気づいたら中断でいいでしょｗ 今何個ある？ 1 個です 1個ください もう無いです OK，誰かの割り込み入ったんだ 最後に Postgres について 語るときに少し出てきます
10. プログラミング言語での悲観的排他制御手段の実装例 • Rust ◦ https://doc.rust-lang.org/std/sync/struct.RwLock.html • Go ◦ https://pkg.go.dev/sync#RWMutex
11. 目次 1. そもそも排他制御ってなんだっけ？ 2. 2-Phase Locking とは？ 3. 古典的なトランザクション分離レベルとアノマリー 4. 現代的なトランザクション分離レベルへ a. 既存の問題点と新発見されたアノマリー b. MVCC および Snapshot Isolation の導入 5. MySQL/Postgres における実装 6. MySQL/Postgres におけるベストプラクティス
12. 2-Phase Locking (2PL) って何？ Two Phase Lock - Qiita by @kumagi さん • トランザクションを実現する上でのロッ ク手法の 1 つ ◦ しかし，この原型は実際のデータベース 実装では殆ど使われていない • 成長相でロックを取得していく • 縮退相でロックを解放していく • ロックの解放と取得が途中で入り乱れて はならない
13. Strict 2-Phase Locking (S2PL) って何？ Two Phase Lock - Qiita by @kumagi さん • 実際にデータベースで使われている ロック手法の 1 つ • 成長相でロックを取得していく ◦ 必要なもののロックを徐々に取っていく • 縮退相＝トランザクションの終了 ◦ 終了時にロックを一気に解放する MySQL の SERIALIZABLE はこれ！
14. 2-Phase Locking の功罪 • 長所 ◦ 実装がシンプル （読み書きするやつとりあえず全部ロックしておけばいいので） • 短所 ◦ ロック範囲・ロック時間が長くなりやすい （ゆえに重い）
15. 2-Phase Locking を軽くするにはどうしたらいい？ 「全部ロックする必要ないじゃん」 「ちょっとぐらい不整合起こっていいじゃん」 で妥協する選択肢を与えよう ↓ ANSI で「トランザクション分離レベル」として仕様が策定される
16. 目次 1. そもそも排他制御ってなんだっけ？ 2. 2-Phase Locking とは？ 3. 古典的なトランザクション分離レベルとアノマリー 4. 現代的なトランザクション分離レベルへ a. 既存の問題点と新発見されたアノマリー b. MVCC および Snapshot Isolation の導入 5. MySQL/Postgres における実装 6. MySQL/Postgres におけるベストプラクティス
17. (参考) MySQL/Postgres での分離レベル宣言 SET TRANSACTION ISOLATION LEVEL READ COMMITTED; BEGIN; …. COMMIT; BEGIN; SET TRANSACTION ISOLATION LEVEL READ COMMITTED; …. COMMIT;
18. 古典的なトランザクション分離レベルの定義 (概要) • READ UNCOMMITTED ◦ 他者のコミットされていない変更まで見えてしまう (Dirty Read) • READ COMMITTED ◦ 他者のコミットされていない変更は見えない ◦ 他者のコミットされた変更は見える • REPEATABLE READ ◦ 他者のコミットされた変更はすべて見えるが， 自分のトランザクションの中で一貫性は保たれる。 同じレコードに対して繰り返される SELECT は，常に最初と同じ結果を返す (Repeatable Read) • SERIALIZABLE ◦ 複数のトランザクションが同時実行されたとしても， いずれかの順番で逐次実行した場合と結果が同じになる (直列化できる) ことが保証されている 文字通り読むと…？
19. 古典的なトランザクション分離レベルの定義 (厳密) • READ UNCOMMITTED ◦ 他者のコミットされていない変更まで見えてしまう (Dirty Read) • READ COMMITTED ◦ Dirty Read が起こらない • REPEATABLE READ ◦ Dirty Read が起こらない ◦ Non-Repeatable Read (Fuzzy Read) が起こらない • SERIALIZABLE ◦ Dirty Read が起こらない ◦ Non-Repeatable Read (Fuzzy Read) が起こらない ◦ Phantom Read が起こらない ▪ 「同一レコードに対しては同じ結果を返すが，範囲選択や集計関数で，他のトランザクションによって 作られたばかりの新規レコードを拾ってしまう」ことが起こらない 厳密には…？ • 更新は検知できる • 作成と削除は検知できない
20. よく見るやつ DBMS複数利用時の問題とトランザクションの分離を学ぼう！（ダーティーリード、ファントムリード、READ COMMITTEDなど） | ITの学び
21. 2-Phase Locking と ANSI の分離レベル定義の関連性 Isolation Levelの階層 - Qiita by @kumagi さん 「歴史上の話を調べてみると、ANSI のこの分離レベルは制定当初 2-Phase Lock が基本だった時代背景があ り、それでは遅すぎるから 2PL を緩和する方向で策定したようである。そういう観点で見るとこの4レベルか らは意図されたLockのプロトコルが透けて見える。」 Read Write READ UNCOMMITTED ロックしない S2PL READ COMMITTED ロックするが 2PL は守らない 即座に解放してよい S2PL REPEATABLE READ S2PL S2PL SERIALIZABLE S2PL + 範囲ロック S2PL + 範囲ロック
22. 目次 1. そもそも排他制御ってなんだっけ？ 2. 2-Phase Locking とは？ 3. 古典的なトランザクション分離レベルとアノマリー 4. 現代的なトランザクション分離レベルへ a. 既存の問題点と新発見されたアノマリー b. MVCC および Snapshot Isolation の導入 5. MySQL/Postgres における実装 6. MySQL/Postgres におけるベストプラクティス
23. 既存の定義では何が問題か？ • Dirty Write が許容されている ◦ 「他のトランザクションでコミットされていない書き込み内容を上書きしてしまう」というアノマリー ◦ S2PL で Write されていれば問題はないが…？ Read Write READ UNCOMMITTED ロックしない S2PL READ COMMITTED ロックするが 2PL は守らない 即座に解放してよい S2PL REPEATABLE READ S2PL S2PL SERIALIZABLE S2PL + 範囲ロック S2PL + 範囲ロック
24. 既存の定義では何が問題か？ • READ UNCOMMITTED ◦ 他者のコミットされていない変更まで見えてしまう (Dirty Read) • READ COMMITTED ◦ Dirty Read が起こらない • REPEATABLE READ ◦ Dirty Read が起こらない ◦ Non-Repeatable Read (Fuzzy Read) が起こらない • SERIALIZABLE ◦ Dirty Read が起こらない ◦ Non-Repeatable Read (Fuzzy Read) が起こらない ◦ Phantom Read が起こらない ▪ 「同一レコードに対しては同じ結果を返すが，範囲選択や集計関数で，他のトランザクションによって 作られたばかりの新規レコードを拾ってしまう」ことが起こらない ここの定義の中に 「Dirty Write が起こらない」 が入っていない ↓ 全部入れよう！ それまでは S2PL が当たり前過ぎて， Dirty Write が発生しないのは自明で， 仕様として書かれていなかったと思われる
25. 既存の定義では何が問題か？ • READ UNCOMMITTED ◦ 他者のコミットされていない変更まで見えてしまう (Dirty Read) • READ COMMITTED ◦ Dirty Read が起こらない • REPEATABLE READ ◦ Dirty Read が起こらない ◦ Non-Repeatable Read (Fuzzy Read) が起こらない • SERIALIZABLE ◦ Dirty Read が起こらない ◦ Non-Repeatable Read (Fuzzy Read) が起こらない ◦ Phantom Read が起こらない ▪ 「同一レコードに対しては同じ結果を返すが，範囲選択や集計関数で，他のトランザクションによって 作られたばかりの新規レコードを拾ってしまう」ことが起こらない 「Dirty Read が起こらない」 「Non-Repeatabl Read が起こらない」 「Phantom Read が起こらない」 では，真に直列化可能とは言えない！ 新しいアノマリーが発見されてしまった…
26. 新発見されたアノマリー MySQL/Postgres におけるトランザクション分離レベルと発生するアノマリーを整理する by @mpyw グルーピング 現象 書き込み不整合 DirtyWrite 読み取り不整合 Dirty Read Non-Repeatable Read (Fuzzy Read) Phantom Read Read Skew 更新競合 Cursor Lost Update Lost Update 直列化異常 Write Skew Observe Skew
27. 新しいアノマリーを紹介するぜ！ • Dirty Write ◦ 他のトランザクションでコミットされていない更新を上書きしてしまう • Read Skew ◦ x と y を順次読むとき，「x がこの値なら y はこの範囲にある値のはず」という論理的な前提が崩れる • Lost Update ◦ 他のトランザクションによってコミットされた更新を気づかないまま上書きしてしまう ◦ SELECT … FOR UPDATE を使えば指定した行（カーソル）単位では防げるが， これも防げないことを Cursor Lost Update と呼んでいる（若干雑な解釈） • Write Skew ◦ 「x を読んで y を更新」「y を読んで x を更新」の 2 つのトランザクションが， すれ違いざまに相手の変更前の値に依存した更新を行ってしまう • Observe Skew (Readonly Anomaly, Read Only Skew, etc…) ◦ 2 つだけであれば直列化可能であったはずの 2 つのトランザクションに 3 人目の観測者が加わることによって， 観測結果まで含めた整合性を担保しようとすると 3 つのトランザクションの順序が確定できなくなってしまう
28. 新しいアノマリーを紹介するぜ！ • Dirty Write ◦ 他のトランザクションでコミットされていない更新を上書きしてしまう • Read Skew ◦ x と y を順次読むとき，「x がこの値なら y はこの範囲にある値のはず」という論理的な前提が崩れる • Lost Update ◦ 他のトランザクションによってコミットされた更新を気づかないまま上書きしてしまう ◦ SELECT … FOR UPDATE を使えば指定した行（カーソル）単位では防げるが， これも防げないことを Cursor Lost Update と呼んでいる（若干雑な解釈） • Write Skew ◦ 「x を読んで y を更新」「y を読んで x を更新」の 2 つのトランザクションが， すれ違いざまに相手の変更前の値に依存した更新を行ってしまう • Observe Skew (Readonly Anomaly, Read Only Skew, etc…) ◦ 2 つだけであれば直列化可能であったはずの 2 つのトランザクションに 3 人目の観測者が加わることによって， 観測結果まで含めた整合性を担保しようとすると 3 つのトランザクションの順序が確定できなくなってしまう SERIALIZABLE 未満でも防げる SERIALIZABLE か 明示的 Locking Read でないと防げない 発生しない
29. 伝わらないので図だけ見て雰囲気を掴んでください！ Read Skew Lost Update いろんなAnomaly - Qiita by @kumagi R(y) が R(x) と論理的に 整合性が取れなくなって いるかもしれない T2 が T1 の W(x) を無かった ことにして上書きしてしまう
30. 伝わらないので図だけ見て雰囲気を掴んでください！ Write Skew Observe Skew いろんなAnomaly - Qiita by @kumagi お互いに更新前の 相手の値に依存している 何もわからん
31. 目次 1. そもそも排他制御ってなんだっけ？ 2. 2-Phase Locking とは？ 3. 古典的なトランザクション分離レベルとアノマリー 4. 現代的なトランザクション分離レベルへ a. 既存の問題点と新発見されたアノマリー b. MVCC および Snapshot Isolation の導入 5. MySQL/Postgres における実装 6. MySQL/Postgres におけるベストプラクティス
32. MVCC (Multi Version Concurrency Control) とは • 今までは履歴を保管せずに最新のデータだけを保管していた ◦ 同時実行に非常に弱い ◦ 至るところで排他ロックが発生する 　↓ • 過去のバージョンも全部置いておけばいいのでは？ ◦ Git の歴史みたいなイメージで OK ◦ 排他ロックが減らせるような気がする！ ◦ Multiversion Concurrency Control(MVCC) その1 - Qiita by @kumagi ▪ 詳しくはこちら
33. MVCC (Multi Version Concurrency Control) の採用 あり なし InnoDB SET READ_COMMITTED_SNAPSHOT ON; PRAGMA journal_mode=WAL; MyISAM
34. Snapshot Isolation とは • MVCC の一種 ◦ MVCC に更に A:「トランザクションを開始した時点で読み取るバージョンが確定する」 B:「更新が他のトランザクションと競合しない場合に限りコミットが成功する」 という 2 つの制約がつく ◦ Postgres の REPEATABLE READ 以上でこの動きになる （MySQL はオプション次第で A を厳密に満たすことができるが， B は全く満たしていない） (Postgres は B を厳密に満たすことができるが，A は MySQL のオプション無しの範囲でのみ満たしている) • 新たなトランザクション分離レベルとして追加された
35. Snapshot Isolation を含めた分離レベルとアノマリー 古い定義 新しい定義 Isolation Levelの階層 - Qiita by @kumagi REPEATABLE READ と完 全に役割は被らない
36. 「よく見るやつ」を新しい定義に合わせて更新するぜ！ これが…
37. 「よく見るやつ」を新しい定義に合わせて更新するぜ！ こうなる！ A Critique of ANSI SQL Isolation Levels 論文より
38. 「よく見るやつ」を新しい定義に合わせて更新するぜ！ こうなる！ A Critique of ANSI SQL Isolation Levels 論文より
39. 複雑すぎて分からない… • 理論的な定義を厳密に覚えてもあんまり意味ないです • 「MySQL だったらこう， Postgres だったらこう」 がだいたい分かっていれば十分 ◦ 「MySQL/Postgres においての分離レベル X は論文での分離レベル X+Y に相当します」 のように，厳密に 1 対 1 の関係になっていなかったりする
40. 目次 1. そもそも排他制御ってなんだっけ？ 2. 2-Phase Locking とは？ 3. 古典的なトランザクション分離レベルとアノマリー 4. 現代的なトランザクション分離レベルへ a. 既存の問題点と新発見されたアノマリー b. MVCC および Snapshot Isolation の導入 5. MySQL/Postgres における実装 6. MySQL/Postgres におけるベストプラクティス
41. ここから先は私の Zenn の記事にて MySQL/Postgres におけるトランザクション分離レベルと発生するアノマリーを整理する 以下，大雑把にまとめます
42. アノマリー
43. MVCC MVCC によって， 通常の読み取りではロック不要にしている
44. MySQL の場合の表
45. MySQL の場合の表 REPEATABLE READ が デフォルト
46. MySQL の場合の表 Locking Read/Write においては ダウングレードが発生する！ REPEATABLE READ ↓ READ COMMITTED
47. MySQL の場合の表 REPEATABLE READ で Consistent Read だけを行う場合 3 種類の読み取り不整合を防げる (MVCC のおかげ)
48. MySQL の場合の表 REPEATABLE READ で Locking Read/Write だけを行う場合 3 種類の読み取り不整合を防げる • Fuzzy Read と Read Skew を防げるのは レコードロックのおかげ • Phantom Read まで防げるのは ギャップロックのおかげ READ COMMITTED 相当に ダウングレードされていても， 全てロックしていれば問題ない
49. MySQL の場合の表 Consistent Read と Locking Read/Write の混在で これらが全て発生してしまう ダウングレードの影響を受けてしまう
50. MySQL の場合の表 Lost Update も同様に， 書き込む前の読み取りに Consistent Read が混在して いる場合は発生 ダウングレードの影響を受けてしまう
51. 分離レベルのダウングレードと混在を実感できる SQL ここで別のトランザクションが INSERT を COMMIT え？ え？ え？
52. MySQL の場合の表 SERIALIZABLE は 脳筋 S2PL
53. Postgres の場合の表
54. Postgres の場合の表 READ UNCOMMITTED がない
55. Postgres の場合の表 READ COMMITTED がデフォルト
56. Postgres の場合の表 REPEATABLE READ で 3 種類の読み取り不整合を防げる (Snapshot Isolation のおかげ) 論文の分離レベルでいう SNAPSHOT ISOLATION + REPEATABLE READ に対応
57. Postgres の場合の表 REPEATABLE READ で Lost Update を防げる (Snapshot Isolation のおかげ) 但し，楽観的な制御になっているので，エラーからのリトライが必要
58. Postgres の場合の表 SERIALIZABLE は Serializable Snapshot Isolation によって実現されている (S2PL ではない) 但し，楽観的な制御になっているので， エラーからのリトライが必要 SSI 専用の SIRead ロックは 読み取り操作でもロックを取る… というよりはただマーキングして おくだけ，に近い
59. 目次 1. そもそも排他制御ってなんだっけ？ 2. 2-Phase Locking とは？ 3. 古典的なトランザクション分離レベルとアノマリー 4. 現代的なトランザクション分離レベルへ a. 既存の問題点と新発見されたアノマリー b. MVCC および Snapshot Isolation の導入 5. MySQL/Postgres における実装 6. MySQL/Postgres におけるベストプラクティス
60. MySQL/Postgres 共通 • 基本的には READ COMMITTED をベースにした上で明示的な Locking Read を必要に応じて活用せよ。 通常は NO WAIT オプションを付与してブロッキングを回避し，即時失敗させるとよい。 • Locking Read は空振りするとレコードロックが取得できないので，その懸念がある場合は アドバイザリーロック の併用を検討するとよい。 排他制御のためだけに Redis 渋々使ってませんか？データベース単独でアドバイザリーロックできるよ！
61. MySQL • デフォルトの REPEATABLE READ から READ COMMITTED に変更して使用せよ。 • もし REPEATABLE READ のまま使用する場合， ◦ ギャップロック に注意せよ。 レコードロックの空振り で意図せず発生させてしまわないように注意。 ◦ Consistent Read と Locking Read/Write の混在 に注意せよ。 一貫性が欲しい部分でそれらを併用してはならない。 • SERIALIZABLE は並列実行性が著しく落ちるため，実用性は薄い。 • READ UNCOMMITTED の出番は基本的には無い。
62. Postgres • デフォルトの READ COMMITTED のまま使用せよ。 • 競合頻度が低い場合， REPEATABLE READ 以上で観測される 楽観的制御 の振る舞いに身を任せたほうが パフォーマンスが出る場合があると考えられる。トレードオフとして，リトライもしくはリトライを 促すための処理をアプリケーション側で書く必要となる。 ◦ 但し使いこなすには様々な知識が必要であり，一般的な Web 開発において適合するのは レアケースであるため， Web 開発においてはあまり意識しなくてもよい。
63. おわり