SQL Serverのロック エスカレーションによって発生するブロックの問題を解決する

  • [アーティクル]

概要

ロック エスカレーションは、多くの細かい粒度のロック (行ロックやページ ロックなど) をテーブル ロックに変換するプロセスです。 Microsoft SQL Serverは、ロック エスカレーションを実行するタイミングを動的に決定します。 この決定を行うと、SQL Serverは、特定のスキャンで保持されるロックの数、トランザクション全体によって保持されるロックの数、およびシステム全体のロックに使用されるメモリを考慮します。 通常、SQL Serverの既定の動作では、パフォーマンスが向上する場合、または過剰なシステム ロック メモリをより適切なレベルに減らす必要がある場合にのみ、ロックエスカレーションが発生します。 ただし、一部のアプリケーションまたはクエリの設計では、このアクションが望ましくないときにロックエスカレーションがトリガーされ、エスカレートされたテーブル ロックによって他のユーザーがブロックされる場合があります。 この記事では、ロックエスカレーションがブロックの原因であるかどうかを判断する方法と、望ましくないロックエスカレーションに対処する方法について説明します。

元の製品バージョン: SQL Server
元の KB 番号: 323630

ロックのエスカレーションによってブロックが発生しているかどうかを判断する

ロックのエスカレーションでは、ほとんどのブロックの問題は発生しません。 ブロックの問題が発生した時点またはその近くでロック エスカレーションが発生しているかどうかを判断するには、イベントを含む拡張イベント セッションを lock_escalation 開始します。 イベントが表示 lock_escalation されない場合は、サーバーでロック エスカレーションが発生せず、この記事の情報は状況には適用されません。

ロックのエスカレーションが発生している場合は、エスカレートされたテーブル ロックが他のユーザーをブロックしていることを確認します。

ヘッド ブロッカーによって保持され、他のサーバー プロセス ID (SPID) をブロックしているヘッド ブロッカーとロック リソースを識別する方法の詳細については、「INF: ブロックの問題SQL Server理解して解決する」を参照してください。

他のユーザーをブロックしているロックが、ロック モードが S (共有) または X (排他) の TAB (テーブル レベル) ロック以外のロックである場合、ロックエスカレーションは問題になりません。 特に、TAB ロックが意図ロック (IS、IU、IX のロック モードなど) の場合、ロックのエスカレーションが原因ではありません。 ブロックの問題がロックエスカレーションによって引き起こされない場合は、「INF: ブロックの問題の理解と解決」のトラブルシューティング手順SQL Server参照してください。

ロックのエスカレーションを防止する

ロックエスカレーションを防ぐ最も簡単で安全な方法は、トランザクションを短くし、ロックエスカレーションのしきい値を超えないように高価なクエリのロック占有領域を減らすことです。 この目標を達成するには、次の方法を含むいくつかの方法があります。

  • 大規模なバッチ操作をいくつかの小さな操作に分割します。 たとえば、次のクエリを実行して、監査テーブルから 100,000 を超える古いレコードを削除し、クエリによって他のユーザーをブロックするロック エスカレーションが発生したと判断します。

    DELETE FROM LogMessages WHERE LogDate < '20020102';

    これらのレコードを一度に数百個削除することで、トランザクションごとに蓄積されるロックの数を大幅に減らすことができます。 これにより、ロックのエスカレーションが防止されます。 たとえば、次のクエリを実行します。

    DECLARE @done bit = 0;
WHILE (@done = 0)
BEGIN
    DELETE TOP(1000) FROM LogMessages WHERE LogDate < '20020102';
    IF @@rowcount < 1000 SET @done = 1;
END;

  • クエリを可能な限り効率的にすることで、クエリのロック フットプリントを減らします。 大きなスキャンや多くのブックマーク参照により、ロックエスカレーションの可能性が高くなる可能性があります。 さらに、これによりデッドロックの可能性が高まり、コンカレンシーとパフォーマンスに悪影響を与えます。 ロック エスカレーションの原因となるクエリを特定したら、新しいインデックスを作成したり、既存のインデックスに列を追加してインデックスまたはテーブル スキャンを削除したり、インデックス シークの効率を最大化したりする機会を探します。 実行プランを確認し、新しい非クラスター化インデックスを作成してクエリのパフォーマンスを向上させる可能性があります。 詳細については、「SQL Serverインデックス アーキテクチャとデザイン ガイド」を参照してください。

    この最適化の 1 つの目標は、ブックマーク検索のコストを最小限に抑えるために、インデックス シークができるだけ少ない行を返すことです (クエリのインデックスの選択性を最大化します)。 SQL Server、Bookmark Lookup 論理演算子が多数の行を返すと見積もった場合、句をPREFETCH使用してブックマーク検索を実行する可能性があります。 ブックマーク参照SQL Server使用PREFETCHする場合は、クエリの一部のトランザクション分離レベルを、クエリの一部の "反復可能な読み取り" に増やす必要があります。 つまり、"読み取りコミット済み" 分離レベルのステートメントのように SELECT 見える場合は、(クラスター化インデックスと 1 つの非クラスター化インデックスの両方で) 何千ものキー ロックが取得される可能性があります。 これにより、このようなクエリがロック エスカレーションのしきい値を超える可能性があります。 これは、エスカレートされたロックが共有テーブル ロックである場合に特に重要ですが、これらは既定の "読み取りコミット済み" 分離レベルでは一般的に見られない場合です。 ブックマーク参照 WITH PREFETCH 句によってエスカレーションが発生している場合は、Index Seek に表示される非クラスター化インデックス、またはクエリ プランのブックマーク検索論理演算子の下にあるインデックス スキャン論理演算子に列を追加することを検討してください。 カバーインデックス (クエリで使用されたテーブル内のすべての列を含むインデックス)、または結合条件に使用された列をカバーするインデックス、または WHERE 句で"列の選択" リストにすべてを含めるのが現実的でない場合は、少なくともインデックスを作成できます。

    入れ子になったループ結合では、 が使用 PREFETCHされる場合もあります。これにより、同じロック動作が発生します。

  • 別の SPID が現在互換性のないテーブル ロックを保持している場合、ロックエスカレーションは発生しません。 ロックエスカレーションは常にテーブル ロックにエスカレートし、ページ ロックにはエスカレートしません。 さらに、別の SPID が互換性のない TAB ロックを保持しているためにロックエスカレーション試行が失敗した場合、エスカレーションを試みたクエリは TAB ロックの待機中にブロックされません。 代わりに、元のより詳細なレベル (行、キー、またはページ) でロックを取得し続け、定期的に追加のエスカレーション試行を行います。 したがって、特定のテーブルのロック エスカレーションを防ぐ方法の 1 つは、エスカレートされたロックの種類と互換性のない別の接続でロックを取得して保持することです。 テーブル レベルの IX (意図排他) ロックでは、行またはページはロックされませんが、エスカレートされた S (共有) または X (排他) TAB ロックとは互換性がありません。 たとえば、mytable テーブル内の多数の行を変更するバッチ ジョブを実行する必要があり、ロックエスカレーションのためにブロックが発生したとします。 このジョブが常に 1 時間以内に完了する場合は、次のコードを含む Transact-SQL ジョブを作成し、バッチ ジョブの開始時刻の数分前に新しいジョブを開始するようにスケジュールすることができます。

    BEGIN TRAN;
SELECT * FROM mytable (UPDLOCK, HOLDLOCK) WHERE 1 = 0;
WAITFOR DELAY '1:00:00';
COMMIT TRAN;

    このクエリは、mytable の IX ロックを 1 時間取得して保持します。 これにより、その間のテーブルのロック エスカレーションが防止されます。 このバッチは、データを変更したり、他のクエリをブロックしたりしません (他のクエリが TABLOCK ヒントを使用してテーブル ロックを強制しない限り、または管理者が ALTER INDEX を使用してページロックまたは行ロックを無効にしている場合を除きます)。

  • SARGability の欠如によるロック エスカレーションを排除します。これは、クエリで述語と結合列にインデックスを使用できるかどうかを記述するために使用されるリレーショナル データベース用語です。 SARGability の詳細については、「 インサイド デザイン ガイド クエリに関する考慮事項」を参照してください。 たとえば、多数の行 (1 行) を要求していないと思われる非常に単純なクエリでは、テーブル/インデックス全体がスキャンされる可能性があります。 これは、WHERE 句の左側に関数または計算がある場合に発生する可能性があります。 SARGability を欠くこのような例としては、暗黙的または明示的なデータ型変換、ISNULL() システム関数、パラメーターとして渡された列を持つユーザー定義関数、または などのWHERE CONVERT(INT, column1) = @aWHERE Column1*Column2 = 5列の計算などがあります。 このような場合、すべての列値を最初に取得して関数に渡す必要があるため、クエリに適切な列が含まれている場合でも、クエリは既存のインデックスをシークできません。 これにより、テーブルまたはインデックス全体がスキャンされ、多数のロックが取得されます。 このような状況では、SQL Serverロックカウントのエスカレーションしきい値に達する可能性があります。 解決策は、WHERE 句の列に対する関数の使用を回避し、SARGable 条件を確保することです。

ロックエスカレーションを無効にする

SQL Serverでロック エスカレーションを無効にすることはできますが、推奨されません。 代わりに、「ロックエスカレーションの 防止 」セクションで説明されている防止戦略を使用してください。

  • テーブル レベル: ロックエスカレーションは、テーブル レベルで無効にすることができます。 ALTER TABLE ... SET (LOCK_ESCALATION = DISABLE) を参照してください。 対象となるテーブルを特定するには、T-SQL クエリを調べます。 それが不可能な場合は、 拡張イベントを使用し、 lock_escalation イベントを有効にして、 object_id 列を調べます。 または、 Lock:Escalation イベント を使用し、SQL Profiler を ObjectID2 使用して列を調べます。
  • インスタンス レベル: ロック エスカレーションを無効にするには、インスタンスのトレース フラグ 1211 または 1224 のいずれか、またはその両方を有効にします。 ただし、これらのトレース フラグを使用すると、SQL Serverのインスタンス内のすべてのロック エスカレーションがグローバルに無効になります。 ロックエスカレーションは、数千のロックを取得して解放するオーバーヘッドによって遅くなるクエリの効率を最大化することで、SQL Serverで便利な目的を果たします。 ロックエスカレーションは、ロックを追跡するために必要なメモリを最小限に抑えるのにも役立ちます。 ロック構造体SQL Server動的に割り当てることができるメモリは有限です。 そのため、ロック エスカレーションを無効にし、ロック メモリのサイズが十分に大きくなると、クエリに対して追加のロックを割り当てようとすると失敗し、次のエラー エントリが生成される可能性があります。

エラー: 1204、重大度: 19、状態: 1
SQL Serverは、現時点では LOCK リソースを取得できません。 アクティブなユーザーが少ない場合はステートメントを再実行するか、システム管理者にSQL Serverロックとメモリの構成をチェックするように依頼します。

注:

1204 エラーが発生すると、現在のステートメントの処理が停止され、アクティブなトランザクションがロールバックされます。 ロールバック自体がユーザーをブロックしたり、SQL Server サービスを再起動するとデータベースの復旧時間が長くなることがあります。

これらのトレース フラグ (-T1211 または -T1224) は、SQL Server 構成マネージャーを使用して追加できます。 新しいスタートアップ パラメーターを有効にするには、SQL Server サービスを再起動する必要があります。 または DBCC TRACEON (1224, -1) クエリをDBCC TRACEON (1211, -1)実行すると、トレース フラグはすぐに有効になります。
ただし、-T1211 または -T1224 をスタートアップ パラメーターとして追加しないと、SQL Server サービスが再起動されたときにコマンドのDBCC TRACEON効果が失われます。 トレース フラグをオンにすると、今後のロック エスカレーションは回避されますが、アクティブなトランザクションで既に発生したロック エスカレーションは元に戻りません。

ROWLOCK などのロック ヒントを使用すると、初期ロック プランのみが変更されます。 ロック ヒントでは、ロックのエスカレーションは防止されません。

ロックエスカレーションのしきい値

ロックエスカレーションは、次のいずれかの条件で発生する可能性があります。

  • メモリしきい値に達しました - ロック メモリの 40% のメモリしきい値に達しました。 ロック メモリがバッファー プールの 24% を超えると、ロック エスカレーションがトリガーされる可能性があります。 ロック メモリは、表示されるバッファー プールの 60% に制限されます。 ロック エスカレーションのしきい値は、ロック メモリの 40% に設定されます。 これは、バッファー プールの 60% の 40%、つまり 24% です。 ロック メモリが 60% の制限を超えた場合 (ロックエスカレーションが無効になっている場合は、はるかに可能性が高くなります)、追加のロックを割り当てようとするすべての試行が失敗し 1204 、エラーが生成されます。

  • ロックしきい値に達する - メモリしきい値がチェックされた後、現在のテーブルまたはインデックスで取得されたロックの数が評価されます。 数値が 5,000 を超えると、ロックエスカレーションがトリガーされます。

しきい値に達したことを理解するには、拡張イベントを使用し、 lock_escalation イベントを有効にして、 escalated_lock_count 列と escalation_cause列を 調べます。 または、 Lock:Escalation イベントを使用し、値を EventSubClass 調べます。"0 - LOCK_THRESHOLD" はステートメントがロックしきい値を超えたことを示し、"1 - MEMORY_THRESHOLD" はステートメントがメモリしきい値を超えたことを示します。 また、 列と IntegerData2 列をIntegerData調べます。

推奨事項

「ロックエスカレーションの 防止 」セクションで説明されているメソッドは、テーブルレベルまたはインスタンスレベルでエスカレーションを無効にするよりも優れたオプションです。 さらに、一般に、予防方法では、ロック エスカレーションを無効にするよりも、クエリのパフォーマンスが向上します。 このトレース フラグは、ロックのエスカレーションによって引き起こされる重大なブロックを軽減するためにのみ有効にすることをお勧めします。この記事で説明されているオプションなど、他のオプションは調査中です。

関連項目