SQL Server で取得されたロックの情報を確認する方法として lock_aquired / lock_released イベントを拡張イベントで取得する方法があります。
取得されているロックを確認する方法としては sys.dm_tran_locks を参照する方法もあります。
この DMV から情報を取得した場合は、次の画像のように、resource_description から、どのロックリソース (%%lockres%%) のキーに対してロックが取得されているのかを確認することができます。
しかし、DMV の参照はトレースとして情報を時系列で取得することは難しく、ロックのような短時間で取得される内容が細かに変わっていくような特性のある情報については DMV を連続でダンプしても解析することは難しいです。
本投稿を各モチベーションとなったのは「キーに対してどのような順序でロックが取得されているか」を確認したかったのですが、このような情報については DMV で取得することは難しく、拡張イベントのようなイベント駆動のトレースの情報を取得する必要があります。
しかし拡張イベントで「取得されるロック」の情報を確認した場合 、上述の DMV のような resource_description のような可視性の高い情報が無く、次の画像のように、「resource_x」「associated_object_id」を使用して、情報を確認する必要があります。
これらの情報を使用して「どのキーに対してロックが取得されたのか?」を確認する方法を考えてみました。
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
これでオブジェクト名を解決することができます。
次に、キーのロックリソースの情報を拡張イベントから取得する必要があるのですが、これには、「resource_2」を使用することができるようでした。
最初にこの値を HEX に変換します。
SELECT FORMAT(CAST(2386995095 AS BIGINT), 'X') AS HexValue;
次にこの値の順序を入れ替えます。
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)
取得された値で、%%lockres%% の仮想列に対して LIKE 検索を行うことで拡張イベントで確認できた KEY ロックがどの行の情報なのかを確認することができました。
拡張イベントで取得された KEY ロックのログをこのような方法で確認することで、「データに対してどのような順序で KEY ロックが取得されたのか?」を考察することができます。
更新系の処理であれば、トランザクションログの Lock Information から確認することもできますが、検索系の処理ではトランザクションログから追うことができないため、拡張イベントにより取得されたトレースログを情報を確認することができることで、ロック取得の考察を深めることができるシーンがあるのではないでしょうか。