↓ メインコンテンツへスキップ
NineKoo9’s story
  1. Posts/

[MySQL] 同時実行制御まとめ — MVCC、2PL、Locking Read、Next-Key Lock

·15 分
MySQL InnoDB MVCC 2PL Isolation Level Database
NineKoo9
著者
NineKoo9
目次

このドキュメントは、疑問を持っていた点をAIエージェントと対話しながら整理したメモです。InnoDBで通常のSELECTとlocking readの違い、MVCCと2PLの関係、next-key lockとundo/redoの役割を整理します。

目次
#

  1. MVCC
  2. 2PL
  3. MVCCと2PLの関係
  4. Isolation Levelの深掘り比較
  5. Non-Repeatable Read vs Phantom Read
  6. Locking ReadとNext-Key Lock
  7. Steal/No-Force方針とRedo/Undo Logの関係
  8. 核心まとめ

1. MVCC
#

概念
#

MVCCはデータの複数のバージョンを活用して、読み取りと書き込みが互いを不必要にブロックしないようにする方式だ。

InnoDBの通常のSELECTはREAD COMMITTEDとREPEATABLE READでデフォルトでconsistent nonlocking readとして処理される。つまり通常は現在の最新値をそのまま読むのではなく、特定の時点のスナップショットを基準に見えるバージョンを読む。

InnoDBの実装 - 隠しカラムとundo log
#

MySQL公式ドキュメントを基準にInnoDBは各行に内部フィールドを維持する。

隠しカラム役割
DB_TRX_IDこの行を最後にINSERTまたはUPDATEしたトランザクションID
DB_ROLL_PTRrollback segmentのundo log recordを指すポインタ
DB_ROW_ID内部row id
現在のrow
  DB_TRX_ID = 110
  DB_ROLL_PTR ──▶ undo log record
                     以前のバージョン情報

核心は「行を丸ごと複数コピーしておく」というよりも:

  • 現在のrowには最新バージョンがあり
  • 必要ならDB_ROLL_PTRをたどってundo情報を使ってより古いバージョンを再構成する

と理解する方が正確だ。

公式ドキュメントは削除も内部的にはdelete markが付くupdateのように扱うと説明している。

Read View
#

Read Viewは「何が見えて何が見えないか」を決定するスナップショットメタデータだ。

厳密には活性トランザクションのリストとvisibility boundaryを基準に判断するため、よく言われる「自分のTRX_IDより小さいものはすべて見える」程度に単純化するのは不十分だ。ただし概念的には以下のように理解してよい。

  • スナップショット時点より以前にコミットされたバージョンは見える
  • その時点にまだ活性中であるか、以降にコミットされたバージョンは見えない
  • 現在のrowが見えない場合、InnoDBは必要に応じてundoを使って古いバージョンを再構成する
  • 例外的に、現在のトランザクションがearlier statementsで直接作った変更は現在のトランザクションから見える

Isolation Levelごとのスナップショット生成タイミング
#

Isolation Level通常のSELECT基準の動作
READ UNCOMMITTEDnonlocking readだが一貫したスナップショット読み取りではない
READ COMMITTED各consistent readごとにfresh snapshotを生成
REPEATABLE READ最初のconsistent readが作ったsnapshotを再利用
SERIALIZABLEautocommit=0の場合、plain SELECTが事実上FOR SHAREとして処理される

MVCCが与える効果
#

READ COMMITTEDとREPEATABLE READの通常のSELECTでは以下の性質が核心だ。

  • コミットされていないか後でコミットされた変更は見えない
  • consistent readは読み取り中にロックを取らないため読み取り-書き込みの競合を大幅に減らす
  • REPEATABLE READでは同じトランザクション内のrepeated plain SELECTが互いに一貫した結果を見る

2. 2PL
#

概念
#

2PL（Two-Phase Locking）はロック取得区間とロック解放区間を分離する古典的な同時実行制御モデルだ。

Growing Phase          Shrinking Phase
（ロック取得のみ可能）    （ロック解放のみ可能）
──────────────────────────────────────────
  Lock A
  Lock B
  Lock C
               ──▶ Unlock A
                   Unlock B
                   Unlock C
  • Growing Phase: 必要なロックを取得するだけで解放しない
  • Shrinking Phase: ロックを解放し始めたら新しいロックを取得しない

InnoDBではどう見るべきか
#

MySQL公式ドキュメントはInnoDBのトランザクションモデルがmulti-versioning databaseの利点とtraditional two-phase lockingを組み合わせようとすると説明している。

そのためInnoDB全体を即「Strict 2PLデータベース」と呼ぶのは過剰だが、locking readと書き込み区間のロック動作だけ取り出してみると、strict 2PLに近い性格がある。

例えばFOR UPDATE、FOR SHARE、UPDATE、DELETEはロックを取ってトランザクション終了時点まで維持する。

BEGIN;

SELECT * FROM orders WHERE id = 1 FOR UPDATE;
UPDATE orders SET status = 'DONE' WHERE id = 1;

COMMIT;

公式ドキュメントはFOR SHAREとFOR UPDATEで取ったロックがCOMMITまたはROLLBACK時に解放されると明示している。

InnoDBの主なロック種類
#

ロック種類説明
Shared Lock（S）読み取り保護用ロック
Exclusive Lock（X）修正/削除用ロック
Intention Lock（IS / IX）テーブルレベルの意図表示
Gap Lockインデックスレコード間のgapに対するロック
Next-Key Lockindex-record lock + gap lock
Insert Intention LockINSERT直前のgapに取られる意図ロック

ロックの実際の範囲は分離レベルと検索条件、使用したインデックスによって異なる。

Deadlock
#

ロックベースの同時実行制御の代表的な副作用はデッドロックだ。

T1: Lock A → Lock B 試みる
T2: Lock B → Lock A 試みる

InnoDBはdeadlock detectionによってこのような循環待機を検出し、1つのトランザクションをロールバックして問題を解決する。

3. MVCCと2PLの関係
#

InnoDBはどちらか一方だけを使うエンジンではなく、読み取りパスとロックパスを分離して一緒に使用する。

処理使用方式
通常のSELECT（READ COMMITTED、REPEATABLE READ）MVCCベースのconsistent nonlocking read
SELECT ... FOR UPDATE / SELECT ... FOR SHARElocking read、最新状態 + ロック
UPDATE / DELETE最新状態 + ロック
INSERTinsert intention lock + record lock

したがってInnoDBを最も無理なく説明する方式は以下だ。

  • 通常の読み取りはMVCC
  • locking readとDMLはロックベース
  • ロックベースの区間はstrict 2PLに近い性格

公式ドキュメントがREPEATABLE READの中でnonlocking SELECTとlocking statementを混在させることを推奨しない理由もここにある。1つのトランザクション内にsnapshot worldとcurrent worldが共存するからだ。

4. Isolation Levelの深掘り比較
#

Read Committed - SELECTを実行したその瞬間を基準に
#

T1: BEGIN
T1: (t=1) SELECT ...  -> snapshot A 生成

  T2: UPDATE ... ; COMMIT  (t=2)

T1: (t=3) SELECT ...  -> snapshot B 生成
                         -> T2の変更が反映される場合がある

ポイントは単純だ。

  • 各consistent readごとに新しいsnapshotを作る
  • 同じトランザクション内でもrepeated readの結果が変わりうる
  • locking read / UPDATE / DELETEは通常gapではなくindex recordのみロックする
  • gap lockingは主にforeign key checkとduplicate key checkでのみ使用される

つまりREAD COMMITTEDでは、rangeベースのlocking readでphantom rowの問題が残りうる。

Repeatable Read - 最初のconsistent readを基準に
#

T1: BEGIN
T1: (t=1) SELECT ...  -> snapshot A 生成

  T2: UPDATE ... ; COMMIT  (t=2)

T1: (t=3) SELECT ...  -> snapshot A 再利用
                         -> plain SELECTの結果は一貫している

ポイントは以下のとおりだ。

  • 最初のconsistent readが作ったsnapshotを再利用する
  • 同じトランザクション内のplain SELECTは互いに一貫した結果を見る
  • range条件のlocking read、UPDATE、DELETEはgap lockまたはnext-key lockを使う場合がある

ただしここでも注意すべき点がある。

  • plain SELECTはread viewベースのsnapshotを見る
  • locking readとDMLはlatest stateを基準にロックを使用する

そのため同じトランザクション内でnonlocking SELECTとlocking statementを混在させると解釈が難しくなる。

Serializable - 「MVCC完全放棄」と断定するのは過剰
#

元文のようにSERIALIZABLEを「MVCCを完全に捨てる」と書くのは誇張だ。

MySQL 8.4公式ドキュメントはSERIALIZABLEを**REPEATABLE READと似ているが**、autocommitがオフのときにplain SELECTを暗黙的にSELECT ... FOR SHAREに変えると説明している。

つまり:

  • 手動トランザクションではplain SELECTもlocking readのように動作する
  • しかしautocommitがオンであればplain SELECTは自分自身が1つのread-only transactionなので、consistent nonlocking readとして直列化できる

したがってより正確な理解はこうだ。

  • SERIALIZABLE = 「常にMVCC完全放棄」
  • ではなく
  • 「RRよりも厳格で、特にautocommit offのplain SELECTはlocking readのように扱う」

Isolation Level全体比較
#

Levelplain SELECTlocking read / DML特徴
READ UNCOMMITTEDnonlocking read、ただし一貫していない概ねRCと似たlockingdirty readが可能
READ COMMITTED各consistent readごとにfresh snapshotgap lockingをほぼ無効化repeated readが不安定
REPEATABLE READ最初のconsistent read snapshotを再利用range scan時にgap/next-key lockを使用可能InnoDBのデフォルト値
SERIALIZABLEautocommit offならplain SELECTが事実上FOR SHARE最も保守的なlocking同時実行性が低下する可能性

5. Non-Repeatable Read vs Phantom Read
#

共通前提 - plain SELECTとlocking readを区別しなければならない
#

この2つの異常現象は教科書的には似て見えるが、InnoDBでは通常のconsistent readかlocking readかによって説明が異なる。

5-1. Non-Repeatable Read - updateされた既存の行
#

T1: BEGIN
T1: SELECT * FROM users WHERE id = 1  -> 'Alice'

  T2: UPDATE users SET name='Bob' WHERE id=1;
  T2: COMMIT

T1: SELECT * FROM users WHERE id = 1  -> ?

現在のrowがすでに変更されていても、REPEATABLE READのplain SELECTはsnapshotの基準に合う古いバージョンを探す必要がある場合がある。

[現在のRow]
  id=1, name='Bob'
  DB_TRX_ID = 110
  DB_ROLL_PTR ──────────▶ [Undo Log]
                              id=1, name='Alice'
  • READ COMMITTEDでは2番目のSELECTが新しいsnapshotを取るのでBobが見える
  • REPEATABLE READでは最初のsnapshotを再利用するので、update undoを使って古いバージョンを再構成できる

つまり既存の行のupdateは、update undoチェーンが重要なケースだ。

5-2. Phantom Read - 範囲内に新しい行が現れる場合
#

T1: BEGIN
T1: SELECT * FROM users WHERE age > 20

  T2: INSERT INTO users VALUES (3, 'Charlie', 28);
  T2: COMMIT

T1: SELECT * FROM users WHERE age > 20  -> ?

ここで注意すべき点は以下の2つだ。

  1. REPEATABLE READの**plain SELECT**は同じsnapshotを再利用するため、repeated readの結果セットが安定している。
  2. MySQL公式ドキュメントがphantom problemを説明するときに使う代表的な例は**SELECT ... FOR UPDATEのようなlocking read**だ。

つまり「phantomはinsertされた行にundo logがないから発生する」と説明するのは不正確だ。

より正確に言えば:

  • 新しくinsertされた行は自分のsnapshot以降に生まれたrowなら見えない
  • insert undoはrollback用途であり、commit後に捨てられる
  • consistent readで過去バージョンの再構成に核心的に使われるのはupdate undoだ
  • locking readのphantom防止はnext-key lockが担当する

5-3. 2つの違いの核心比較
#

区分Non-Repeatable ReadPhantom Read
観察対象同じrowの値が変わる同じpredicateの結果セットが変わる
代表的な原因既存の行のUPDATE範囲内に新しい行のINSERTまたは既存の行の移動
REPEATABLE READのplain SELECTsnapshot + update undoで安定同じsnapshotを再利用するため結果セットが安定
locking readでの防御最新状態に対してrow lockを使用range scanにnext-key/gap lockを使用

核心はこうだ。

  • plain SELECTの反復可能性はsnapshotが担当する
  • locking readのphantom防止はnext-key lockが担当する

6. Locking ReadとNext-Key Lock
#

Locking Readは最新状態とロックを一緒に使う
#

SELECT ... FOR UPDATEとSELECT ... FOR SHAREはplain SELECTとは異なるlocking readだ。

  • 最新状態を基準に読む
  • 検索過程で出会ったindex recordsにロックをかける
  • トランザクション終了まではロックを維持する

公式ドキュメントはold versionはロックできず、old versionはundo logを適用してメモリ上で再構成すると説明している。

またlocking readは明示的なトランザクション内で使用しなければならない。ドキュメントもSTART TRANSACTIONまたはautocommit=0の状態を前提に説明している。

Next-Key Lockが必要な理由
#

MySQL公式ドキュメントのphantomの例は以下のとおりだ。

SELECT * FROM child WHERE id > 100 FOR UPDATE;

インデックスに90、102のみあるとしよう。

T1: SELECT * FROM child WHERE id > 100 FOR UPDATE;
T2: INSERT INTO child VALUES (101);  -- gapがロックされていないとphantomが可能

このとき単純なrow lockだけでは(90, 102)の間に101が新しく入ってくることを防げない。そこでInnoDBはnext-key lockingを使用する。

Next-Key Lockの意味
#

next-key lockは:

  • index-record lock
    • そのレコードの前のgap lock

の組み合わせだ。

(90, 102]   <- 102 record + その前のgap
(102, +∞)   <- 最後のrecordの後ろのgap

公式ドキュメントはデフォルトのREPEATABLE READでInnoDBがsearchとindex scanにnext-key lockを使ってphantom rowを防ぐと説明している。

逆にREAD COMMITTEDではgap lockingがほぼ無効化されるため、range条件のlocking readではphantom rowの問題が再び発生する可能性がある。

7. Steal/No-Force方針とRedo/Undo Logの関係
#

背景 - Buffer Pool、Dirty Page、Checkpoint
#

InnoDBはtableとindexデータをメモリのBuffer Poolにキャッシュする。修正されたページはすぐにデータファイルに書き込まれない場合があり、このようなページをdirty pageと呼ぶ。

[Disk]  ──読み取り──▶  [Buffer Pool]
                           └─ dirty page
                                └─ background flush / checkpoint

公式ドキュメントは:

  • dirty pageはバックグラウンドで後でflushされる場合があり
  • page cleanerがこれを担当し
  • checkpointはfuzzy checkpointingで動作してバッファプール全体を一度にflushする必要がないと説明している

つまりcommitとデータファイルflushのタイミングは常に1:1で対応しているわけではない。

Redo Log
#

MySQL 8.4公式ドキュメントでredo logは:

  • disk-based data structure
  • crash recoveryのときデータファイルに最後まで反映できなかった変更を補正するための構造

として説明される。

つまり予期しない終了前にデータファイルの更新が完了しなかった変更は、再起動時にredoをreplayして復元する。

Undo Log
#

undo log recordはclustered index recordの最新変更を元に戻すための情報を持つ。

公式ドキュメントは以下を明確に区分している。

  • insert undo: rollbackにのみ必要でcommit後に捨てられる
  • update undo: rollbackだけでなくconsistent readにも使われ、どのsnapshotも必要としなくなった時のみ捨てられる

したがってundo logの役割は2つある。

  • rollbackのための復元情報
  • consistent readのための過去バージョン再構成情報

Steal/No-Forceという表現は解釈だ
#

ここで元文のSteal + No-Forceの説明は教科書的な解釈としてはかなり有用だが、MySQL公式ドキュメントがInnoDBをその用語で直接規定しているわけではない。

ただし公式ドキュメントが示している事実をまとめると:

  • dirty pageはバックグラウンドで後でflushされる場合があり
  • redoはcrash recoveryに必要であり
  • undoはrollbackとconsistent readに必要だ

という点から、理論的にはsteal/no-forceに近い運営モデルとして理解できると言う程度は可能だ。

つまりこの部分は以下のように表現する方が安全だ。

  • 「InnoDBは公式ドキュメント上Steal + No-Forceと規定される」
  • よりも
  • 「公式ドキュメントが説明するflush/recoveryの動作を教科書の用語で読むと、steal/no-forceに近く理解できる」

8. 核心まとめ
#

RC/RRのplain SELECT
  -> MVCCベースのconsistent nonlocking read

READ COMMITTED
  -> consistent readごとにfresh snapshot

REPEATABLE READ
  -> 最初のconsistent read snapshotを再利用

FOR UPDATE / FOR SHARE / UPDATE / DELETE
  -> 最新状態 + ロック
  -> lockingパートはstrict 2PLに近い

phantom防止
  -> plain SELECTの反復可能性はsnapshotが担当
  -> locking readのphantom防止はnext-key lockが担当

SERIALIZABLE
  -> RRよりも厳格
  -> autocommit offのplain SELECTは事実上FOR SHAREのように動作

redo / undo
  -> redoはcrash recovery replay
  -> undoはrollback + old-version reconstruction

一言でまとめるとこうだ。

InnoDBは読み取りをすべてロックで処理するエンジンでもなく、すべてMVCCのみを使うエンジンでもない。plain SELECTはsnapshotを読み、locking readと書き込み処理は最新状態を基準にロックを使用する。

参考資料
#

← GCP Docker デプロイ & 運用ガイド IP、MAC、ARP、TCP/UDP、NAT/PATは実際の通信でどのようにつながるか？ →