Upgrade to Pro — share decks privately, control downloads, hide ads and more …

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

mpyw
August 19, 2022

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

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

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

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

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

mpyw

August 19, 2022
Tweet

More Decks by mpyw

Other Decks in Programming

Transcript

  1. MySQL/Postgres における
    トランザクション分離レベル
    2022/08/19
    Presented by @mpyw
    2022/11/10 一部改定

    View full-size slide

  2. 目次
    1. そもそも排他制御ってなんだっけ?
    2. 2-Phase Locking とは?
    3. 古典的なトランザクション分離レベルとアノマリー
    4. 現代的なトランザクション分離レベルへ
    a. 既存の問題点と新発見されたアノマリー
    b. MVCC および Snapshot Isolation の導入
    5. MySQL/Postgres における実装
    6. MySQL/Postgres におけるベストプラクティス

    View full-size slide

  3. 目次
    1. そもそも排他制御ってなんだっけ?
    2. 2-Phase Locking とは?
    3. 古典的なトランザクション分離レベルとアノマリー
    4. 現代的なトランザクション分離レベルへ
    a. 既存の問題点と新発見されたアノマリー
    b. MVCC および Snapshot Isolation の導入
    5. MySQL/Postgres における実装
    6. MySQL/Postgres におけるベストプラクティス

    View full-size slide

  4. 排他制御ってなんだっけ?
    排他制御(はいたせいぎょ)とは、コンピュータ・プログラムの実行におい
    て、複数のプロセスが利用出来る共有資源に対し、複数のプロセスからの同時
    アクセスにより競合が発生する場合に、あるプロセスに資源を独占的に利用さ
    せている間は、他のプロセスが利用できないようにする事で整合性を保つ処理
    の事をいう。

    View full-size slide

  5. 排他制御が無くても正常に動く場合
    今何個ある?
    1個です
    1個ください
    どうぞ
    今何個ある?
    0 個です

    View full-size slide

  6. 排他制御が無いとバグる場合
    今何個ある?
    1個です
    1個ください
    どうぞ
    今何個ある?
    1 個です
    1個ください
    もう無いです

    View full-size slide

  7. 排他制御で不整合を防ぐ
    今何個ある?
    1個です
    1個ください
    どうぞ
    今何個ある?
    待ってね
    0 個です
    排他ロック取得
    ロック解放
    排他ロック取得待機
    排他ロック取得

    View full-size slide

  8. 悲観的排他制御
    ● 共有ロック (Shared)
    ○ 読み取るためだけにロックする
    ○ 読み取るために共有ロックを取る人は
    複数人居てもよい
    ○ 排他ロックは誰にも取らせない
    ● 排他ロック (Exclusive)
    ○ 読み書きするためにロックする
    ○ 共有ロック・排他ロック問わず,他の誰にもロックは取らせない
    先行\後続 共有ロック 排他ロック
    共有ロック ✅ ❌
    排他ロック ❌ ❌
    バグったら嫌だから
    ちゃんと防いでおこう…

    View full-size slide

  9. 楽観的排他制御
    ● そもそもあまり競合しない前提でしか使えない
    ● 気づいた時点でそれまでの処理を
    全部キャンセルできれば OK という考え方
    途中でバグったことに
    気づいたら中断でいいでしょw
    今何個ある?
    1 個です
    1個ください
    もう無いです
    OK,誰かの割り込み入ったんだ
    最後に Postgres について
    語るときに少し出てきます

    View full-size slide

  10. プログラミング言語での悲観的排他制御手段の実装例
    ● Rust
    ○ https://doc.rust-lang.org/std/sync/struct.RwLock.html
    ● Go
    ○ https://pkg.go.dev/sync#RWMutex

    View full-size slide

  11. 目次
    1. そもそも排他制御ってなんだっけ?
    2. 2-Phase Locking とは?
    3. 古典的なトランザクション分離レベルとアノマリー
    4. 現代的なトランザクション分離レベルへ
    a. 既存の問題点と新発見されたアノマリー
    b. MVCC および Snapshot Isolation の導入
    5. MySQL/Postgres における実装
    6. MySQL/Postgres におけるベストプラクティス

    View full-size slide

  12. 2-Phase Locking (2PL) って何?
    Two Phase Lock - Qiita by @kumagi さん
    ● トランザクションを実現する上でのロッ
    ク手法の 1 つ
    ○ しかし,この原型は実際のデータベース
    実装では殆ど使われていない
    ● 成長相でロックを取得していく
    ● 縮退相でロックを解放していく
    ● ロックの解放と取得が途中で入り乱れて
    はならない

    View full-size slide

  13. Strict 2-Phase Locking (S2PL) って何?
    Two Phase Lock - Qiita by @kumagi さん
    ● 実際にデータベースで使われている
    ロック手法の 1 つ
    ● 成長相でロックを取得していく
    ○ 必要なもののロックを徐々に取っていく
    ● 縮退相=トランザクションの終了
    ○ 終了時にロックを一気に解放する
    MySQL の
    SERIALIZABLE はこれ!

    View full-size slide

  14. 2-Phase Locking の功罪
    ● 長所
    ○ 実装がシンプル
    (読み書きするやつとりあえず全部ロックしておけばいいので)
    ● 短所
    ○ ロック範囲・ロック時間が長くなりやすい
    (ゆえに重い)

    View full-size slide

  15. 2-Phase Locking を軽くするにはどうしたらいい?
    「全部ロックする必要ないじゃん」
    「ちょっとぐらい不整合起こっていいじゃん」
    で妥協する選択肢を与えよう

    ANSI で「トランザクション分離レベル」として仕様が策定される

    View full-size slide

  16. 目次
    1. そもそも排他制御ってなんだっけ?
    2. 2-Phase Locking とは?
    3. 古典的なトランザクション分離レベルとアノマリー
    4. 現代的なトランザクション分離レベルへ
    a. 既存の問題点と新発見されたアノマリー
    b. MVCC および Snapshot Isolation の導入
    5. MySQL/Postgres における実装
    6. MySQL/Postgres におけるベストプラクティス

    View full-size slide

  17. (参考) MySQL/Postgres での分離レベル宣言
    SET TRANSACTION ISOLATION LEVEL
    READ COMMITTED;
    BEGIN;
    ….
    COMMIT;
    BEGIN;
    SET TRANSACTION ISOLATION LEVEL
    READ COMMITTED;
    ….
    COMMIT;

    View full-size slide

  18. 古典的なトランザクション分離レベルの定義 (概要)
    ● READ UNCOMMITTED
    ○ 他者のコミットされていない変更まで見えてしまう (Dirty Read)
    ● READ COMMITTED
    ○ 他者のコミットされていない変更は見えない
    ○ 他者のコミットされた変更は見える
    ● REPEATABLE READ
    ○ 他者のコミットされた変更はすべて見えるが,
    自分のトランザクションの中で一貫性は保たれる。
    同じレコードに対して繰り返される SELECT は,常に最初と同じ結果を返す (Repeatable Read)
    ● SERIALIZABLE
    ○ 複数のトランザクションが同時実行されたとしても,
    いずれかの順番で逐次実行した場合と結果が同じになる (直列化できる) ことが保証されている
    文字通り読むと…?

    View full-size slide

  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 が起こらない
    ■ 「同一レコードに対しては同じ結果を返すが,範囲選択や集計関数で,他のトランザクションによって
    作られたばかりの新規レコードを拾ってしまう」ことが起こらない
    厳密には…?
    ● 更新は検知できる
    ● 作成と削除は検知できない

    View full-size slide

  20. よく見るやつ
    DBMS複数利用時の問題とトランザクションの分離を学ぼう!(ダーティーリード、ファントムリード、READ COMMITTEDなど) | ITの学び

    View full-size slide

  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 + 範囲ロック

    View full-size slide

  22. 目次
    1. そもそも排他制御ってなんだっけ?
    2. 2-Phase Locking とは?
    3. 古典的なトランザクション分離レベルとアノマリー
    4. 現代的なトランザクション分離レベルへ
    a. 既存の問題点と新発見されたアノマリー
    b. MVCC および Snapshot Isolation の導入
    5. MySQL/Postgres における実装
    6. MySQL/Postgres におけるベストプラクティス

    View full-size slide

  23. 既存の定義では何が問題か?
    ● Dirty Write が許容されている
    ○ 「他のトランザクションでコミットされていない書き込み内容を上書きしてしまう」というアノマリー
    ○ S2PL で Write されていれば問題はないが…?
    Read Write
    READ UNCOMMITTED ロックしない S2PL
    READ COMMITTED ロックするが 2PL は守らない
    即座に解放してよい
    S2PL
    REPEATABLE READ S2PL S2PL
    SERIALIZABLE S2PL + 範囲ロック S2PL + 範囲ロック

    View full-size slide

  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 が発生しないのは自明で,
    仕様として書かれていなかったと思われる

    View full-size slide

  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 が起こらない」
    では,真に直列化可能とは言えない!
    新しいアノマリーが発見されてしまった…

    View full-size slide

  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

    View full-size slide

  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 つのトランザクションの順序が確定できなくなってしまう

    View full-size slide

  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 でないと防げない
    発生しない

    View full-size slide

  29. 伝わらないので図だけ見て雰囲気を掴んでください!
    Read Skew Lost Update
    いろんなAnomaly - Qiita by @kumagi
    R(y) が R(x) と論理的に
    整合性が取れなくなって
    いるかもしれない
    T2 が T1 の W(x) を無かった
    ことにして上書きしてしまう

    View full-size slide

  30. 伝わらないので図だけ見て雰囲気を掴んでください!
    Write Skew Observe Skew
    いろんなAnomaly - Qiita by @kumagi
    お互いに更新前の
    相手の値に依存している
    何もわからん

    View full-size slide

  31. 目次
    1. そもそも排他制御ってなんだっけ?
    2. 2-Phase Locking とは?
    3. 古典的なトランザクション分離レベルとアノマリー
    4. 現代的なトランザクション分離レベルへ
    a. 既存の問題点と新発見されたアノマリー
    b. MVCC および Snapshot Isolation の導入
    5. MySQL/Postgres における実装
    6. MySQL/Postgres におけるベストプラクティス

    View full-size slide

  32. MVCC (Multi Version Concurrency Control) とは
    ● 今までは履歴を保管せずに最新のデータだけを保管していた
    ○ 同時実行に非常に弱い
    ○ 至るところで排他ロックが発生する
     ↓
    ● 過去のバージョンも全部置いておけばいいのでは?
    ○ Git の歴史みたいなイメージで OK
    ○ 排他ロックが減らせるような気がする!
    ○ Multiversion Concurrency Control(MVCC) その1 - Qiita by @kumagi
    ■ 詳しくはこちら

    View full-size slide

  33. MVCC (Multi Version Concurrency Control) の採用
    あり なし
    InnoDB
    SET READ_COMMITTED_SNAPSHOT ON;
    PRAGMA journal_mode=WAL;
    MyISAM

    View full-size slide

  34. Snapshot Isolation とは
    ● MVCC の一種
    ○ MVCC に更に
    A:「トランザクションを開始した時点で読み取るバージョンが確定する」
    B:「更新が他のトランザクションと競合しない場合に限りコミットが成功する」
    という 2 つの制約がつく
    ○ Postgres の REPEATABLE READ 以上でこの動きになる
    (MySQL はオプション次第で A を厳密に満たすことができるが, B は全く満たしていない)
    (Postgres は B を厳密に満たすことができるが,A は MySQL のオプション無しの範囲でのみ満たしている)
    ● 新たなトランザクション分離レベルとして追加された

    View full-size slide

  35. Snapshot Isolation を含めた分離レベルとアノマリー
    古い定義 新しい定義
    Isolation Levelの階層 - Qiita by @kumagi
    REPEATABLE READ と完
    全に役割は被らない

    View full-size slide

  36. 「よく見るやつ」を新しい定義に合わせて更新するぜ!
    これが…

    View full-size slide

  37. 「よく見るやつ」を新しい定義に合わせて更新するぜ!
    こうなる!
    A Critique of ANSI SQL Isolation Levels 論文より

    View full-size slide

  38. 「よく見るやつ」を新しい定義に合わせて更新するぜ!
    こうなる!
    A Critique of ANSI SQL Isolation Levels 論文より

    View full-size slide

  39. 複雑すぎて分からない…
    ● 理論的な定義を厳密に覚えてもあんまり意味ないです
    ● 「MySQL だったらこう, Postgres だったらこう」
    がだいたい分かっていれば十分
    ○ 「MySQL/Postgres においての分離レベル X は論文での分離レベル X+Y に相当します」
    のように,厳密に 1 対 1 の関係になっていなかったりする

    View full-size slide

  40. 目次
    1. そもそも排他制御ってなんだっけ?
    2. 2-Phase Locking とは?
    3. 古典的なトランザクション分離レベルとアノマリー
    4. 現代的なトランザクション分離レベルへ
    a. 既存の問題点と新発見されたアノマリー
    b. MVCC および Snapshot Isolation の導入
    5. MySQL/Postgres における実装
    6. MySQL/Postgres におけるベストプラクティス

    View full-size slide

  41. ここから先は私の Zenn の記事にて
    MySQL/Postgres におけるトランザクション分離レベルと発生するアノマリーを整理する
    以下,大雑把にまとめます

    View full-size slide

  42. アノマリー

    View full-size slide

  43. MVCC
    MVCC によって,
    通常の読み取りではロック不要にしている

    View full-size slide

  44. MySQL の場合の表

    View full-size slide

  45. MySQL の場合の表
    REPEATABLE READ が
    デフォルト

    View full-size slide

  46. MySQL の場合の表
    Locking Read/Write においては
    ダウングレードが発生する!
    REPEATABLE READ

    READ COMMITTED

    View full-size slide

  47. MySQL の場合の表
    REPEATABLE READ で
    Consistent Read だけを行う場合
    3 種類の読み取り不整合を防げる
    (MVCC のおかげ)

    View full-size slide

  48. MySQL の場合の表
    REPEATABLE READ で
    Locking Read/Write だけを行う場合
    3 種類の読み取り不整合を防げる
    ● Fuzzy Read と Read Skew を防げるのは
    レコードロックのおかげ
    ● Phantom Read まで防げるのは
    ギャップロックのおかげ
    READ COMMITTED 相当に
    ダウングレードされていても,
    全てロックしていれば問題ない

    View full-size slide

  49. MySQL の場合の表
    Consistent Read と
    Locking Read/Write の混在で
    これらが全て発生してしまう
    ダウングレードの影響を受けてしまう

    View full-size slide

  50. MySQL の場合の表
    Lost Update も同様に,
    書き込む前の読み取りに
    Consistent Read が混在して
    いる場合は発生
    ダウングレードの影響を受けてしまう

    View full-size slide

  51. 分離レベルのダウングレードと混在を実感できる SQL
    ここで別のトランザクションが INSERT を COMMIT
    え?
    え? え?

    View full-size slide

  52. MySQL の場合の表
    SERIALIZABLE は
    脳筋 S2PL

    View full-size slide

  53. Postgres の場合の表

    View full-size slide

  54. Postgres の場合の表
    READ UNCOMMITTED がない

    View full-size slide

  55. Postgres の場合の表
    READ COMMITTED がデフォルト

    View full-size slide

  56. Postgres の場合の表
    REPEATABLE READ で
    3 種類の読み取り不整合を防げる
    (Snapshot Isolation のおかげ)
    論文の分離レベルでいう
    SNAPSHOT ISOLATION + REPEATABLE READ
    に対応

    View full-size slide

  57. Postgres の場合の表
    REPEATABLE READ で Lost Update を防げる
    (Snapshot Isolation のおかげ)
    但し,楽観的な制御になっているので,エラーからのリトライが必要

    View full-size slide

  58. Postgres の場合の表
    SERIALIZABLE は
    Serializable Snapshot Isolation
    によって実現されている
    (S2PL ではない)
    但し,楽観的な制御になっているので,
    エラーからのリトライが必要
    SSI 専用の SIRead ロックは
    読み取り操作でもロックを取る…
    というよりはただマーキングして
    おくだけ,に近い

    View full-size slide

  59. 目次
    1. そもそも排他制御ってなんだっけ?
    2. 2-Phase Locking とは?
    3. 古典的なトランザクション分離レベルとアノマリー
    4. 現代的なトランザクション分離レベルへ
    a. 既存の問題点と新発見されたアノマリー
    b. MVCC および Snapshot Isolation の導入
    5. MySQL/Postgres における実装
    6. MySQL/Postgres におけるベストプラクティス

    View full-size slide

  60. MySQL/Postgres 共通
    ● 基本的には READ COMMITTED をベースにした上で明示的な Locking Read を必要に応じて活用せよ。
    通常は NO WAIT オプションを付与してブロッキングを回避し,即時失敗させるとよい。
    ● Locking Read は空振りするとレコードロックが取得できないので,その懸念がある場合は
    アドバイザリーロック の併用を検討するとよい。
    排他制御のためだけに Redis 渋々使ってませんか?データベース単独でアドバイザリーロックできるよ!

    View full-size slide

  61. MySQL
    ● デフォルトの REPEATABLE READ から READ COMMITTED に変更して使用せよ。
    ● もし REPEATABLE READ のまま使用する場合,
    ○ ギャップロック に注意せよ。
    レコードロックの空振り で意図せず発生させてしまわないように注意。
    ○ Consistent Read と Locking Read/Write の混在 に注意せよ。
    一貫性が欲しい部分でそれらを併用してはならない。
    ● SERIALIZABLE は並列実行性が著しく落ちるため,実用性は薄い。
    ● READ UNCOMMITTED の出番は基本的には無い。

    View full-size slide

  62. Postgres
    ● デフォルトの READ COMMITTED のまま使用せよ。
    ● 競合頻度が低い場合, REPEATABLE READ 以上で観測される 楽観的制御 の振る舞いに身を任せたほうが
    パフォーマンスが出る場合があると考えられる。トレードオフとして,リトライもしくはリトライを
    促すための処理をアプリケーション側で書く必要となる。
    ○ 但し使いこなすには様々な知識が必要であり,一般的な Web 開発において適合するのは
    レアケースであるため, Web 開発においてはあまり意識しなくてもよい。

    View full-size slide