Posted inデータベース

ロックの限界を超える：PostgreSQLとMySQLにおけるMVCC（多版型同時実行制御）実践ガイド

Database tutorial - IT technology blog
Database tutorial - IT technology blog

データベースロックの悪夢

10,000人の同時ユーザーが購入ボタンを押すフラッシュセールを想像してみてください。在庫サービスが必死に数量を更新する一方で、何千人もの買い物客が在庫を確認しています。古いデータベースアーキテクチャでは、データの整合性を保つ唯一の方法は強力なロック（Locking）でした。トランザクションAが特定の行を読み取っている間、トランザクションBはただ待つしかありません。これは「悲観的ロック（Pessimistic Locking）」と呼ばれます。機能はしますが、拡張性は皆無です。

私はかつて、行レベルロックが常態化していたレガシーシステムを引き継いだことがあります。トラフィックが急増すると、レイテンシは50msから5秒に跳ね上がりました。データベースのCPUやRAMが不足していたわけではありません。ただロックが解除されるのを待って、アイドル状態になっていただけでした。MVCC（Multi-Version Concurrency Control：多版型同時実行制御）はこれを解決します。大規模プロジェクトでPostgreSQLとMySQLのMVCC戦略を実装してきた経験から、内部の違いを理解することが、高負荷下でも高速に動作し続けるシステムを構築する秘訣であると分かりました。

核心となる対立：読み取り vs 書き込み

読み取りと書き込みが互いに干渉し合うと、データベースの並行性は損なわれます。高いパフォーマンスを実現するには、以下の条件を満たすシステムが必要です。

  • 複数のユーザーが同時に同じデータを読み取れること.
  • 読み取りが書き込みをブロックしないこと。
  • 書き込みが読み取りをブロックしないこと。

従来のロックでは、データを単一の静的なスナップショットとして扱います。値を変更する場合、完了するまで他のユーザーからその値を隠す必要があります。MVCCは異なるアプローチをとります。同じ行の複数のバージョンを同時に存在させるのです。データを上書きする代わりに、データベースは新しいバージョンを作成します。各トランザクションは、クエリが開始された瞬間のデータのプライベートな「スナップショット」を参照します。

PostgreSQL vs MySQL：同じゴールへの2つの道

どちらのエンジンもMVCCを使用していますが、内部メカニズムは大きく異なります。適切な方を選択し、あるいは現在の設定をチューニングするには、内部で何が起きているかを知る必要があります。

PostgreSQL：テーブル内バージョン管理アプローチ

PostgreSQLは、行のすべてのバージョンをメインのデータファイル内に直接保存します。各行には、xmin（その行を作成したトランザクション）とxmax（その行を削除または置換したトランザクション）という隠しメタデータが含まれています。

-- Postgresの隠しMVCCメタデータを確認する
SELECT ctid, xmin, xmax, * FROM users WHERE id = 1;

行を更新するとき、Postgresは古いデータには触れません。xmaxを使用して古いバージョンを「期限切れ」としてマークし、完全に新しい行を挿入します。これにより書き込みは非常に高速になります。しかし、これは「肥満化（Bloat）」を引き起こします。不要なタプル（Dead Tuples）のクリーンアップを行うVACUUMプロセスが追いつかず、1GBのテーブルが5GBに膨れ上がるのを何度も見てきました。定期的なクリーンアップがなければ、最終的にパフォーマンスは急落します。

MySQL (InnoDB)：Undoログアプローチ

MySQL (InnoDB)は逆のアプローチをとります。メインテーブルには行の最新バージョンのみを保持します。MVCCを提供するために、古いデータは**Undoログ（Undo Log）**と呼ばれる別の構造に移動されます。各行には、そのログに保存されている以前の状態へのポインタが含まれています。

トランザクションが古いバージョンのデータを必要とする場合、InnoDBはそれらの Undoレコードを使用してオンザフライでデータを再構成します。これにより、Postgresで見られるようなテーブルの肥満化を防ぐことができます。トレードオフは何でしょうか？ 長時間実行されるトランザクションがあると、Undoログのサイズが爆発的に増加し、長いバージョンチェーンをたどる必要があるため、システム全体の速度が低下する可能性があります。

分離レベル：データに不整合が生じる場所

MVCCは万能薬ではありません。**トランザクション分離レベル（Transaction Isolation Level）**によって、どのような異常（アノマリー）に直面するかが決まります。特に「ファントムリード（Phantom Read）」と「書き込みスキュー（Write Skew）」の2つは、開発者が陥りやすい問題です。

1. ファントムリード

ファントムリードは、1つのトランザクション内で同じクエリを2回実行した際、別のユーザーがデータを挿入したために2回目に新しい行が見つかる現象です。MySQLのデフォルトのREPEATABLE READは、**ネクストキーロック（Gap Locking）**を使用して行間の「隙間」をロックし、これらのファントムを防ぎます。PostgreSQLのREPEATABLE READはさらに厳格で、データスナップショットの変更を検知すると単にエラーを返し、アプリケーション側に競合の処理を強制します。

2. 書き込みスキュー：静かなる暗殺者

書き込みスキューは、REPEATABLE READでも発生しうる微妙なバグです。2つのトランザクションが同じデータを読み取り、ロジックに基づいて判断を下した後、互いの前提を無効にするような異なる行を更新した場合に発生します。

オンコールの医師のシナリオ：
病院では、少なくとも1人の医師が勤務している必要があります。アリスとボブはどちらもオンコール（待機中）です。二人は同時に退勤しようとします。

-- トランザクション 1 (アリス)
SELECT count(*) FROM doctors WHERE on_call = true; -- 2を返す
UPDATE doctors SET on_call = false WHERE name = 'Alice'; -- 許可される

-- トランザクション 2 (ボブ)
SELECT count(*) FROM doctors WHERE on_call = true; -- 2を返す (MVCCスナップショットのため)
UPDATE doctors SET on_call = false WHERE name = 'Bob'; -- 許可される

-- 結果: オンコールの医師が0人になる。システムの失敗。

異なる行を更新したため、ロックはトリガーされませんでした。MVCCはデータベースの動作を維持することには成功しましたが、ビジネスロジックの違反を許してしまいました。

本番環境で成功するための戦略

すべてをSERIALIZABLEに設定すればいいわけではありません。最も安全なレベルですが、トランザクションをほぼ逐次的に実行させるため、スループットが激減する可能性があります。並行性の高いロジックを処理するための、より良い方法を以下に示します。

クリティカルパスには明示的なロックを使用する

書き込みスキューのリスクがある場合は、SELECT ... FOR UPDATEを使用します。これにより、読み取っている行をデータベースに強制的にロックさせ、処理が終わるまで他のトランザクションを待機させます。

-- 書き込みスキューを手動で防ぐ
BEGIN;
SELECT count(*) FROM doctors WHERE on_call = true FOR UPDATE;
-- アリスがロックを保持。ボブのクエリはここで待機する。
UPDATE doctors SET on_call = false WHERE name = 'Alice';
COMMIT;

PostgreSQLのSSIの利点

PostgreSQLは**直列化可能スナップショット分離（SSI: Serializable Snapshot Isolation）**という機能を提供しています。テーブル全体をロックする従来の直列化レベルとは異なり、SSIは依存関係を追跡します。潜在的な書き込みスキューを検知すると、一方のトランザクションを停止させ、直列化エラーを返します。コード側では、このエラーをキャッチして**操作を即座にリトライ**する準備が必要です。

MySQLの戦略：バージョントラッキング

MySQLの場合、私は**楽観的並行性制御（OCC: Optimistic Concurrency Control）**を好んで使います。重いデータベースロックに頼る代わりに、テーブルにversionカラムを追加します。これは軽量で、Webアプリケーションに最適です。

-- アプリケーション層での楽観的ロック
UPDATE products 
SET stock = stock - 1, version = version + 1 
WHERE id = 101 AND version = 12; -- 12は事前に取得したバージョン

更新された行数が0の場合、他の誰かが先に更新したことを意味します。その場合、アプリケーションは新しいデータを再取得してやり直す必要があります。

最終チェックリスト

  1. READ COMMITTEDを基本にする： 90%のユースケースにおいて、これが最適なデフォルト設定です。高いパフォーマンスを提供し、ダーティリード（Dirty Reads）を防ぎます。
  2. 肥満化（Bloat）に注意（Postgres）： pg_stat_all_tablesを監視しましょう。不要なタプル数が増えている場合は、autovacuumの設定を調整する必要があります。
  3. 長時間実行されるトランザクションを停止する（MySQL）： 履歴リストの長さ（History List Length）を監視しましょう。数時間開いたままのトランザクションはUndoログを肥大化させ、パフォーマンスを低下させます。
  4. リトライロジックを構築する： 高い分離レベルを使用する場合、リトライは必須です。それはアプリケーションの信頼性を支える核となる部分です。

MVCCは、現代のデータベースが数百万行までスケールすることを可能にするエンジンです。バージョンがどのように保存され、どこでロジックが破綻するかを理解することで、超高速かつ完全に整合性のとれたデータレイヤーを構築できるようになります。

Share:
Tags: