SE の雑記

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

lock_aquired / lock_released の拡張イベントログで KEY ロックの対象を確認してみる

leave a comment

SQL Server で取得されたロックの情報を確認する方法として lock_aquired / lock_released イベントを拡張イベントで取得する方法があります。

取得されているロックを確認する方法としては sys.dm_tran_locks を参照する方法もあります。
この DMV から情報を取得した場合は、次の画像のように、resource_description から、どのロックリソース (%%lockres%%) のキーに対してロックが取得されているのかを確認することができます。

image

しかし、DMV の参照はトレースとして情報を時系列で取得することは難しく、ロックのような短時間で取得される内容が細かに変わっていくような特性のある情報については DMV を連続でダンプしても解析することは難しいです。

本投稿を各モチベーションとなったのは「キーに対してどのような順序でロックが取得されているか」を確認したかったのですが、このような情報については DMV で取得することは難しく、拡張イベントのようなイベント駆動のトレースの情報を取得する必要があります。

しかし拡張イベントで「取得されるロック」の情報を確認した場合 、上述の DMV のような resource_description のような可視性の高い情報が無く、次の画像のように、「resource_x」「associated_object_id」を使用して、情報を確認する必要があります。

image

これらの情報を使用して「どのキーに対してロックが取得されたのか?」を確認する方法を考えてみました。

lock_aquired / lock_released イベントを使用した取得された KEY ロックの対象の確認

最初に「どのオブジェクトでロックが取得されたのか」を確認する必要があります。

上述の画像の情報が拡張イベントで取得された情報となるのですが、取得できる情報にはオブジェクト名 / オブジェクト ID は含まれていません。

拡張イベントのログからオブジェクト名を解決するためには「associated_object_id」を使用する必要があります。この associated_object_id は hobt_id となるようですので、sys.partitions でオブジェクト名を解決することができます。

select 
object_name(object_id) as object_name ,
index_id, partition_number, partition_id, hobt_id
from sys.partitions 
where hobt_id = 72057594050183168

 

これでオブジェクト名を解決することができます。

image

次に、キーのロックリソースの情報を拡張イベントから取得する必要があるのですが、これには、「resource_2」を使用することができるようでした。

最初にこの値を HEX に変換します。

SELECT FORMAT(CAST(2386995095 AS BIGINT), 'X') AS HexValue;

 

image

次にこの値の順序を入れ替えます。

DECLARE @hexvalue varchar(8) = (SELECT FORMAT(CAST(2386995095 AS BIGINT), 'X') AS HexValue)
SELECT 
	 SUBSTRING(@hexvalue,7,2) + SUBSTRING(@hexvalue,5,2) + 
	 SUBSTRING(@hexvalue,3,2) + SUBSTRING(@hexvalue,1,2)

 

image

取得された値で、%%lockres%% の仮想列に対して LIKE 検索を行うことで拡張イベントで確認できた KEY ロックがどの行の情報なのかを確認することができました。

image

拡張イベントで取得された KEY ロックのログをこのような方法で確認することで、「データに対してどのような順序で KEY ロックが取得されたのか?」を考察することができます。

更新系の処理であれば、トランザクションログの Lock Information から確認することもできますが、検索系の処理ではトランザクションログから追うことができないため、拡張イベントにより取得されたトレースログを情報を確認することができることで、ロック取得の考察を深めることができるシーンがあるのではないでしょうか。

Share

Written by Masayuki.Ozawa

1月 11th, 2025 at 7:57 pm

Leave a Reply