SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

SQL Server / SQL Database パフォーマンスチューニング & トラブルシューティング シリーズ : SQL Server のロックの基本的な動作

leave a comment

SQL Server / SQL Database の実運用環境では、ロックについて悩まされることが多々あるのではないでしょうか。

SQL Server のロックの基本的な動作の理解はトラブルシューティングでは重要となりますので、SQL Server のロックの基礎について、本シリーズでもまとめておきたいと思います。

今回は次のようなテーブルを例にして、解説を行いたいと思います。

SET NOCOUNT ON
GO

DROP TABLE IF EXISTS LockTEST
CREATE TABLE LockTEST(
    C1 int identity primary key,
    C2 varchar(36) DEFAULT NEWID(),
    C3 float DEFAULT RAND() * 10,
    C4 float DEFAULT RAND() * 100
    INDEX NCCIX_LockTEST_C3 (C3),
    INDEX NCCIX_LockTEST_C4 (C4)
)
GO

INSERT INTO LockTEST DEFAULT VALUES
GO 10000

 

ロックについての公式ドキュメント

 

SQL Server のロックについての公式ドキュメントは、次のものとなります。

このドキュメントは SQL Server のロックについて調査をする際には、必ず目を通すドキュメントとなります。

この情報の中でも、ロックの互換性 の互換性モードの次の表については、頻繁に参照することになりますので、公式のドキュメントとしてはこのドキュメントの情報を覚えておいてください。

 

 

SQL Server のロックの基本的な考え方

 

SQL Server の データベースエンジンの分離レベル については「READ COMMITTED」がデフォルトの設定となります。

(SQL Database では「READ COMMITTED SNAPSHOT : RCSI」がデフォルトとなり、挙動が変わりますので、これは別の機会に触れたいと思います)

SQL Server の READ COMMITTED の動作は次のように規定されています。

image

 

SQL Server の既定の READ COMMITTED では、データの読み取り時にはロックが取得され、該当のデータの読み取りが完了したタイミングでロックが解放されます。

このようなデータに対して検索を実行した場合を考えてみます。

image

 

データの検索を実行した場合

SELECT * FROM LockTEST WHERE C1 IN (1,3,5)

 

C1 = 1 のデータを読んでいる最中にデータの更新が行われてしまうと、データの整合性が取れなくなるため、データを読み取っている最中については、更新がかけられないように「該当レコードへの更新をブロック」するためのロックが取得されます。

image

検索によって取得されたロックについては、他のセッションからの検索は許容されます。

他のセッションからデータを変更しようとした場合は、ブロックされます。

しかし、他セッションから同じレコードを参照された場合については、競合しない互換性のロックを取得していますので、他のプログラムから同一のデータを検索することが可能です。

image

 

該当レコードの情報の取得が終わったタイミングでロックが解放され、次のアクセス対象のデータのロックを取得します。

image

検索については、これを繰り返しながら処理を行っていきます。

このようなロック解放は分離レベルを「READ COMMITTED」で利用している場合の動作となります。

SQL Server では、次のような 4 種類の分離レベルを使用することができ、分離レベルによってどのようなロックが取得されるかは変わってきます。

image

 

データの更新を実行した場合

それではデータの更新を行う場合はどうなるでしょうか。

次のようなクエリを実行します

 UPDATE LockTEST SET C2 = NEWID() WHERE C1 = 4

 

データの更新を行い、コミットが行われるまでは、該当のレコードに対してロックを行った状態になります。

この時のロックですが「更新が完了するまで、変更対象のレコードが排他アクセスとなるためのロック」が取得されます。

image

更新が完了する前に次のクエリが実行されたとします。

SELECT * FROM LockTEST WHERE C1 = 4

READ COMMITED の分離レベルでは検索時にデータに対してのロックが取得されます。

「C1 = 4」のレコードについては、コミットが完了するまで排他アクセスとなるロックが取得されています。

この状態で READ COMMITTED の分離レベルで実行されているクエリについては、データ更新による排他アクセスとなるためのロックの影響を受けますので、更新が完了するまで SELECT についてもブロックがされることになります。

image

 

SQL Server 既定の分離レベルである READ COMMITTED 分離レベルの基本的な動作についてはこのようになります。

 

ロックの粒度

 

ロックには「粒度」という考え方があります。

データの更新を実行する場合には、該当のデータに対して排他アクセスの状態とする必要がありますが、この時データに対して排他的にアクセスを行わせるために、最初からテーブル全体を排他アクセス対象としてしまうと、他のプログラムから更新に関係のないデータについてのアクセスもできなくなり、同時実行性が著しく低下してしまいます。

image

操作対象のレコード (リソース) にのみロックがかかっている状態であれば、同時実行性を著しく損なうことはありません。

image

「どのような範囲 / リソースに対してロックをかけるか」の考え方が「ロックの粒度」になります。

SQL Server では、次のような粒度でロックを取得することができます。

image

 

このリソースは階層構造となっており、一般的に考慮すべきリソースで考えた場合は次のような階層構造となります。

image

  • オブジェクト単位で考えた場合、階層の最上段はデータベースとなります
  • SQL Server のデータベースは、一つまたは、複数のデータファイルで構成されます
  • データファイルの中には、複数のテーブルが作成されます
  • テーブルのデータはエクステントという 8 個のページをまとめた管理単位でデータが割り当てられます
  • テーブルのレコードは、ページ内に格納されます
  • 格納されたレコードは KEY または RID という一意識別子により認識されます

ロックは、このような階層構造となっており、実行される操作によって、リソースに対してどの粒度ロックが取得されているのかが変わってきます。

SELECT を実行した場合の例としては、次のようなロックが取得されるケースがあります。

(厳密には、データや他セッションから実行されているクエリの状態によっては KEY:S は発生せず、PAGE:ISで完了するケースもあるはずですが)

  1. DATABASE: S
  2. OBJECT : IS
  3. PAGE : IS
  4. KEY : S

UPDATE を実行した場合の例としては、次のようなロックが取得されるケースがあります。

  1. DATABASE : S
  2. OBJECT : IX
  3. PAGE : IU
  4. KEY : U
  5. PAGE : IX
  6. KEY : X

 

SELECT を行うと、該当レコードの KEY に対して、S というモードのロックが取得されますが、それ以外にもいくつかのリソースに対して、ロックが取得されています。

(「ロックのモード」については後述します)

UPDATEについても、X というロックが KEY に対して取得されていますが、それ以外にもいくつかのリソースに対してロックが取得されていますね。

 

インテントロックによる階層の表現

SQL Server には「インテント ロック (I_)」というロックモードがあり、このロックを使用することで、取得されているロックの階層構造が表現されます。

単純に KEY のロックだけ取得している状態ですと、上位の階層に対しての変更が可能となってしまい、「レコードの参照が完了していない状態でテーブルの定義の変更が実行できてしまう」というような状態が発生する可能性があります。

image

変更処理が行われる前に、ページやオブジェクトが削除されるとデータとしての整合性が取れなくなってしまいます。

ロックの互換性の比較は「同一の粒度のリソース」に対して実施しますので、単純にレコードに対してのみロックをかけている状態ではそれより上位の階層の変更ができてしまう可能性があります。

このような状態を回避するために、SQL Server では、下位の階層のリソースを操作している最中には「上位の階層のリソースに対してインテントロック」が取得されていることで、階層の整合性を保つことができます。

インテントロックを主語として見た場合は、「インテントロックを取得しているリソースより下位の階層でロックが取得されている状態になる」ということになります。

 

image

緑がインテントロックとなるのですが、レコードにアクセスをしたのであれば、

  • そのレコードが格納されているページ (格納領域) に対してインテントロックを取得
  • そのレコードが格納されているオブジェクトに対してインテントロックを取得

というような動作が行われます。

ロックには、ロックの互換性 があり、競合するロックと競合しないロックがあります。

インテントロック (I_ のロック) は、他のインテントロックとは競合は発生しませんので、インテントロック間で同時実行性が低下することはありません。

image

しかし、通常のロックとは競合が発生します。

image

SELECT を実行した際には、オブジェクトに対して「IS」のロックが取得されます。

このロックは X のロックや、SCH-M のロックと競合 (Confilict) しますので、インテントロックが取得されているオブジェクトに対しては、スキーマの変更ができないことになります。

実際に操作する対象の上位の階層のリソースに対して、インテントロックが取得されることで、処理対象を含むオブジェクト (リソース) の階層の整合性を適切に保つことができるようになります。

インテントロックについては、ドキュメントの説明 の次の部分をイメージできるとわかりやすいのではないでしょうか。

他のトランザクションが上位のリソースを変更することにより下位レベルのロックを無効にしてしまうことを防ぐ。

 

 

S / IS というようなロックの種類のことを「ロック モード」といい、実行されたクエリに応じて、「適切なロックモード」のロックが自動的に取得されます。

この「ロックモード」が、次に考えるべき内容です。

 

ロックモード

 

ロックを取得する際には「粒度」と「ロックモード」が重要な内容となります。

ロックの粒度で「DATABSE : S」というような記述を行いました。

この「S」というような記述の部分がロックモードです。

ロックはロックモードにより「他のセッションからのどのようなデータアクセスを許可するか」が決まります。

SQL Server のロックモードには次のような種類があります。

image

更新を行うときには、「排他 (X)」のロックが取得され、検索を実行した場合には、「共有 (S)」のロックが取得されます。

ロックには、「互換性」があり、この互換性については、ロックの互換性 から確認することができます。

 

ロック互換性によるロックの競合

X のロックと S のロックは「Conflict (競合)」となっていますので、この種類のロックが同時に発生している場合は、ロックの競合により待ちが発生することになります。

image

ロックの競合については、基本的には「同一の粒度のリソースに対して取得しているロックモード」で比較を行うことになります。

「C1 = 4」のデータを更新中に、他のセッションから、テーブルの変更を行うクエリを実行してみます。

ALTER TABLE LockTEST ADD  C5 int

 

ALTER TABLE は次のようなロックが取得されます。

  1. DATABASE : S
  2. OBJECT : SCH_S
  3. OBJECT : SCH_M
  4. METADATA : SCH_S

左が既に取得されている UPDATE により取得されているロック、右が ALTER TABLE によって新規に取得を試みているロックです。

image

UPDATE は該当レコードに対しての排他のロックは取得し、オブジェクトに関しては「IX (インテント排他ロック )」 を取得しています。

ALTER TABLE は、オブジェクトに対して「SCH_M (スキーマ修正)」のロックを取得します。

同じ「オブジェクトという粒度」で、UPDATE によって既にに取得されている「IX」のロックと、ALTER により取得しようとした「SCH-M」のロックの互換性はありません (Conflict する)

image

この二つのクエリが合致した場合には、「ロックの競合によるブロッキング」が発生することになります。

SQL Server では何らかのクエリを実行する場合、「アクセス対象となるオブジェクトに対してインテントロックを取得する」ケースが多いです。

インテントロックと SCH-M のロックは互換性がないため、競合が発生します。

image

そのため、SCH-M のロックがオブジェクトに対して長時間取られているようなクエリが実行されている場合は、他のセッションで実行されているクエリとロックの競合を起こす原因となりやすいです。

 

ロック競合によるブロッキングとは？

 

「同一のリソースに対して、互換性のないロックモードによるロック取得」が同時に発生している場合、ロックは競合 (Conflict) し、クエリの処理の待機が発生します。

UPDATE LockTEST SET C2 = NEWID() WHERE C1 = 4

image

この状態のことを「ロック競合によりブロッキングが発生している」と言います。

 

「C1 = 4」に対して、UPDATE を実行した場合、該当のレコードに対して排他アクセスの状態とするためのロックが取得されます。

(SQL Server では、UPDATE を実行した場合は、該当のレコードに対して U のロックを取得 (該当レコードの取得) してから、X のロックに変換 (該当レコードの更新) を行うというような流れとなり、二つのモードのロックを使用して処理が行われています)

UPDATE のコミットが行われるまでは「KEY:X」のロックが取得されたままとなります。

image

この状態で SELECT により「KEY:S」が取得された場合、S と X は互換性がないため、「ロックの競合」が発生します。

image

この状態のことを「ロック競合によりブロッキングが発生している」といいます。

ロック競合によるブロッキングは互換性のないロックの取得が同時に発生している状態であり、原因となっている最初のロックが解放されることで、ブロッキングの解消が行われます。

 

ブロッキングチェーン

ロックについては、待機リスト (Waiter List) によって、ロックの待機状態が管理されており、最初のロック競合が解消されないと、次の図のようにロックの競合が連鎖してしまい、同時実行性が大幅に低下する可能性があります。

(この図は簡略化したものであり、実際にはロックパーティションや低優先度なロック (Low Priority) という概念もありますので、実行方法 / 環境によってロック競合の状態は変わることがあります)

image

上の図のような情報を「ブロッキングチェーン」と呼び、チェーンの先頭になっているセッション ID (SPID) のことを「ヘッドブロッカー」と呼びます。

上記の図であれば、「セッション ID 197 がヘッドブロッカー」となっており、このセッションで取得されているロックによって、同一のレコードを / オブジェクトを取得するための処理の同時実行性の低下が発生していることになります。

 

オブジェクトに対しての ALTER がブロックされると、以降に実行された、「ヘッドブロッカーの UPDATE 対象となっているレコード以外へのアクセス」についてもブロッキング対象となる可能性が出てきます。

image

「C1 ＝ 6」という UPDATE 外のレコードの取得ですが、この SELECT を実行した前には、ALTER によりオブジェクトに対しての「OBJECT:SCH-M」の取得要求が待機リストの中に格納されています。

「OBJECT:SCH-M」と検索時に取得される「OBJECT:IS」のロックは、互換性がなく競合の対象となりますので、以降は、このテーブルに対してのデータ取得に対してもブロッキング対象となってきます。

image

 

 

ブロッキングチェーンの取得

ブロッキングチェーンの簡易の情報であれば、次のような情報で取得できます。

SELECT session_id, blocking_session_id, wait_type, wait_resource 
FROM sys.dm_exec_requests 
WHERE 
(blocking_session_id IS NOT NULL AND blocking_session_id <> 0 )

 

sys.dm_exec_requests には、「blocking_session_id」(ブロック原因となっているセッション ID) という情報を取得することができますので、これを利用すると、ブロッキングチェーンの情報を取得することができます。

ブロッキングの関連性が必要になった場合は、再帰クエリで表現でき、ブロッキングチェーンの取得.sql でサンプルを公開していますので、こちらも利用してみてください。

ロック競合の状態を階層で確認することができます。

image

 

ロック競合の取得

SQL Server では、デッドロックモニターの仕組みを利用した「ブロッキング プロセス レポート」を取得する方法もあります。

これは、blocked process threshold サーバー構成オプション を設定することで取得することができるようになり、指定した秒数、ロック競合によってブロッキングが発生している場合には、ブロッキングのレポートが取得できるようになります。

ドキュメントに記載されている例としては、

sp_configure 'show advanced options', 1 ;  
GO  
RECONFIGURE ;  
GO  
sp_configure 'blocked process threshold', 20 ;  
GO  
RECONFIGURE ;  
GO

 

を実行することで、20 秒のロック競合が発生した場合に、ブロッキングのレポートを取得することができるようになります。

この秒数ですが、クエリを実行しているプログラムの「コマンドタイムアウト以下に設定」をすることが基本ではないでしょうか。(コマンドタイムアウト以上に設定をしても、レポートの秒数に達する前にコマンドが終了してしまいますので)

「blocked process threshold」の設定は、単体では効果はなく、ログ取得の仕組みと組み合わせる必要があります。

設定を行った後は、拡張イベントの「blocked_process_report」のイベントを取得することで、ブロッキングのレポートをログとして残すことができます。

image

このレポートには、どのようなクエリ間でロックの競合が発生したかの情報が出力されますので、ブロッキングの解決を行うための重要な情報となります。

image

 

ロックタイムアウトによるロック競合時間の緩和

SQL Server には「ロック タイムアウト」の設定により、ロック競合が発生した場合のタイムアウトの閾値をミリ秒単位で指定することができます。

基本的にロックタイムアウトは「-1 (無制限)」になっていることが多く、ロック競合によりタイムアウトはせず、クエリのタイムアウトによってタイムアウトするのが一般的かと思います。

強すぎるロック (例 : OBJECT : SCH-M) のロックが長時間取られていると、そのロック競合に引きずられて、様々なクエリでブロッキングが発生し、大量 / 複雑なブロッキングチェーンが発生する可能性があります。

SSMS からクエリを実行する場合は、「ロックタイムアウト = 無制限」「クエリタイムアウト = 無制限」で実行されるのが既定の設定となっていますので、何かのメンテナンスを実行する際にロック競合が気になる場合は、ロックタイムアウトの設定も使ってみるとよいかと思います。

注意が必要な点としては、「処理によっては、ロックタイムアウトが発生してもトランザクションが残った状態となる可能性がある」ことです。

例としては統計情報の自動更新の設定を行う「sp_autostats」と「SET LOCK_TIMEOUT」を組み合わせた場合などです。

SET LOCK_TIMEOUT 1000
EXEC sp_autostats 'LockTest', 'OFF'

WHILE(@@TRANCOUNT > 0)
BEGIN
    ROLLBACK TRAN
END

 

sp_autostats では、処理内でトランザクションを明示的に開いており、どのタイミングでロック競合が発生するかによって、トランザクションの状態が変わってきます。

ロックタイムアウトが発生して処理が中断された場合にトランザクションが開かれた状態になっている可能性もあり、その場合は、セッションの切断 / リセットが行われないと、ロックを保持したままになる可能性があります。

LOCK_TIMEOUT によりロック競合発生時のタイムアウトを指定する際には、処理によっては、エラーとなった場合に開かれているトランザクションが存在しないかの確認についても検討をした方が良いかと思います。

(SSMS から実行している場合はクエリエディタを閉じるのも方法の一つです)

 

NOLOCK により取得されるロックでもロック競合は発生する

SQL Server では「NOLOCK」というオプションを指定することもできます。

SELECT * FROM LockTEST WITH(NOLOCK) WHERE C1 = 4

 

NOLOCK ヒントを指定してクエリを実行した場合、次のようなロックが取得されます。

  • DATABASE : S
  • OBJECT : SCH-S

データ自体にロックは取得されませんが、「オブジェクトに対してのスキーマ安定度ロック (SCH-S)」は取得されます。

SCH-S と SCH-M は競合するロックとなります。

image

NOLOCK ロックヒントはロックをかけない人ではなく、最小限のロックは取得されており、このロックはクエリの完了まで保持されますので、「NOLOCK ロックヒントを設定して長時間実行されているクエリ」は、「SCH-M」のロックが必要となるクエリのブロッカーとなりえる可能性があります。

これについては、逆のことも言え、SCH-M のロックが取得されている場合には、NOLOCK ヒントを指定してもデータにアクセスすることはできません。

NOLOCK ヒントは「データに対してはロックを取得しないが、他のリソースに対してのロックは取得される」動作となりますので、この部分については抑えておいた方が良いかもしれませんね。

この動作は NOLOCK ヒントだけでなく、分離レベルとして「READ UNCOMMITTED」を指定している場合も同様となります。

 

 

ブロッキングの解決には、「ロックは可能な限り短い時間で解放を行う」ことが重要となり、「処理時間を短縮する / 不要なデータにアクセスを行わない」ことはロック競合を解決する際の大きなポイントとなるのではないでしょうか。

 

 

操作によって取得されるロックを把握する

 

ここまでで「ロックの粒度」と「ロック モード」について見ていきました。

「実行されているクエリによって、どのようなロックが取得されるのか？」を把握することは、ロックの問題を対応するにあたって重要な情報となります。

SQL Server は 動的ロック ストラテジ により、取得するロックを判断するため、実行されているクエリの状態によっては、全く同じロックになるとは限りませんが、取得されるロックの傾向を確認することはできます。

 

私が使用するケースの多い、取得されるロックの把握方法は 2 種類です。

 

拡張イベントから取得する

拡張イベントには「lock_acquired」「lock_released」というイベントがあり、これらのイベントを取得することで、ロックの取得と解放の情報を確認することができます。

image

これらのイベントを取得することで、次のような情報を確認することができます。

image

「自分が実行する操作に対してどのようなロックが取得されるか」を確認できていると、操作によって同時実行性にどのような影響が発生するかを把握することができます。

単純な SELECT について、このような情報を取得する必要性はないかもしれませんが、テーブルや DB に対して何らかのメンテナンスを実施する場合には、そのメンテナンスのための操作によって取得されているロックを把握しておくことで、メンテナンス作業による影響を確認することができます。

 

トランザクションログから確認する

更新系の処理については、トランザクションログにロックの情報が出力されていますので、この情報も活用できます。

UPDATE LockTEST SET C2 = NEWID() WHERE C1 = 4

 

このクエリによって取得された更新に対してのトランザクションログの情報を確認するというアプローチが可能です。


SELECT
    [Transaction ID], 
    [Transaction Name],
    Operation,
    Context,
    AllocUnitName,
    [Page ID],
    [Slot ID],
    [Lock Information]
FROM sys.fn_dblog(NULL, NULL)
WHERE [Transaction ID] IN(
    SELECT [Transaction ID] FROM sys.fn_dblog(NULL, NULL) WHERE [Transaction Name] = 'UPDATE'
)

 

このクエリを実行することで、次のような情報を取得することができ、「Lock Information」から、ステートメントにより変更が行われる際に要求されたロックの情報を確認することができます。

image

拡張イベントと異なり、全てのロックの情報を取得することはできないのですが、SQL によってどの情報が更新され、どのようなロックが取得されるかを確認する場合には、トランザクションログの内容から確認をしてみると、直ぐに判断できるケースもあります。

 

ロックリソースの把握

上記の方法でロックの情報を取得すると、「ロックリソース」の情報を確認することができます。

ロックリソースは次のような情報です。

  • KEY: 19:72057594050641920 (a0c936a3c965)
  • OBJECT: 19:1330103779:7

よくある形式としては、次のようなものです。

  1. データベース ID : オブジェクト ID
  2. データベース ID : ファイル ID : ページ ID
  3. データベース ID : キーハッシュ

「1.」については、SCH-S / SCH-M のようなロックが取得されている際に使用される形式ですがオブジェクト ID を OBJECT_NAME 関数 に設定することで、実際のオブジェクト名を解決することができます。

「2.」についてはページに対してのロック、これは、「ホットページ」と言われるような特定のページに対して、変更が集中している場合に確認できるケースがあります。

ページの情報については、次のようなクエリで具体的な情報を確認することができます。

DBCC PAGE には、DB_ID / ファイル ID / ページ ID を指定します。最後の 3 については、出力の形式ですので、これは固定でよいかと。

DBCC TRACEON(3604)
DBCC PAGE(19, 1, 432,3)

 

上記の DBCC コマンドはページの内容を直接参照するものとなりますが、ページの中には、どのオブジェクト ID で、どのようなデータが入っているのかを確認することができますので、この情報からホットページに格納されているデータを判断することができます。

image

SQL Server 2019 以降であれば、sys.dm_db_page_info (Transact-SQL) から情報を確認することもできます。

 

「3.」の形式はキーロックが発生している場合に確認ができる形式となります。

この形式で出力されていると「KEY: 19:72057594050641920 (a0c936a3c965)」というような情報となり、具体的にどのレコードなのかが分かりません。

この情報が出力されている場合、該当のページの情報も前後で取得できているケースがありますので、そのページ内の情報を確認して、「KeyHashValue」の値からレコードを取得することができます。

image

この キーハッシュについては、「%%lockres%%」という仮想列からも情報を取得することができますので、次のようなクエリで代替することもできます。

SELECT * FROM LockTest WHERE %%lockres%% = '(a0c936a3c965)'

 

ロックリソースの情報を取得した場合には「具体的にどのようなオブジェクト / データなのかを把握する」ということも意識しておくとよいかと。

 

 

SQL Server のロックエスカレーションの挙動

 

最後に、ロックエスカレーションの挙動についても触れておきたいと思います。

最新バージョンのドキュメントが見当たらないのですが、公式のドキュメントとしては ロックのエスカレーション (データベース エンジン) で解説されています。

SQL Server でロックのエスカレーションが原因で発生するブロッキング問題を解決する方法 の情報も参考になります。

 

SQL Server のロックエスカレーションの発生条件については、基本的には次の内容となります。

ロックのエスカレーションは、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 個獲得するごとにロックのエスカレーションを開始します。

SQL Server は、1 ロックあたり、96 バイトのメモリを消費します。

大量のロックを取得するようなクエリについては、上位の階層のリソースのロック一つにまとめた方が、リソース消費が効率的ですので、メモリまたは、ロック数によってロックエスカレーションがトリガーされ、テーブルまたはパーティションに対してのロックにエスカレートされます。

ロックエスカレーションは、テーブルまたは、パーティションのロックにエスカレーションをするものとなりますので、他のセッションで取得しているロックと、エスカレーション後のロックが競合する場合には、エスカレーションは失敗します。

エスカレーションに失敗した場合は、新たに 1,250 個のロックを取得した場合に、再度エスカレーションを実行するという動作が繰り返され、エスカレーションが必ず成功するというような動作にはなっていません。

 

ロックエスカレーションに関しての設定

テーブルのデフォルトの設定として、「SET ( LOCK_ESCALATION = { AUTO | TABLE | DISABLE } )」というオプションが提供されておりデフォルトは「TABLE」となっており、これを AUTO や DISABLE に変更することができます。

テーブル単位でロックエスカレーションの挙動を変えることもできますが、インスタンス全体の動作として トレースフラグ で挙動を変更することもできます。

image

 

インスタンス全体 / セッション単位で設定をおこなうことができますので、全体としてロックエスカレーションを禁止 / 特定のセッションで実行された場合は禁止というような使い分けができます。

 

 

ロックエスカレーションの発生状況の取得

ロックエスカレーションの発生状況については、拡張イベントの「lock_escalation」から取得することができます。

image

このイベントを取得することで、どのようなステートメントでロックエスカレーションが発生したかを確認することができます。

image

他にも DMV から取得するという方法もあります。

dm_db_index_operational_stats (Transact-sql) では、インデックス単位でロックエスカレーションの発生状況を確認することができる項目を持っています。

成否にかかわらずロックエスカレーションを試行した場合には、「index_lock_promotion_count」がカウントアップされ、他のセッションによって取得されているロックにより、ロックが競合し、エスカレーションできなかった場合は「index_lock_promotion_attempt_count」がカウントアップされます。

image

これは、SQL Server のサービスを最後に起動してからの累計値となり、拡張イベントを設定していなくても、現在、どのような傾向なのかを確認することができます。

 

 

今回はロックの基本的な動作についてのみ記載を行いましたが、他にも、

など、様々な考慮 / 情報取得方法があります。

日本語の書籍では、ロックに特化したものはないのですが、洋書になると Expert SQL Server Transactions and Locking: Concurrency Internals for SQL Server Practitioners のような書籍など詳細に解説されているものなどがあります。

今回の投稿でロックについて気になった挙動がある場合には、このような書籍や、冒頭で紹介した トランザクションのロックおよび行のバージョン管理ガイド を読み込むことで、さらに理解を深めることができますので、これらの情報も参考にして、ロックに対してのアプローチを学習していただければと思います。

Share

Written by Masayuki.Ozawa

7月 28th, 2020 at 8:12 pm

Posted in SQL Database,SQL Server

Tagged with ,

«
»

Leave a Reply