跳到主要内容

SQL Serverにおけるデッドロック

概要​

デッドロック（Deadlock）とは、2つ以上のプロセスが互いに相手がロックしているリソースの解放を待ち続け、永久に処理が進まなくなる状態を指します。

SQL Serverでは、デッドロック検知機構（Deadlock Monitor）が定期的にシステムをチェックしており、デッドロックを検出すると、コスト（ロールバックにかかるコストなど）が低い方のトランザクションを「犠牲者（Victim）」として強制終了（Kill）し、エラー（エラー番号 1205）を返します。

デッドロックのサイクル​

典型的なデッドロックは以下のようなサイクルで発生します：

  1. トランザクションA が リソース1 をロック。
  2. トランザクションB が リソース2 をロック。
  3. トランザクションA が リソース2 のロック獲得を要求（待機）。
  4. トランザクションB が リソース1 のロック獲得を要求（待機）。

互いに相手のリソースを待っているため、どちらも進むことができません。

原因となる主なクエリ構造とパターン​

デッドロックは単に「重いクエリ」だから発生するわけではなく、リソースへのアクセス順序やロックの種類の競合によって発生します。

1. 逆順アクセス（Reverse Order Access）​

最も基本的かつ頻繁なパターンです。2つのトランザクションが、複数のテーブル（または行）に対して逆の順序でアクセスする場合に発生します。

  • Transaction A: Table X → Table Y
  • Transaction B: Table Y → Table X
-- Transaction A
BEGIN TRAN
UPDATE TableX SET Col1 = 1 WHERE Id = 1;
-- ここで少し処理時間があるとする
UPDATE TableY SET Col1 = 1 WHERE Id = 1; -- TableYのロック待ち
COMMIT

-- Transaction B
BEGIN TRAN
UPDATE TableY SET Col1 = 2 WHERE Id = 1;
-- ここで少し処理時間があるとする
UPDATE TableX SET Col1 = 2 WHERE Id = 1; -- TableXのロック待ち（デッドロック発生）
COMMIT

2. ブックマークルックアップ（Bookmark Lookup）による競合​

SELECT文とUPDATE/DELETE文の間で発生するパターンです。 非クラスター化インデックスを使用した読み取り（SELECT）と、データの更新（UPDATE）が競合します。

  • SELECT側: 非クラスター化インデックスを読み取り → ヒープ/クラスター化インデックスを参照（Key Lookup/RID Lookup）しようとする（Sロック）。
  • UPDATE側: ヒープ/クラスター化インデックスを更新 → 非クラスター化インデックスを更新しようとする（Xロック）。

アクセスパスが逆方向になるため、デッドロックが発生します。

3. Serializable 分離レベル（範囲ロック）​

分離レベルを SERIALIZABLE に設定している場合、範囲ロック（Range Lock）が取得されます。 例えば、存在しない行を確認してから挿入する（Upsertのような）処理を並列で行うと、互いに範囲共有ロック（RangeS-S）を取得し、その後挿入のために排他ロック（X）への変換を試みると、互いのSロックが邪魔をして変換デッドロック（Conversion Deadlock）が発生します。

一般的な対応策​

デッドロックは完全に防ぐことが難しい場合もありますが、以下の対策で発生頻度を大幅に下げることができます。

1. アクセス順序の統一​

すべてのトランザクションで、テーブルやリソースにアクセスする順序を統一します。 親テーブル → 子テーブルの順、あるいはIDの昇順など、ルールを決めて実装します。

2. トランザクションの短縮化​

トランザクションをできるだけ短く保ちます。

  • ユーザー入力待ちをトランザクション内に含めない。
  • バッチ処理を小分けにする。
  • 不要な処理をトランザクション外に出す。

ロックの保持期間が短くなれば、競合の可能性も低くなります。

3. 適切な分離レベルの使用​

可能であれば、ロックの競合が少ない分離レベルを使用します。

  • Read Committed Snapshot Isolation (RCSI): 読み取り操作が書き込み操作をブロックしなくなり（Sロックを取得しない）、書き込みも読み取りをブロックしなくなります。多くのデッドロック（特に読み取りと書き込みの競合）を解消できます。

    ALTER DATABASE [MyDatabase] SET READ_COMMITTED_SNAPSHOT ON;

4. インデックスチューニング（カバリングインデックス）​

ブックマークルックアップによるデッドロックを防ぐため、クエリに必要な列をすべて含む「カバリングインデックス」を作成します。これにより、ベーステーブルへのルックアップが不要になり、デッドロックのサイクルが断ち切られます。

CREATE NONCLUSTERED INDEX IX_Table_Col1 ON Table (Col1) INCLUDE (Col2, Col3);

5. UPDLOCK ヒントの使用​

「読み取ってから更新する」ロジックの場合、読み取り時点で UPDLOCK を指定して、最初から更新意図のあるロックを取得しておきます。これにより、変換デッドロック（Sロック → Xロックへの変換待ちによるデッドロック）を防ぐことができます。

BEGIN TRAN
-- 最初から更新ロックを取得
SELECT * FROM TableX WITH (UPDLOCK) WHERE Id = 1;
UPDATE TableX SET Col1 = 1 WHERE Id = 1;
COMMIT

6. 再試行（Retry）ロジックの実装​

デッドロックは一時的な状態であることが多いため、アプリケーション側でエラー（1205）を検知した場合に、トランザクション全体を再試行するロジックを組み込むことが推奨されます。

// C#での再試行ロジックのイメージ
int retryCount = 0;
while (retryCount < 3)
{
    try
    {
        // トランザクション処理
        break; // 成功したらループを抜ける
    }
    catch (SqlException ex) when (ex.Number == 1205) // Deadlock error number
    {
        retryCount++;
        Thread.Sleep(1000); // 少し待機してからリトライ
    }
}

7. NOLOCK ヒントの使用（注意が必要）​

デッドロック対策として SELECT ステートメントに WITH (NOLOCK) ヒントを追加する方法がよく知られていますが、これには重大な副作用があるため注意が必要です。

Pros（メリット）​

  • ブロックの回避: 共有ロック（Sロック）を取得しないため、更新中のデータであっても待機することなく読み取ることができます。
  • デッドロックの減少: 読み取りと書き込みが互いにブロックし合わないため、これらに起因するデッドロックが発生しなくなります。
  • パフォーマンス向上: ロックのオーバーヘッドが減少し、スループットが向上する場合があります。

Cons（デメリット）​

  • ダーティリード（Dirty Read）: コミットされていない（後にロールバックされるかもしれない）データを読み取ってしまう可能性があります。
  • データの不整合: データの移動（ページ分割など）が発生している最中に読み取ると、同じ行を2回読み込んだり、逆に行を読み飛ばしたりする可能性がある（Allocation Order Scanによる弊害）。
  • エラーの可能性: 整合性のないデータを読み込むことで、アプリケーション側で予期せぬエラーが発生する可能性があります。

推奨・非推奨​

  • 基本的には非推奨です。特に金融システムや在庫管理など、データの正確性が求められる場面では使用すべきではありません。
  • 推奨される代替案: NOLOCK の代わりに Read Committed Snapshot Isolation (RCSI) の有効化を検討してください。RCSIを使用すれば、ダーティリードを防ぎつつ（一貫性のある以前のバージョンを読み取る）、読み取りと書き込みのブロックを回避できます。
  • 使用してもよい場面: 厳密な正確性が求められない概算レポートや、ダーティリードのリスクを完全に許容できるデバッグ用途などに限定すべきです。