SQL Server でロックのエスカレーションが原因で発生するブロッキング問題を解決する

適用対象: SQL Server

元の KB 番号: 323630

まとめ

ロックエスカレーションは、多くの細かいロック (行ロックやページ ロックなど) を 1 つのテーブル ロックに変換します。 SQL Serverは、ロック数またはロック メモリに基づいてエスカレーションを動的にトリガーし、既定の動作により、ほとんどのワークロードのスループットが向上します。 ただし、クエリやトランザクションの設計によっては、結果のテーブル ロックによって他のユーザーがブロックされる場合があります。 この記事では、ロックエスカレーションがSQL Serverでブロックを引き起こしているかどうかを判断する方法、ロックを防止する方法、および無効にする方法 (およびタイミング) について説明します。

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

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

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

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

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

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

ロックエスカレーションを防ぐ最も簡単で安全な方法は、トランザクションを短くし、ロックエスカレーションのしきい値を超えないように高価なクエリのロック占有領域を減らすことです。 次の 1 つ以上の戦略を使用します。

大規模な操作をより小さなトランザクションに分割する

大きなバッチ操作をいくつかの小さな操作に分割します。 たとえば、次のクエリを実行して、監査テーブルから 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 ステートメントのように見える場合、(クラスター化インデックスと非クラスター化インデックスの両方で) 何千ものキー ロックが取得される可能性があります。 これにより、このようなクエリがロックエスカレーションのしきい値を超える可能性があります。 これは、エスカレートされたロックが共有テーブル ロックである場合に特に重要ですが、これらは既定の "読み取りコミット済み" 分離レベルでは一般的には見られない場合です。 Bookmark Lookup WITH PREFETCH 句がエスカレーションの原因になっている場合は、インデックス シークに表示される非クラスター化インデックスに列を追加するか、クエリ プランの Bookmark Lookup 論理演算子の下に Index Scan 論理演算子を追加することを検討してください。 カバーインデックス (クエリで使用されたテーブル内のすべての列を含むインデックス)、または結合条件に使用された列をカバーするインデックス、または "列の選択" リストにすべてを含めるのが現実的でない場合は WHERE 句で作成できます。

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

別の接続で互換性のあるテーブル レベルのロックを保持する

別の SPID が現在互換性のないテーブル ロックを保持している場合、ロックエスカレーションは発生しません。 ロックのエスカレーションは常にテーブル ロックにエスカレートされ、ページ ロックにはエスカレートされません。 さらに、別の SPID が互換性のない TAB ロックを保持しているためにロックエスカレーションの試行が失敗した場合、エスカレーションを試みたクエリは TAB ロックを待機している間はブロックされません。 代わりに、元のより細かいレベル (行、キー、またはページ) でのロックの取得が続行され、定期的に追加のエスカレーションが試行されます。

Note

この手法は回避策であり、一般的な推奨事項ではありません。 エスカレーションを抑制する期間、開いているトランザクションと IX ロックを保持する必要があります。 前に説明したクエリとトランザクションの機能強化を優先します。

したがって、特定のテーブルでのロックのエスカレーションを防ぐ方法の 1 つは、エスカレートされたロックの種類と互換性のない別の接続に対してロックを取得し、保持することです。 テーブル レベルの IX (意図排他) ロックでは、行やページはロックされませんが、エスカレートされた S (共有) または X (排他) TAB ロックとは互換性がありません。 たとえば、mytable テーブル内の多数の行を変更し、ロックエスカレーションのためにブロックの原因となったバッチ ジョブを実行する必要があるとします。 このジョブが常に 1 時間以内に完了する場合は、次のコードを含む Transact-SQL ジョブを作成し、バッチ ジョブの開始時刻の数分前に新しいジョブを開始するようにスケジュールできます。

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

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

述語が SARGable であることを確認する

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

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

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

  • テーブル レベル: テーブル レベルでロックエスカレーションを無効にすることができます。 以下を参照してください。ALTER TABLE ... SET (LOCK_ESCALATION = DISABLE) 対象となるテーブルを特定するには、T-SQL クエリを調べます。 それが不可能な場合は、 Extended イベントを使用し、 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 のロックとメモリの構成を確認するようにシステム管理者に依頼します。

Note

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

SQL Server 構成マネージャーを使用して、これらのトレース フラグ (-T1211 または -T1224) を追加できます。 新しいスタートアップ パラメーターを有効にするには、SQL Server サービスを再起動する必要があります。 DBCC TRACEON (1211, -1)またはDBCC TRACEON (1224, -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" はステートメントがメモリしきい値を超えたことを示します。 また、 IntegerData 列と IntegerData2 列を調べます。

推奨事項

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

関連するコンテンツ

  • Last updated on