INFRASTRUCTURE LEARNINGインフラ学習
インフラ大全インフラ大全IT INFRASTRUCTURE / LEARNING
インフラ学習ガイド
教材・演習を探す/

Escで閉じる · 検索語は送信しません

LEARNINGEN

テーマ別の解説テーマ:SQL Serverの性能と同時実行

SQL Serverのロック・ブロッキング・デッドロック:2セッションで違いを理解

ロックはデータを守る制御、ブロッキングは競合する要求の待機、デッドロックは互いを待つ循環です。「SQLが遅い」を一つの原因に決めず、2セッションがどの資源を保持し、何を待つかを図と時系列で追います。

この教材で学ぶこと

一方向の待機と循環待ちを図から判別し、同じ試行の観測から次の調査を選べる。

先に知っておくこと

セッションは接続に対応するサーバー側の作業単位。トランザクションは変更をまとめて確定・取消しする単位です。以下はSQL Server 2022の独立した合成例で、実DBや本物のセッションには接続しません。

まず、保持・待機・循環を分ける

似ている言葉を、別の状態として読む
言葉意味それだけでは分からないこと
ロック lock競合する処理から資源を保護する仕組み待機が起きているか
ブロッキング blocking必要なロックと競合する保持があり、要求が待つ循環待ちがあるか
デッドロック deadlock互いの処理完了を妨げる待機の循環どちらが犠牲になるか、業務への影響

ロックは異常の名前ではありません。長く待つことと、循環して待つことは別です。CPU・I/O・ネットワーク等で遅い要求まで、ロック待ちと決めつけません。

Aが保持し、Bだけが待つ例

例W:B → Aは「BがAの解放を待つ」

セッションA

文書103を変更し、まだ確定していない。

セッションB

同じ文書103の更新を要求。Aと競合するため待つ。

  1. B → A文書103の保護が解放されるのを待つ

矢印は待機先です。通信の経路や、処理済みの順番ではありません。AはBを待っていません。

独立した例WはCorpDocs-Wを使い、通常のディスク上の表、READ COMMITTED、READ_COMMITTED_SNAPSHOT(RCSI)はOFF、ロックの昇格や他の競合はないと仮定します。文書103という表記は説明用の資源ラベルで、実際のKEY・PAGE・OBJECT等のロック資源を取得した表示ではありません。

文書103に対する2つの要求
時点AB
W1トランザクションを開始し103を更新まだ要求しない
W2未確定のまま保持103の更新を要求し待機
W3COMMIT又はROLLBACKで終了競合する保持の解放後に進行可能

Bも保持すると、待機が循環することがある

例D:A → B → Aという循環

セッションA

103を保持し、Bが保持する104を要求。

セッションB

104を保持し、Aが保持する103を要求。

  1. A → B104の解放を待つ
  2. B → A103の解放を待つ

別の独立した例Dです。例Wの後に自動的に続く出来事ではありません。両方の保持と要求を与えたため、循環が成立します。

循環ができるまでの順序
時点AB
D1103を更新して保持未開始
D2103を保持したまま104を更新して保持
D3104を要求してBを待つ104を保持したまま
D4Bを待ったまま103を要求してAを待つ

SQL Serverがデッドロックを検出すると、犠牲となるトランザクションを選び、取り消してエラー1205を返します。この図だけでは犠牲となる側を決められません。アプリは失敗した取引全体の再試行可否、待機、回数制限、DB外へ出た副作用を考えます。単独の最後の文だけを無条件に再実行する設計にはしません。

読み取り方式とエンジン版で、挙動は変わる

例の条件と、別に確認する仕組み
条件理解する違い
READ COMMITTED・RCSI OFF通常の読取りは共有ロックを使い、競合する書込みで待つことがある
READ COMMITTED・RCSI ON読取りは文の開始時点のコミット済み行バージョンを使う。全ロックや全待機がなくなる意味ではない
SQL Server 2022この例のロック機構。optimized lockingは対象外
SQL Server 2025optimized lockingを利用可能。SQL Serverでは既定OFFで、DBごとの設定と前提を確認

optimized lockingでは、書込みの保護にトランザクションIDを用いるなど保持の仕組みが変わります。Azure SQLの既定値を、自社SQL ServerやRDSへそのまま当てはめません。RCSIの変更も、その場しのぎの操作ではなく、読取りの意味・影響・運用条件を確認して判断します。

同じ時点の保持・要求・取引を集める

  1. 対象DB・時刻・待つセッション・待機種類・待機先を同じ試行として対応させる。
  2. 待機の先頭まで追い、実行中の文だけでなく、未完了のトランザクションと直前の処理も確認する。
  3. デッドロックなら、xml_deadlock_reportの保持・要求・犠牲を読む。SQL Serverのsystem_healthは既定で同イベントを収集するが、保持期間と権限を確認する。
  4. 目的と影響を確認して取引時間やアクセス順、クエリを改善し、再発時の同じ観測条件で比較する。

保持と待機から、状態を見分ける

選択すると、正誤と理由が表示されます。

例WはB → Aだけです。どの判断が適切?
答えと理由を読む

正解:ブロッキングはあるが、循環は示されていない。 · AからBへの待機がないので、この与件では循環しません。

例DでA → BとB → Aが成立。図から分かるのは?
答えと理由を読む

正解:待機が循環する。犠牲となる側は未指定。 · 保持と要求から循環は分かりますが、犠牲の選定条件は別です。

RCSIをONにすれば、どのロックも待機も消える?
答えと理由を読む

正解:いいえ。読取りの仕組みと、残る競合を分ける。 · RCSIはREAD COMMITTEDの読取りに関係します。全待機を消す設定ではありません。

実行中SQLが短いのに、先頭セッションが他を待たせています。次は?
答えと理由を読む

正解:未完了の取引と、同時点の保持・要求を確認する。 · 今の文の短さだけでは、以前からの保持や取引の長さは分かりません。

確認して、自分の言葉で説明する

  1. 例Wの矢印を「誰が誰を待つか」という文で説明する。
  2. 例Dで103と104の保持を入れ替えず、循環を一周たどる。
  3. 観測していない犠牲・終了時刻・実環境の成功を補わない。
  4. 版・RCSI・取引の長さを調査の前提に含める。
理解を確かめる: BがAを待っている。それだけで、AもBを待つと判断できる?

いいえ。一方向の待機だけでは循環を示しません。

Aの保持と次の要求、Bの保持と要求を同じ時点で確認します。デッドロックの判断には循環の根拠が必要です。

出典と確認した条件

説明に用いた一次資料を、確認した内容・条件と本文の参照箇所に分けてまとめています。専門家による監修・実環境での検証は行っていません。