SQLServerのテーブルロック状態を取得するSQL

SQLServerのデッドロックを調査している中でロックの状態を調べるSQLがあることが判明したのでメモします。

ロックの状態を取得するSQL
- SQLの実行結果
- ロックの種類

ロックの状態を取得するSQL

ロックの状態を取得するSQLは以下のようにすれば取得できるようです。

ロックの状態を調べるSQLは以下の通り SELECT resource_type as オブジェクトの種類, resource_associated_entity_id as エンティティID, (CASE when resource_ty...

SELECT
     resource_type AS type
    ,resource_associated_entity_id as entity_id
    ,( CASE WHEN resource_type = 'OBJECT' THEN
          OBJECT_NAME( resource_associated_entity_id )
      ELSE
          ( SELECT
              OBJECT_NAME( OBJECT_ID )
           FROM
             sys.partitions
           WHERE
               hobt_id=resource_associated_entity_id )
      END)
        AS object_name
    ,request_mode AS request_mode
    ,request_type AS request_type
    ,request_status AS request_status
    ,request_session_id AS session_id
FROM
    sys.dm_tran_locks
WHERE
    resource_type <> 'DATABASE'
ORDER BY
    request_session_id

SQLの実行結果

上記のSQL実行結果は

type     entity_id  object_name  mode   type  status  session_id
OBJECT   90123845   test_table   IS     LOCK  GRANT   51

ロックの種類

ロックの種類は以下のものになります。

S	共有ロック	他のトランザクションからの読込は可能。更新は不可となる。
X	排他ロック	他のトランザクションからの読込・更新が共に不可となる。 INSERT、UPDATE、DELETEを実行するとこのロックになる。
U	更新ロック	他のトランザクションからの読込は可能。更新は不可となる。 SELECTで WITH( UPDLOCK ）を指定するとこのロックになる。
IS	インテント共有	下位の階層に位置するリソースの (すべてではなく) 一部に対し、要求されているかかけられている共有ロックを保護します。
IX	インテント排他	下位の階層に位置するリソースの (すべてではなく) 一部に対し、要求されているかかけられている排他ロックを保護します。 IX は IS のスーパーセットです。また、下位のリソースに対する共有ロックの要求を保護します。
SIX	インテント排他の共有	下位の階層に位置するすべてのリソースに対し、要求されているかかけられている共有ロックを保護し、下位のリソースの (すべてではなく) 一部のインテント排他ロックを保護します。上位リソースで同時実行している IS ロックは可能です。たとえば、テーブルに対し SIX ロックをかけると、変更中のページにインテント排他ロックが、変更中の行に排他ロックがかかります。 1 つのリソースに対しては、一度に1つのSIXロックしかかけられません。その結果、他のトランザクションによってリソースが更新されることはなくなりますが、他のトランザクションはテーブルレベルのISロックをかけることで下位のリソースを読み取ることができます。
IU	インテント更新	下位の階層に位置するすべてのリソースに対し、要求されているかかけられている更新ロックを保護します。 IU ロックはページリソースに対してのみ使用します。更新操作が発生すると、IU ロックは IX ロックに変換されます。
SIU	共有インテント更新	SロックとIUロックを個別にかけるか、同時にかけるかして組み合わせたものです。たとえば、トランザクションで PAGLOCK ヒントを指定してクエリを実行してから更新操作を実行するとします。 PAGLOCK ヒントを指定したクエリでSロックをかけ、更新操作で IU ロックをかけます。
UIX	更新インテント排他	UロックとIXロックを個別にかけるか、同時にかけるかして組み合わせたものです。

SQLServerのテーブルロック状態はSQLで意外と簡単に取得することができました。

以上、SQLServerのテーブルロック状態を取得するSQLでした。