次の方法で共有

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

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

  • 別の SPID が現在互換性のないテーブル ロックを保持している場合、ロックエスカレーションは発生しません。 ロックのエスカレーションは常にテーブル ロックにエスカレートされ、ページ ロックにはエスカレートされません。 さらに、別の SPID が互換性のない TAB ロックを保持しているためにロックエスカレーションの試行が失敗した場合、エスカレーションを試みたクエリは TAB ロックを待機している間はブロックされません。 代わりに、元のより細かいレベル (行、キー、またはページ) でのロックの取得が続行され、定期的に追加のエスカレーションが試行されます。 したがって、特定のテーブルでのロックのエスカレーションを防ぐ方法の 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 を使用してページまたは行ロックを無効にしている場合を除きます)。

  • 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 列を調べます。

推奨事項

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

関連項目