Skip to content

MS_SQLServerLockEscalation

Jump to bottom
nishi_74322014 edited this page Aug 13, 2026 · 1 revision

SQL Server のロックのエスカレーション

概要

SQL Server では、ロックのリソースが多くなると、
ロック エスカレーションという処理が走り、ロックの粒度を、

（『行』、『ページ』） ⇒ 『テーブル』

と大きくすることで、ロックリソースの削減を図り、
全体のパフォーマンスを改善しようとする。

詳細

問題

SQL Server では、ユーザ プログラムの処理で使用できる、最も低い分離レベルである、
コミット済み読み取り(Read Committed) を適用した場合でも、
参照処理で一時的なロック(共有ロック) を適用するため、
参照処理が、大量の行ロックを獲得する場合は、
ロック エスカレーションが発生する可能性がある。

  • ロック エスカレーションは、パフォーマンス改善に繋がるが、
    ロックの粒度が大きくなるため、同時実行性の観点からは問題がある。

  • ロック エスカレーションに起因するデッドロックなどが、
    多くのプロジェクトで問題として報告されている。

移行メモ（正誤）: 「最も低い分離レベル」は正確ではない。
SQL Server の分離レベルで最も低いのは
**READ UNCOMMITTED（コミットされていない読み取り）**であり、
こちらは共有ロックを取得しない（ダーティ リードを許容する）。
本文の趣旨は「実務で通常使う最も低い分離レベル」ということであり、
既定の READ COMMITTED でも参照時に共有ロックを取る、という点が要旨。

補足（エスカレーションが特に効くケース）: 「テーブル ロックまで上がる」の
実害は、粒度そのものより保持期間との組み合わせで決まる。

  • READ COMMITTED の参照であれば、共有ロックは行を読み終えると解放される
    ため、エスカレーションしても影響は限定的。
  • しかし、REPEATABLE READ / SERIALIZABLE、あるいは
    **更新処理（排他ロック）**では、ロックがトランザクション終了まで保持される。
    ここでテーブル ロックへエスカレートすると、
    そのテーブル全体が長時間ブロックされる。

つまり、「大量更新を 1 トランザクションでまとめて実行する」設計が
最も危険である。

対策

ここでは、

  • ロック エスカレーション発生の閾値
  • ロック エスカレーション発生抑止方法
  • ロック エスカレーションを抑止した際に発生する問題

の 3 点について説明する。

ロック エスカレーション発生の閾値

ロック エスカレーション発生の閾値は、具体的には次のような数値をベースにしている。
※ ただし、この閾値はベースとなる値を示すだけであり、実際は統計情報に左右される。

  • トランザクションで獲得しているロック数が 1250 を超え、かつ 1250 の整数倍である場合、
    以下の判定処理が実行される。
    • クエリ内部で特定のスキャンで保持するロック数が 5000 を超える場合、
      エスカレーションが試行される。
    • ロックに使用しているメモリ量が、現在使用しているメモリ（AWE 領域を除く）の
      x% に達している場合、ロック エスカレーションが試行される
      （メモリは、ロック 1 つにつき 96 byte を消費する）。
      • SQL Server 2000 では 24%、SQL Server 2005、2008 では 40% である。
        尚、これらの数値はバージョンアップ等により予告なく変更されることがある。

移行メモ（正誤）: 単一のステートメントで取得するロック数のしきい値は
5000（公式ドキュメントの記載値）である。
元ページの「4845」は実測に基づく近似値と思われるが、
ドキュメント上の閾値に合わせて修正した。

ロック エスカレーション発生抑止方法

トレース フラグ 1224 or 1211 を有効にして、
ロック エスカレーションを無効にすることもできる。

ただし、トレース フラグでロック エスカレーションを無効にした場合、
下記の問題が発生するため、推奨はできない。

補足（最新化：テーブル単位の制御が推奨）: SQL Server 2008 以降は、
インスタンス全体に効くトレース フラグではなく、
テーブル単位でエスカレーションを制御できる。

-- このテーブルではエスカレーションしない
ALTER TABLE dbo.LargeTable SET (LOCK_ESCALATION = DISABLE);

-- パーティション単位でエスカレーションする（テーブル全体は止めない）
ALTER TABLE dbo.LargeTable SET (LOCK_ESCALATION = AUTO);

-- 既定（テーブル ロックへエスカレート）
ALTER TABLE dbo.LargeTable SET (LOCK_ESCALATION = TABLE);

特に、パーティション分割されたテーブルでは
AUTO（パーティション（HoBT）単位でエスカレート）が有効。
テーブル全体を止めずに済む
（SQL Server パーティション分割参照）。

なお、トレース フラグ 1211 と 1224 の違いは以下。

フラグ 挙動
1211 エスカレーションを完全に無効化。メモリ枯渇時もエスカレートしないため エラー 1204 のリスクが高い
1224 ロック数によるエスカレーションは無効化するが、メモリ逼迫時はエスカレートする。1211 より安全

両方を指定した場合は 1211 が優先される。

ロック エスカレーションを抑止した際に発生する問題

トレース フラグ 1211 でロック エスカレーションを抑止した場合、
ロックリソースの不足に起因する『エラー 1204』が発生する可能性がある。

  • 『エラー 1204』の発生の閾値は、以下のようになっている。
    • SQL Server の Locks オプションでロック数を明示的に指定している場合は
      その数に達した場合。
    • デフォルトの動的設定の場合は、ロックに使用しているメモリ量が、
      現在使用しているメモリ（AWE 領域を除く）の x% に達した場合。
      • SQL Server 2000 では 60% である。
        尚、これらの数値はバージョンアップ等により予告なく変更されることがある。

補足（本来の対策は設計側）: トレース フラグや LOCK_ESCALATION は
対症療法であり、根本対策は以下である。

対策 内容
バッチ分割 1 トランザクションで扱う行数を数千件程度に抑え、TOP (n) と WHILE でループさせる。エスカレーションの閾値に達しない
インデックス 適切なインデックスでスキャン範囲を狭める（SQL Server のインデックス）
分離レベル 参照側は RCSI（読み取り時にロックを取らない）に切り替える（DBMSのロック・分離戦略と同時実行制御）
アクセス順序 全処理で同じ順序でオブジェクトにアクセスする（SQL Server でのデッドロック）

特に **RCSI（READ_COMMITTED_SNAPSHOT ON）**は、
参照処理が共有ロックを取らなくなるため、
「参照が原因のエスカレーション」という本ページの前提自体を解消する。

補足（エスカレーションの発生を観測する）: 発生の有無は、
拡張イベントの lock_escalation イベントで確認できる
（SQL Server のログ参照）。
現在のロック状況は sys.dm_tran_locks の
resource_type が OBJECT の行を見ることで確認できる
（SQL Server でのロック・タイムアウト）。

参考

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

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

NetDevInfraWiki

マイクロソフト系技術情報 Wiki
Open 棟梁 Wiki

（未着手）

開発基盤部会 Wiki

移行管理: DONE ／ TODO

Clone this wiki locally