• [アーティクル]

ロックのエスカレーション (データベース エンジン)

ロックのエスカレーションとは、粒度の小さい多数のロックを変換して、粒度が大きい少数のロックにする処理です。この処理により、同時実行の競合が発生する確率は高くなりますが、システムのオーバーヘッドは減少します。

適用対象: SQL Server 2008 R2 以上のバージョン。

SQL Server データベース エンジンは次のように、低いレベルでロックを獲得すると、下位レベルのオブジェクトを含んでいる上位のオブジェクトにもインテント ロックをかけます。

  • データベース エンジンは、行またはインデックス キー範囲をロックするとき、それらの行またはキーを含んでいるページにインテント ロックをかけます。

  • データベース エンジンは、ページをロックするとき、ロックされたページを含む上位レベルのオブジェクトにインテント ロックをかけます。オブジェクトにインテント ロックをかけるだけでなく、次のオブジェクトに対してインテント ページ ロックが要求されます。

    • 非クラスター化インデックスのリーフ レベルのページ

    • クラスター化インデックスのデータ ページ

    • ヒープ データ ページ

データベース エンジンは、ロック数を最小限に抑え、ロックのエスカレーションが必要になる可能性を減らすために、同じステートメントに対して行ロックとページ ロックの両方を行うことがあります。たとえば、非クラスター化インデックスにページ ロックをかけ、(インデックス ノード内でクエリの条件を満たすのに十分な連続するキーが選択されている場合は) データに行ロックをかけることがあります。

ロックをエスカレートするために、データベース エンジンはテーブルのインテント ロックを完全なロックに変更しようとします。たとえば、インテント排他 (IX) ロックを排他 (X) ロックに変更したり、インテント共有 (IS) ロックを共有 (S) ロックに変更する処理を試行します。ロックのエスカレーションが成功し、完全なテーブル ロックが獲得された場合、ヒープまたはインデックスのトランザクションで保持されているすべてのヒープ ロックまたは B ツリー ロック、ページ (PAGE) ロック、または行レベル (RID) ロックが解放されます。完全なロックを獲得できない場合、その時点ではロックのエスカレーションが発生せず、データベース エンジンは行ロック、キー ロック、またはページ ロックの獲得を続行します。

データベース エンジンは、行ロックまたはキー範囲ロックをページ ロックにエスカレートせず、テーブル ロックに直接エスカレートします。同様に、ページ ロックは常にテーブル ロックにエスカレートされます。SQL Server 2008 では、パーティション テーブルのロックを、テーブル ロックの代わりに関連するパーティションの HoBT レベルにエスカレートできます。HoBT レベルのロックでは、パーティションの固定 HoBT は必ずしもロックされません。

注意注

HoBT レベルのロックを使用すると、通常は同時実行性が向上します。ただし、異なるパーティションをロックしている複数のトランザクションで、排他ロックを他のパーティションに拡張する必要が生じた場合に、デットロックが発生する可能性があります。TABLE ロック粒度の方がパフォーマンスが良い場合もまれにあります。

同時実行トランザクションによって保持されているロックの競合により、ロックのエスカレーションが失敗した場合、データベース エンジンは、トランザクションが新たなロックを 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 は、エスカレーションの発生時にまだアクセスされていなかったので、このロックもエスカレートされません。

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

ロックのエスカレーションは、ALTER TABLE SET LOCK_ESCALATION オプションを使用してロックのエスカレーションをテーブルで無効にしていない場合、および次のいずれかの条件が満たされた場合に開始されます。

  • 1 つの Transact-SQL ステートメントがパーティション分割されていない 1 つのテーブルまたはインデックスに対して少なくとも 5,000 個のロックを獲得した場合

  • 1 つの Transact-SQL ステートメントがパーティション テーブルの 1 つのパーティションに対して少なくとも 5,000 個のロックを獲得し、ALTER TABLE SET LOCK_ESCALATION オプションが AUTO に設定されている場合

  • データベース エンジンのインスタンスのロック数がメモリまたは構成のしきい値を超えた場合

ロックの競合によりロックをエスカレートできない場合、データベース エンジンは新たなロックを 1,250 個獲得するごとにロックのエスカレーションを開始します。

Transact-SQL ステートメントのエスカレーションのしきい値

新たなロックを 1,250 個獲得するごとにエスカレーションが発生するかどうかをデータベース エンジンで確認する際、ロックのエスカレーションが発生するのは、Transact-SQL ステートメントがテーブルの参照の 1 つで少なくとも 5,000 個のロックを獲得した場合に限られます。Transact-SQL ステートメントがテーブルの参照の 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 テーブル ヒントを使用します。ただし、このオプションを使用すると、ユーザーが同じデータにアクセスしようとしている他のユーザーをブロックするという問題が増加します。複数の同時接続ユーザーがいるシステムではこのオプションを使用しないでください。

  • パーティション テーブルで、テーブル レベルではなく HoBT レベルにロックをエスカレートしたり、ロックのエスカレーションを無効にしたりするには、ALTER TABLE の LOCK_ESCALATION オプションを使用します。

トレース フラグ 1211 と 1224 を使用して、すべてまたは一部のロックのエスカレーションを無効にすることもできます。詳細については、「トレース フラグ (Transact-SQL)」を参照してください。また、SQL Server Profiler の Lock:Escalation イベントを使用してロックのエスカレーションを監視することもできます。詳細については、「SQL Server Profiler の使用」を参照してください。