MS_SQLServerDeadlock - NetDevInfraWGinOSSConsortium/NetDevInfraWiki GitHub Wiki

SQL Server でのデッドロック

概要

SQL Server でのデッドロック発生時の対応方法について説明します。

前提知識

基本的に、ロックのたすき掛けで発生しています。

このため、

で説明したように、SQL Server のロッキングのメカニズムについて
知っておく必要があります。

例えば、SQL Server は、

  • 参照処理にもロック（共有ロック）をかける。
  • スキャンにより広範囲にロックがかかる。
  • ロック・エスカレーションにより広範囲にロックがかかる。

等と言った所でしょうか。

補足（ロック待ちとデッドロックの違い）: 混同されやすいが別物である。

ロック待ち（ブロッキング） デッドロック
状態 相手が解放すれば進める 相互に待ち合い、永久に進めない
解消 待ち続けるか、LOCK_TIMEOUT で打ち切り SQL Server が片方を強制終了する
エラー 1222（ロック要求のタイムアウト） 1205（デッドロックの犠牲者）

SQL Server はデッドロック モニタが約 5 秒間隔で待ちグラフを走査し、
循環を検出するとロールバック コストが小さいほうを犠牲者に選ぶ。
どちらを犠牲にするかは SET DEADLOCK_PRIORITY で調整できる。

分析方法

ロックの確認

「SQL Server でのロック・タイムアウト」で説明した方法で、
ロックの状態を確認しつつ、デッドロックが発生していないかを確認します。

SQLプロファイラ

SQL プロファイラを用いてデッドロックを確認する方法

補足（最新化：後追いで取れる system_health）: デッドロックは
「再現待ち」でトレースを仕掛ける必要はない。
SQL Server 2012 以降、既定で常時稼働している拡張イベント セッション
system_health が xml_deadlock_report を記録している。

SELECT
    CAST(xed.value('@timestamp','datetime2') AS datetime2) AS utc_time,
    CAST(xed.query('.') AS xml)                            AS deadlock_graph
FROM (
    SELECT CAST(target_data AS xml) AS td
    FROM sys.dm_xe_session_targets AS st
    JOIN sys.dm_xe_sessions AS s
      ON s.address = st.event_session_address
    WHERE s.name = 'system_health'
      AND st.target_name = 'ring_buffer'
) AS d
CROSS APPLY td.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]')
    AS x(xed)
ORDER BY utc_time DESC;

結果の XML を .xdl として保存すれば、SSMS でグラフ表示できる。
リング バッファのため古いものから消える点には注意
（常時採取するなら専用の拡張イベント セッションを作る）。

対応方法

アプリ側の設計上の問題

ロック法で実装された RDBMS を利用するアプリケーションとしての
設計上の問題が無いかを確認します。

更新ロックの活用

Oracle と SQL Server の更新ロックは、「更新データの消失」を防ぐ役割で使用される。
＃「更新データの消失」とは参照したデータが、同一トランザクション中で
更新されてしまう事。
SQL Server の場合、「更新データの消失」の防止は、共有ロックのホールドでも事足りる。
このため、SQL Server の更新ロックは「デッドロック」防止のためと言われる。

補足（更新ロック (U) がデッドロックを防ぐ仕組み）: 本文の要旨を補うと、
更新ロックは共有ロック (S) と排他ロック (X) の中間で、
以下の互換性を持つ。

S U X
S ○ ○ ×
U ○ × ×
X × × ×

重要なのは U 同士が非互換という点。
更新ロックがないと、「2 つのトランザクションが同じ行に S を掛け、
双方が X への昇格を待つ」という典型的なデッドロックが起きる。
U は 1 つしか取れないため、2 つ目は待たされるだけ（ブロッキング）で済む。

アプリ側では、「読んでから更新する」パターンで
WITH (UPDLOCK) を明示するとこの効果が得られる。

SELECT @qty = Qty FROM dbo.Stock WITH (UPDLOCK, ROWLOCK)
 WHERE ItemId = @id;
-- 判定処理
UPDATE dbo.Stock SET Qty = @qty - 1 WHERE ItemId = @id;

デッドロックの回避

更新ロックの活用以外にも、デッドロックを回避のために検討すべきことがあります。

  1. 同じ順序でオブジェクトにアクセスする（CRUD 表などを利用）。
  2. トランザクション内でのユーザの対話をなくす。
  3. トランザクションを短くして、1 つのバッチ内に収める。
  4. 業務に問題が無ければ、できるだけ低い分離レベルを使用する。

補足（1 が最重要）: 4 項目のうち、
「同じ順序でアクセスする」がデッドロック対策の本丸である。
デッドロックは定義上、循環待ちがなければ発生しない。
テーブルへのアクセス順序を全処理で統一すれば、循環自体が作れなくなる。

ここで見落とされやすいのが、同じテーブル内の行の順序と
インデックス経由のアクセス順序である。
同じテーブルを更新していても、

  • IN (1, 5, 3) のようにキーの順序がばらばら
  • 一方はクラスタ化インデックス経由、他方は非クラスタ化インデックス経由

といった違いでデッドロックになる。
前者は ORDER BY でキー順に処理することで回避できる。

DBMSの機能上の問題

ロック法で実装された RDBMS の機能を問題と考える場合、
以下のような対策もあります。

エスカレーションの抑止

ロックのエスカレーションの抑止をする。
SQL Server のロックのエスカレーション

多バージョン法への切替

多バージョン法へ切り替える。
DBMSのロック・分離戦略と同時実行制御

補足（RCSI は万能ではない）: RCSI（READ_COMMITTED_SNAPSHOT ON）は
参照処理が共有ロックを取らなくなるため、
「参照 × 更新」のデッドロックはほぼ解消する。
ただし、「更新 × 更新」のデッドロックは解消しない
（更新は依然として排他ロックを取るため）。
更新同士の競合は、アクセス順序の統一で解決するしかない。

また、SNAPSHOT 分離レベル（RCSI とは別物）では、
ロックの代わりに**更新競合エラー（3960）**が発生するようになる。
これはこれでアプリ側のリトライ設計が必要になる。

その他の対応

タイムアウトと同列に扱う

レアケースであれば、ロック・タイムアウト、コマンド・タイムアウトと同様に
リトライ可能なエラー（業務続行可能なエラー）として設計しておくのも手です。

補足（リトライ設計の要点）: デッドロックの犠牲者は
トランザクション全体がロールバックされているため、
「途中から再開」はできない。トランザクション全体をやり直す。

const int MaxRetry = 3;
for (int i = 0; ; i++)
{
    try
    {
        await DoWorkInTransactionAsync();
        break;
    }
    catch (SqlException ex) when (ex.Number == 1205 && i < MaxRetry)
    {
        // 指数バックオフ + ジッタで、同じ相手と再衝突するのを避ける
        await Task.Delay(TimeSpan.FromMilliseconds(
            Random.Shared.Next(50, 200) * (1 << i)));
    }
}

即座に再実行すると同じ相手と再び衝突しやすいため、
バックオフとジッタを入れるのが要点。
なお、リトライ可能な SQL Server のエラー番号としては
1205（デッドロック）のほか 1222（ロック タイムアウト）、
3960（スナップショット更新競合）、49918/49919/49920（Azure SQL の一時的エラー）
などがある。

SQL Serverの問題

並列クエリ等、ロック以外の原因で
デッドロックが発生する可能性もあります。
原因によっては、パッチで FIX することもあります。

この場合、エラー番号が異なるようです。

  • 通常：1205

  • 並列クエリ：8650

  • サポート技術情報（Microsoft Knowledge Base）

    • クエリ内の並列処理を使用するクエリを実行すると、
      8650 のエラー メッセージが表示されます。
    • [BUG] 並列クエリが応答を停止する、
      または 8650 エラー メッセージが表示される
    • [FIX] クエリを並列実行すると、
      クエリ自体で検出されないデッドロックが発生する

補足（エラー 8650 の現在の対処）: 8650 は
「クエリ プロセッサがメモリ不足でクエリ プランを実行できない」旨のエラーで、
並列実行のイントラクエリ並列処理デッドロック（intra-query parallelism deadlock）
として現れることがある。
現在の一般的な対処は以下。

  • cost threshold for parallelism を引き上げ、
    小さいクエリを並列化しない（既定 5 → 25〜50）
  • 該当クエリに OPTION (MAXDOP 1) を指定
  • 統計情報を更新して、過大なメモリ グラントを避ける

詳細はSQL Server のファイル・グループの
並列クエリの節、および
SQL Server 結合方式の問題を監視するを参照。

参考

Tags: 移行, データアクセス, SQL Server, 障害対応, 性能, デバッグ

このWikiは「Open棟梁Project」,「OSSコンソーシアム 開発基盤部会」によって運営されています。

⚠️ **GitHub.com Fallback** ⚠️