ロックのエスカレーション (データベース エンジン)
ロックのエスカレーションとは、粒度の小さい多数のロックを変換して、粒度が大きい少数のロックにする処理です。この処理により、同時実行の競合が発生する確率は高くなりますが、システムのオーバーヘッドは減少します。
Microsoft SQL Server データベース エンジンは次のように、低いレベルでロックを獲得すると、下位レベルのオブジェクトを含んでいる上位のオブジェクトにもインテント ロックをかけます。
- データベース エンジンは、行またはインデックス キー範囲をロックするとき、それらの行またはキーを含んでいるページにインテント ロックをかけます。
- データベース エンジンは、ページをロックするとき、ロックされたページを含む上位レベルのオブジェクトにインテント ロックをかけます。テーブルにインテント オブジェクト (OBJECT) ロックをかけるだけでなく、次の項目に対してインテント ヒープ ロックまたは B ツリー (HOBT) ロックが要求されます。
- 非クラスタ化インデックス (ページが非クラスタ化インデックス ページの場合)。
- クラスタ化インデックス (ページがクラスタ化インデックス ページの場合)。クラスタ化インデックスを保持するテーブルのデータ ページが含まれます。
- データ ページのヒープ (ページが、クラスタ化インデックスを保持しないテーブル内のデータ ページである場合)。
データベース エンジンは、ロック数を最小限に抑え、ロックのエスカレーションが必要になる可能性を減らすために、同じステートメントに対して行ロックとページ ロックの両方を行うことがあります。たとえば、非クラスタ化インデックスにページ ロックをかけ、(インデックス ノード内でクエリの条件を満たすのに十分な連続するキーが選択されている場合は) データに行ロックをかけることがあります。
ロックをエスカレートするために、データベース エンジンはテーブルのインテント ロックを完全なロックに変更しようとします。たとえば、インテント排他 (IX) ロックを排他 (X) ロックに変更したり、インテント共有 (IS) ロックを共有 (S) ロックに変更する処理を試行します。ロックのエスカレーションが成功し、完全なテーブル ロックが獲得された場合、ヒープまたはインデックスのトランザクションで保持されているすべてのヒープ ロックまたは B ツリー ロック、ページ (PAGE) ロック、キー範囲 (KEY) ロック、または行レベル (RID) ロックが解放されます。完全なロックを獲得できない場合、その時点ではロックのエスカレーションが発生せず、データベース エンジンは行ロック、キー ロック、またはページ ロックの獲得を続行します。
データベース エンジンは、行ロックまたはキー範囲ロックをページ ロックにエスカレートせず、テーブル ロックに直接エスカレートします。同様に、ページ ロックは常にテーブル ロックにエスカレートされます。
同時実行トランザクションによって保持されているロックの競合により、ロックのエスカレーションが失敗した場合、データベース エンジンは、トランザクションが新たなロックを 1,250 個獲得するごとにロックのエスカレーションを再試行します。
各エスカレーション イベントは、主に単一の Transact-SQL ステートメントのレベルで動作します。イベントの開始時に、アクティブなステートメントがエスカレーションのしきい値に関する要件を満たしていれば、そのステートメントによってそれまで参照されていたすべてのテーブルについて、データベース エンジンは現在のトランザクションによって所有されているすべてのロックのエスカレーションを試行します。ステートメントがテーブルをアクセスする前にエスカレーション イベントが開始した場合、そのテーブルについてロックのエスカレーションは試行されません。ロックのエスカレーションが成功した場合、そのテーブルが現在のステートメントによって参照され、エスカレーション イベントに含まれていれば、以前のステートメントでトランザクションが獲得したロックのうち、イベントの開始時にまだ保持されているロックはすべてエスカレートされます。
たとえば、セッションで次の操作が実行されると仮定します。
- トランザクションを開始します。
- TableA を更新します。この更新により、TableA の排他行ロックが生成され、トランザクションが完了するまで保持されます。
- TableB を更新します。この更新により、TableB の排他行ロックが生成され、トランザクションが完了するまで保持されます。
- TableA と TableC を結合する SELECT を実行します。クエリの実行プランでは、行を TableC から獲得する前に TableA から獲得する必要があります。
- SELECT ステートメントを実行すると、TableA の行の取得中、かつ、TableC がアクセスされる前に、ロックのエスカレーションが発生します。
ロックのエスカレーションが成功した場合、TableA のセッションで保持されているロックのみがエスカレートされます。エスカレート対象のロックには、SELECT ステートメントからの共有ロックおよび以前の UPDATE ステートメントからの排他ロックがどちらも含まれます。ロックのエスカレーションを行う必要があるかどうかを判断するために、セッションが SELECT ステートメントの TableA で獲得したロックだけがカウントされますが、エスカレーションが成功した後は、TableA のセッションによって保持されているすべてのロックがテーブルの排他ロックにエスカレートされ、TableA にかけられているその他のすべての小さな単位のロック (インテント ロックなど) が解放されます。
SELECT ステートメントで TableB にはアクティブな参照がなかったので、TableB のロックはエスカレートされません。同様に、TableC は、エスカレーションの発生時にまだアクセスされていなかったので、このロックもエスカレートされません。
ロックのエスカレーションのしきい値
ロックのエスカレーションは、次のいずれかの時点で開始されます。
- 1 つの Transact-SQL ステートメントが単独のテーブルまたはインデックスに対して、少なくとも 5,000 個のロックを獲得したとき。
- データベース エンジンのインスタンスのロック数がメモリまたは構成のしきい値を超えたとき。
ロックの競合によりロックをエスカレートできない場合、データベース エンジンは新たなロックを 1,250 個獲得するごとにロックのエスカレーションを開始します。
Transact-SQL ステートメントのエスカレーションのしきい値
Transact-SQL ステートメントがテーブルまたはインデックスの参照の 1 つ (テーブルがパーティション分割されている場合、テーブル パーティションまたはインデックス パーティションの参照の 1 つ) で少なくとも 5,000 個のロックを獲得すると、ロックのエスカレーションが開始されます。たとえば、ステートメントの結果として 1 つのインデックスに 3,000 個のロックを獲得し、同じテーブルの別のインデックスに 3,000 個のロックを獲得した場合、ロックのエスカレーションは発生しません。同様に、ステートメントがテーブルで自己結合を保持しており、そのテーブルへの各参照によってそのテーブルの 3,000 個のロックのみが獲得される場合、ロックのエスカレーションは発生しません。
ロックのエスカレーションは、エスカレーションの発生時にアクセスされていたテーブルのみについて発生します。1 つの SELECT ステートメントが、TableA、TableB、および TableC という 3 つのテーブルにこの順序でアクセスする結合であると仮定します。このステートメントでは TableA のクラスタ化インデックスに 3,000 個の行ロックを獲得し、TableB のクラスタ化インデックスに少なくとも 5,000 個の行ロックを獲得しますが、TableC はまだアクセスされていません。データベース エンジンが、ステートメントで TableB に少なくとも 5,000 個の行ロックを獲得したことを検出すると、TableB の現在のトランザクションで保持されているすべてのロックがエスカレートされます。また、TableA の現在のトランザクションで保持されているすべてのロックのエスカレートが試行されますが、TableA のロック数が 5,000 未満なので、エスカレーションは成功しません。TableC はエスカレーションの発生時にまだアクセスされていなかったので、エスカレーションが試行されません。
データベース エンジンのインスタンスのエスカレーションのしきい値
ロックのエスカレーションに関するメモリのしきい値よりもロック数が大きくなると、データベース エンジンは必ずロックのエスカレーションを開始します。次のように、メモリのしきい値は locks 構成オプションの設定によって異なります。
- locks オプションを既定値 0 に設定している場合、AWE メモリを除き、ロック オブジェクトによって使用されるメモリがデータベース エンジンによって使用されるメモリの 24% になると、ロックのエスカレーションのしきい値に達します。ロックを表すために使用するデータ構造のサイズは約 100 バイトです。データベース エンジンでは、変化するワークロードに合わせて調整するためにメモリが動的に確保および解放されるので、このしきい値は動的です。
- locks オプションの値が 0 以外の場合、ロックのエスカレーションのしきい値は locks オプションの値の 40% になります (メモリに負荷がかかっている場合は 40% 未満になります)。
データベース エンジンは、エスカレーション対象として、任意のセッションの任意のアクティブなステートメントを選択します。また、インスタンスで使用されるロックのメモリがしきい値よりも大きい間は、1,250 個の新しいロックごとに、エスカレーション対象のステートメントを選択します。
混在した種類のロックのエスカレーション
ロックのエスカレーションが発生する際に、ヒープまたはインデックス用に選択されるのは、制限が最も強い下位レベルのロックの要件を満たすことができるほど強力なロックです。
たとえば、セッションで次の操作が実行されると仮定します。
- トランザクションを開始します。
- クラスタ化インデックスを含むテーブルを更新します。
- 同じテーブルを参照する SELECT ステートメントを実行します。
UPDATE ステートメントにより、次のロックが獲得されます。
- 更新されたデータ行での排他 (X) ロック。
- 更新されたデータ行を含むクラスタ化インデックス ページでのインテント排他 (IX) ロック。
- クラスタ化インデックスでの IX ロックおよびそのテーブルでの IX ロック。
SELECT ステートメントにより、次のロックが獲得されます。
- SELECT ステートメントによって読み取られるすべてのデータ行での共有 (S) ロック (行が、UPDATE ステートメントからの X ロックによって既に保護されている場合を除く)。
- これらの行を含むすべてのクラスタ化インデックス ページでのインテント共有ロック (ページが、IX ロックによって既に保護されている場合を除く)。
- クラスタ化インデックスまたはテーブルは IX ロックによって既に保護されているので、これらに対するロックは獲得されません。
SELECT ステートメントによってロックのエスカレーションが発生するのに十分なロックが獲得され、エスカレーションが成功した場合、テーブルの IX ロックは X ロックに変換され、すべての行ロック、ページ ロック、およびインデックス ロックが解放されます。更新と読み取りはどちらもテーブルの X ロックで保護されます。
ロックとエスカレーションの削減
ほとんどの場合、データベース エンジンでは、ロックおよびロックのエスカレーションの動作は既定値を使用することで、最高のパフォーマンスを得ることができます。データベース エンジンのインスタンスによって多数のロックが生成され、頻繁にロックのエスカレーションが実行されている場合は、次の操作を実行してロックの量を減らすことを検討してください。
- 読み取り操作で共有ロックを生成しない分離レベルを使用します。
- READ COMMITTED 分離レベル (READ_COMMITTED_SNAPSHOT データベース オプションが ON の場合)。
- SNAPSHOT 分離レベル。
- READ UNCOMMITTED 分離レベル。ダーティ リードでの動作が可能なシステムでのみ使用できます。
- データベース エンジンによって行ロックではなくページ ロック、ヒープ ロック、またはインデックス ロックが使用されるように、PAGLOCK テーブル ヒントまたは TABLOCK テーブル ヒントを使用します。ただし、このオプションを使用すると、ユーザーが同じデータにアクセスしようとしている他のユーザーをブロックするという問題が増加します。複数の同時接続ユーザーがいるシステムではこのオプションを使用しないでください。
トレース フラグ 1211 と 1224 を使用して、すべてまたは一部のロックのエスカレーションを無効にすることもできます。詳細については、「トレース フラグ (Transact-SQL)」を参照してください。また、SQL Server Profiler の Lock:Escalation イベントを使用してロックのエスカレーションを監視することもできます。詳細については、「SQL Server Profiler の使用」を参照してください。
参照
概念
データベース エンジンにおける分離レベル
ロックの粒度と階層
ロック モード
ロックの互換性 (データベース エンジン)
locks オプション
その他の技術情報
SET TRANSACTION ISOLATION LEVEL (Transact-SQL)
テーブル ヒント (Transact-SQL)
テーブルとインデックスのアーキテクチャ