Tweet
Tweet

Slide 1

Slide 1 text

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

Slide 2

Slide 2 text

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

Slide 3

Slide 3 text

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

Slide 4

Slide 4 text

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

Slide 5

Slide 5 text

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

Slide 6

Slide 6 text

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

Slide 7

Slide 7 text

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

Slide 8

Slide 8 text

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

Slide 9

Slide 9 text

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

Slide 10

Slide 10 text

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

Slide 11

Slide 11 text

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

Slide 12

Slide 12 text

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

Slide 13

Slide 13 text

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

Slide 14

Slide 14 text

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

Slide 15

Slide 15 text

2-Phase Locking を軽くするにはどうしたらいい？ 「全部ロックする必要ないじゃん」 「ちょっとぐらい不整合起こっていいじゃん」 で妥協する選択肢を与えよう ↓ ANSI で「トランザクション分離レベル」として仕様が策定される

Slide 16

Slide 16 text

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

Slide 17

Slide 17 text

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

Slide 18

Slide 18 text

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

Slide 19

Slide 19 text

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

Slide 20

Slide 20 text

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

Slide 21

Slide 21 text

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

Slide 22

Slide 22 text

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

Slide 23

Slide 23 text

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

Slide 24

Slide 24 text

既存の定義では何が問題か？ ● 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 が発生しないのは自明で， 仕様として書かれていなかったと思われる

Slide 25

Slide 25 text

既存の定義では何が問題か？ ● 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 が起こらない」 では，真に直列化可能とは言えない！ 新しいアノマリーが発見されてしまった…

Slide 26

Slide 26 text

新発見されたアノマリー 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

Slide 27

Slide 27 text

新しいアノマリーを紹介するぜ！ ● 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 つのトランザクションの順序が確定できなくなってしまう

Slide 28

Slide 28 text

新しいアノマリーを紹介するぜ！ ● 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 でないと防げない 発生しない

Slide 29

Slide 29 text

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

Slide 30

Slide 30 text

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

Slide 31

Slide 31 text

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

Slide 32

Slide 32 text

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

Slide 33

Slide 33 text

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

Slide 34

Slide 34 text

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

Slide 35

Slide 35 text

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

Slide 36

Slide 36 text

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

Slide 37

Slide 37 text

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

Slide 38

Slide 38 text

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

Slide 39

Slide 39 text

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

Slide 40

Slide 40 text

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

Slide 41

Slide 41 text

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

Slide 42

Slide 42 text

アノマリー

Slide 43

Slide 43 text

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

Slide 44

Slide 44 text

MySQL の場合の表

Slide 45

Slide 45 text

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

Slide 46

Slide 46 text

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

Slide 47

Slide 47 text

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

Slide 48

Slide 48 text

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

Slide 49

Slide 49 text

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

Slide 50

Slide 50 text

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

Slide 51

Slide 51 text

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

Slide 52

Slide 52 text

MySQL の場合の表 SERIALIZABLE は 脳筋 S2PL

Slide 53

Slide 53 text

Postgres の場合の表

Slide 54

Slide 54 text

Postgres の場合の表 READ UNCOMMITTED がない

Slide 55

Slide 55 text

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

Slide 56

Slide 56 text

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

Slide 57

Slide 57 text

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

Slide 58

Slide 58 text

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

Slide 59

Slide 59 text

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

Slide 60

Slide 60 text

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

Slide 61

Slide 61 text

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

Slide 62

Slide 62 text

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

Slide 63

Slide 63 text

おわり