デッドロックを“見つけて直す”から“起きにくく設計する”へ
SQL Serverのデッドロック（Deadlock）は、2つ以上のトランザクションが互いのロック解放を待ち合い、永続的な待機に陥る状態を指します。
SQL Serverは自動検出すると一方をVictimとしてロールバックしますが、業務から見れば失敗です。
本記事では、検出・再現・分析・防止設計を一連の流れで整理し、実務での恒久対策につなげます。
目次
1. デッドロックとは何か（仕組みと検出の基本）
デッドロックは循環待ちとも呼ばれ、T1がリソースAを保持しつつBを待ち、T2がリソースBを保持しつつAを待つ、といった状況で発生します。
SQL Serverは定期的にデッドロック検出を行い、エラー1205で一方のトランザクションをロールバックします。
- 症状：アプリ側でランダムに発生する一時的な失敗（1205）。
- 誤解：「偶発的だから放置」→ 放置するとピーク時に連鎖的に失敗が増える。
- 方針：再試行の表層対処だけでなく、設計上の原因を潰すのが王道。
2. 発生しやすい典型パターン（設計上の地雷）
| パターン | 説明 | 主な対策 |
|---|---|---|
| 逆順アクセス | セッションAはテーブルX→Y、セッションBはY→Xの順で更新 | アクセス順序の統一、ロック取得順の明文化 |
| 過剰な範囲ロック | インデックス不備でテーブル/範囲ロックが拡大 | カバリング/適切なキー順のインデックス設計 |
| 長大トランザクション | 不要に広い範囲を長時間ロック保持 | コミットの細分化、処理分割、バッチ化 |
| 再試行なし | 一度の失敗で全体が巻き戻る | 1205に限った即時リトライ（指数バックオフ） |
3. 検出方法：DMV／拡張イベント／system_health
まずは「今、何が詰まっているか」を把握します。軽量観測→詳細取得の順で進めるのがコツです。
3-1. いま誰が誰をブロック？（DMVで即時把握）
SELECT
r.session_id,
r.blocking_session_id,
r.status,
DB_NAME(r.database_id) AS dbname,
t.text AS running_sql
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.blocking_session_id <> 0
ORDER BY r.session_id;
blocking_session_idが0以外なら、相手に塞がれている状態です。相互にブロックし合い、かつ待機が解消しない場合はデッドロックに発展しやすい構図です。
3-2. 拡張イベントでデッドロックグラフを取得
最も有用なのがExtended Eventsです。既定のsystem_healthセッションにはデッドロック報告が入ります（多くの環境で自動有効化されています）。
-- system_healthからデッドロックXMLを抽出（権限と環境に応じて調整）
SELECT XEventData.XEvent.value('(data/value)[1]', 'varchar(max)') AS DeadlockGraph
FROM (
SELECT CAST(t.target_data AS XML) AS TargetData
FROM sys.dm_xe_session_targets t
JOIN sys.dm_xe_sessions s ON t.event_session_address = s.address
WHERE s.name = 'system_health' AND t.target_name = 'ring_buffer'
) AS Data
CROSS APPLY TargetData.nodes('//RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData(XEvent);
取得したXML（デッドロックグラフ）をSSMSで開くと、関与セッション・ロック対象（キー/オブジェクト）・リソース待機関係が視覚化され、原因の特定が一気に進みます。
4. 再現と分析：デッドロックグラフを読み解く
本番と同等のインデックス・データ量を用意し、並行に実行して再現します。解析のポイントは以下の3点です。
- アクセス順：テーブル/インデックスを取得する順序が逆になっていないか。
- ロック種別/粒度：キー/ページ/テーブル、S/U/Xのどれか。必要以上に範囲が広くなっていないか。
- 統計情報と計画：誤った実行計画で広範囲スキャンになっていないか。
SSMSの「デッドロックグラフ」ではノード（プロセス）とエッジ（リソース待機）の関係を視覚的に確認できます。Victimに選ばれた側のSQLを重点的に見直すのが最も効率的です。
5. 防止設計：順序の統一・インデックス最適化・短トランザクション
5-1. 更新順序の統一（最強の基本施策）
全アプリ/バッチで「更新順序のガイドライン」を明文化し、コードレビューで遵守を確認します。
たとえば「Customer → Orders → OrderDetails」の順に更新する場合は、他のトランザクションでも必ず同じ順序にします。
5-2. インデックスで“狭く速く”取る
- 検索条件と一致する選択性の高いキーを先頭に配置。
- 必要列をすべてカバーするカバリングインデックスでロック範囲を縮小。
- 範囲検索のWHERE条件が広すぎる場合はキー設計を見直す。
5-3. トランザクションを短く保つ
- ユーザー入力や外部API通信をトランザクション外に出す。
- 小さな単位でコミットし、長時間ロックを避ける。
- 一括更新ではバッチ処理に分割する。
5-4. ロックヒントの活用（目的を明確に）
-- 読み取り後の更新競合を避ける例
SELECT * FROM dbo.Inventory WITH (ROWLOCK, UPDLOCK)
WHERE ItemID = @ItemID;
-- 必要に応じて分離レベルを調整
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
ヒント句の使いすぎは逆効果になる場合もあります。NOLOCKは速いですが、ダーティリードが発生するため、会計・在庫管理では避けましょう。
6. アプリ/SQLでの再試行（Retry）戦略
設計改善に加えて、エラー1205に限定した再試行は実務で非常に有効です。
指数バックオフ（100ms→200ms→400ms）で最大3回程度リトライすると安定性が高まります。
BEGIN TRY
BEGIN TRAN;
-- 競合しやすい更新処理
UPDATE dbo.Account SET Balance = Balance - @amt WHERE Id = @from;
UPDATE dbo.Account SET Balance = Balance + @amt WHERE Id = @to;
COMMIT TRAN;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK TRAN;
IF ERROR_NUMBER() = 1205
BEGIN
-- アプリ層で指数バックオフによる再試行を推奨
RAISERROR('Deadlock detected. Please retry.', 11, 1);
RETURN;
END
ELSE
THROW; -- 想定外はそのまま送出
END CATCH;
再試行を仕込むことでユーザー体験が向上し、アプリ全体の安定稼働に寄与します。
7. 運用：自動監視・通知・事後レビュー
- 拡張イベント常時捕捉：system_health＋独自セッションでxml_deadlock_reportを保存。
- SQL Agent通知：発生時にメールやTeams通知を自動化。
- 週次レビュー：発生件数・対象オブジェクト・実行計画の差分を棚卸し。
- 改善チケット化：再発防止の設計修正を継続的に実施。
まとめ：根本対策で発生確率を下げ続ける
デッドロックは“起きるもの”ではなく、“起きにくく設計できるもの”です。
アクセス順序の統一、適切なインデックス、短いトランザクション、1205再試行の多層防御を行うことで発生率は大きく下げられます。
拡張イベントでの可視化と定期レビューを組み合わせ、継続的な改善を進めましょう。
参考リンク（公式ドキュメント）
- SQL Server デッドロック ガイド
- sys.dm_tran_locks (Transact-SQL)
- 拡張イベントでの監視
- SET TRANSACTION ISOLATION LEVEL
- パフォーマンス チューニング
0 件のコメント:
コメントを投稿