インデックスのロックのカスタマイズ

SQL Server データベース エンジンでは、ほとんどの場合において、クエリに対し最適なロック粒度を自動的に選択するという動的ロック ストラテジを採用しています。テーブルまたはインデックスのアクセス パターンが一定していることがわかっていて、リソースの競合を解決する必要がある場合を除き、ページと行のロックがオンになっている既定のロック レベルをオーバーライドしないことをお勧めします。ロック レベルをオーバーライドすると、テーブルまたはインデックスへの同時アクセスのパフォーマンスが大きく低下することがあります。たとえば、ユーザーが頻繁にアクセスする大きなテーブルに対してテーブルレベルのロックのみを指定すると、ボトルネックが発生します。これは、ユーザーがテーブルにアクセスする前に、テーブルレベルのロックが解除されるのを待たなければならなくなるためです。

アクセス パターンが一定していることがわかっている場合、ページまたは行のロックを禁止することが効果的なケースもいくつかあります。たとえば、あるデータベース アプリケーションでバッチ処理により週単位で更新される参照テーブルを使用しているとします。共有 (S) ロックされているテーブルに、同時に読み取りを行うユーザーがアクセスし、排他 (X) ロックされているテーブルに、週に 1 回のバッチ更新がアクセスします。テーブル上でページと行のロックをオフにし、読み取り側が共有テーブル ロックを通過して同時にテーブルにアクセスできるようにすることで、週を通したロック オーバーヘッドを小さくできます。バッチ ジョブの実行時には、排他テーブル ロックを獲得できるので、効率的に更新を完了できます。

週に 1 回のバッチ更新による更新の実行中は同時読み取りによるテーブルへのアクセスがブロックされるため、ページと行のロックをオフにすることが適切かどうかは状況によって異なります。バッチ ジョブによっていくつかの行またはページのみが変更される場合は、行またはページ レベルのロックを許可するようにロック レベルを変更できます。この場合、他のセッションはブロックされることなくテーブルから読み出しを行うことができます。バッチ ジョブで大量の更新を扱う場合は、バッチ ジョブを効率的に完了するためにテーブル上で排他ロックを取得することが最善の方法です。

2 つの同時処理が同じテーブル上で行ロックを取得した後にブロックすると、どちらもページをロックする必要があるため、デッドロックが発生する場合があります。行ロックを禁止することで、どちらかの処理が待機するようになるため、デッドロックを回避できます。

インデックスで使用するロックの粒度は、CREATE INDEX ステートメントまたは ALTER INDEX ステートメントを使用して設定できます。ロック設定は、インデックス ページとテーブル ページの両方に適用されます。また、CREATE TABLE ステートメントと ALTER TABLE ステートメントを使用して、PRIMARY KEY 制約および UNIQUE 制約によるロック粒度を設定することもできます。旧バージョンとの互換性を維持しているため、sp_indexoption システム ストアド プロシージャを使用して粒度を設定することもできます。特定のインデックスの現在のロックのオプションを表示するには、INDEXPROPERTY 関数を使用します。特定のインデックスに対して、ページレベルのロック、行レベルのロック、またはこの 2 つのロックの組み合わせを禁止することができます。