0Pricing
SQL Interview Prep · レッスン

デッドロック、ロック、MVCC

データベースが競合を回避する仕組みと、ロック方式とスナップショット方式のトレードオフを学びます。

「デッドロック、ロック、MVCC」はCoddyKit上の無料SQL Interview Prepレッスンです。 これはレッスン4/4です。 下記で完全なレッスンを無料で読むことができます。その後、ブラウザ内の組み込みコードエディタと24時間対応のAIチューターでハンズオン演習できます。 これはSQL Interview Prep学習パスの一部であり、ウェブとCoddyKitアプリ全体で進捗が同期されます。 SQL Interview Prepコースには全4レッスンが含まれています。

データベースが分離を実際に実現する仕組み

分離レベルは約束であり、ロックとMVCCはそれを実現する仕組みです。面接でこれらについて質問するのは、トランザクションが衝突したとき内部で何が起きるかを理解しているか確認するためです。

大きく分けて、次の2つの戦略があります。

  • 悲観的（ロック）：ロックが解放されるまで、競合するアクセスをブロックします。
  • 楽観的 / MVCC：全員に一貫したスナップショットを読み取らせ、コミット時に競合を検出します。

このレッスンでは、ロック、デッドロック、MVCCと、それらのトレードオフについて扱います。

共有ロックと排他ロック

従来のロック方式では、主に次の2つのモードを使用します。

  • 共有（S）ロックは読み取りに使用します。同じ行に対して、複数のトランザクションが同時に共有ロックを保持できます。
  • 排他（X）ロックは書き込みに使用します。保持できるのは1つのトランザクションだけで、その行に対する他のすべてのロックをブロックします。

ルールは、SはSと互換性がありますが、Xはどのロックとも互換性がないということです。書き込み側はすべての読み取り側を待ち、読み取り側は書き込み側を待たなければなりません。

SELECT FOR UPDATEによる明示的なロック

読み取るだけの行に書き込みロックを要求し、自分が処理する前に他のトランザクションから変更されるのを防ぐことができます。これは、読み取り・変更・書き込みのサイクルでロストアップデートを防ぐ標準的な方法です。

SELECT ... FOR UPDATEは排他的な行ロックを取得し、COMMITまたはROLLBACKするまで行をロックしたままにします。

BEGIN;
-- lock the row so no one else can modify it concurrently
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;  -- lock released here

デッドロックとは

デッドロックは、2つ以上のトランザクションがそれぞれ相手の必要とするロックを保持し、どのトランザクションも先に進めない循環状態が形成されたときに発生します。

教科書的な例では、T1が行Aをロックしてから行Bを要求し、T2が行Bをロックしてから行Aを要求します。互いに相手を待ち続けるため、どちらも永久に進めません。

データベースは待機グラフを使ってこれを検出します。循環が見つかると、エンジンは犠牲トランザクションを選んで中断し、デッドロックエラーを返します。これにより、他のトランザクションは処理を続けられます。

デッドロック：タイムライン

ロックを取得する順序が交差する様子に注目してください。T1は1行目を取得してから2行目を要求し、T2は2行目を取得してから1行目を要求します。どちらもロックを解放しないため、エンジンは一方を中断します。

中断されたトランザクションにはdeadlock detectedのようなエラーが返され、再試行する必要があります。残ったトランザクションは通常どおりコミットします。

-- T1                                  | -- T2
BEGIN;                                 | BEGIN;
UPDATE accounts SET balance=balance-10  | UPDATE accounts SET balance=balance-10
  WHERE id=1;  -- locks row 1          |   WHERE id=2;  -- locks row 2
UPDATE accounts SET balance=balance+10  | UPDATE accounts SET balance=balance+10
  WHERE id=2;  -- waits for T2         |   WHERE id=1;  -- waits for T1 -> CYCLE
-- one transaction is chosen as victim and rolled back

デッドロックの防止

デッドロックを完全になくすことはできませんが、発生頻度を下げることはできます。面接での標準的な回答は次のとおりです。

  • 一貫したロック順序：常に同じ順序（たとえばidの昇順）で行を取得します。これにより循環を断ち切れます。
  • トランザクションを短く保つ：ロックを保持する時間をできるだけ短くします。
  • 安全な場合は分離レベルを下げる：ロックと競合を減らします。
  • 再試行ロジックを追加する：犠牲になったトランザクションが自動的に再試行するようにします。

一貫した順序でロックを取得することが最も効果的な対策であり、面接官が最初に聞きたい回答です。

ロックの粒度

ロックは異なる範囲に対して取得でき、同時実行性とオーバーヘッドのトレードオフになります。

  • 行レベルのロックは高い同時実行性を実現しますが、管理コストが高くなります。
  • ページまたはテーブルのロックは管理しやすい一方で、より多くのトランザクションをブロックします。

トランザクションがあまりにも多くの行にアクセスすると、行ロックからテーブルロックへエスカレーションするエンジンもあります（ロックエスカレーション）。この仕組みを知っていれば、大規模な一括UPDATEが突然すべての処理をブロックする理由を説明できます。

MVCC：スナップショット方式

MVCC（マルチバージョン同時実行制御）は、Postgres、Oracle、InnoDBが読み取りロックの大半を回避する仕組みです。データベースはロックする代わりに、各行の複数のバージョンを保持します。

最大の利点であり、面接でもよく使われる説明は、読み取り側が書き込み側をブロックせず、書き込み側も読み取り側をブロックしないということです。

各トランザクションはある時点の一貫したスナップショットを参照し、書き込み側はその場で上書きするのではなく、新しい行バージョンを作成します。

MVCCの内部動作

行が更新されると、MVCCは新しいバージョンを書き込み、古いバージョンを保持します。各バージョンにはトランザクションIDのメタデータ（Postgresではxminとxmax）が付加され、いつ可視になったか、いつ置き換えられたかを示します。

トランザクションのスナップショットによって、どのバージョンを参照するかが決まります。どのトランザクションからも見えなくなった古いバージョンはデッドタプルとなり、後でクリーンアップ処理によって回収されます。Postgresではその処理がVACUUMです。これを実行しないとテーブル肥大化が起こるため、面接でよく追加質問されます。

ロックとMVCC：トレードオフ

比較を簡潔にまとめると、次のとおりです。

  • 純粋なロック：正しさを保ちやすい一方で、読み取り側と書き込み側が互いにブロックし、同時実行性が低下します。
  • MVCC：読み取りの同時実行性に優れ、読み取りロックも不要ですが、バージョンの保存とクリーンアップ（VACUUM、bloat）のコストがかかり、書き込み同士の競合には依然としてロックが必要です。

MVCCを使うエンジンでも、書き込み時にはロックを取得します。同じ行を更新する2つのトランザクションは直列化しなければなりません。MVCCがなくすのは読み取りと書き込みの競合であり、書き込み同士の競合ではありません。

楽観的ロックとバージョン列

エンジンレベルのMVCCとは別に、アプリケーションでは、ユーザーの操作が長時間続く場合の読み取り・変更・書き込みに楽観的ロックを追加することがあります。version列を追加してその値を読み取り、更新時にはバージョンが一致することを要求したうえで、値をインクリメントします。

別のトランザクションが先に行を更新していた場合、バージョンが一致しなくなり、影響を受ける行数は0になります。これにより、コードは再読み込みして再試行すべきだと判断できます。ユーザーが考えている間にロックを保持しないため、高い同時実行性を維持できます。「同じレコードを2人のユーザーが編集するとき、どのように対処しますか？」という質問への回答として、面接官はこの方法を好みます。

-- read: SELECT id, data, version FROM items WHERE id = 1;  -- version = 7
UPDATE items
  SET data = 'new value', version = version + 1
  WHERE id = 1 AND version = 7;
-- if rows affected = 0, someone else changed it: reload and retry

確認問題

MVCCの核心となる説明を確認してください。

まとめ：ロック、デッドロック、MVCC

これで、分離を支える仕組みを説明できるようになりました。

  • 共有ロックと排他ロックがアクセスを調整し、SELECT FOR UPDATEが明示的な書き込みロックを取得します。
  • デッドロックはロックの循環です。エンジンは犠牲トランザクションを中断し、一貫したロック順序によってその多くを防げます。
  • MVCCは行のバージョンを保持することで読み取り側と書き込み側のブロックを防ぎますが、クリーンアップ（VACUUM、bloat）のコストがかかります。

これらの仕組みを、前のレッスンで学んだ分離レベルと異常に結び付ければ、同時実行性に関する面接の質問に最初から最後まで対応できるようになります。

よくある質問

「デッドロック、ロック、MVCC」レッスンは無料ですか？

はい。「デッドロック、ロック、MVCC」の完全なテキストはこのウェブで無料で読めます。インタラクティブに演習し（組み込みコードエディタと24時間対応のAIチューター）、SQL Interview Prepコースの残りをアンロックするには、CoddyKit PROにアップグレードしてください。 SQL Interview Prepコースには全4レッスンが含まれています。

「デッドロック、ロック、MVCC」で何を学びますか？

データベースが競合を回避する仕組みと、ロック方式とスナップショット方式のトレードオフを学びます。 ブラウザで直接実行するハンズオンコードでSQL Interview Prepを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

SQL Interview Prepを始めるのに経験は必要ですか？

事前経験は必要ありません。CoddyKitのSQL Interview Prepは初級者から上級者向けに構成されているため、ここから始めるか最初から始めて、自分のペースで進むことができます。 これはレッスン4/4です。

「デッドロック、ロック、MVCC」レッスンにはどのくらい時間がかかりますか？

ほとんどのCoddyKitレッスンは約5～10分かかります。各レッスンはコンパクトでインタラクティブなので、着実に進歩し、ウェブとアプリ全体で正確に前回の場所から再開できます。

このSQL Interview Prepレッスンでコードを書いて実行できますか？

はい。すべてのSQL Interview Prepレッスンに組み込みコードエディタが含まれているため、ブラウザでリアルコードを書いて実行し、即座のAIフィードバックを取得できます。ローカル設定は不要です。

このコースのすべてのレッスン

  1. ACID特性の解説
  2. 4つの分離レベル
  3. ダーティリード、反復不能読み取り、ファントムリード
  4. デッドロック、ロック、MVCC
← SQL Interview Prepに戻る