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

SQL Serverでデッドロックの調査で苦労したので調査に使ったSQLなどをまとめておく。

SQL Serverにおいては、ロックの有無を確認するだけならsys.dm_tran_locksシステムビューですぐに確認できる。

SELECT * FROM sys.dm_tran_locks

しかし、sys.dm_tran_locksだけでは、テーブルロックや行ロックなどの情報がまとめて提供され、ロックされている対象も

オブジェクトIDでの表記になるため非常にわかりにくいものになっています。

ですので、単純にSELECTするのではなく、IDからオブジェクト名を取得したり、オブジェクトの種類によって

別のシステムビューから情報を取得する必要があります。

手を加えたSQLが以下のようになります。

SELECT
     resource_type AS type --オブジェクトの種類
    ,resource_associated_entity_id as entity_id  --エンティティ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 --ロックの種類
    ,request_type --要求の種類
    ,request_status  --状態
    ,request_session_id AS Session_id --セッションID
        ,(SELECT hostname 
          FROM sys.sysprocesses 
          WHERE spid = request_session_id) AS ProcessName
FROM
    sys.dm_tran_locks
WHERE
    resource_type <> 'DATABASE'
ORDER BY
    request_session_id

実行結果は以下のようになります。

他に必要な項目がある場合、SELECT文へ項目の追加を行ってください。

主なロックステータスの種類

主なロックステータスの種類として以下のものがあります。

S	共有ロック	他のトランザクションからの読込は可能。更新は不可となる。
X	排他ロック	他のトランザクションからの読込・更新が共に不可となる。 INSERT、UPDATE、DELETEを実行するとこのロックになる。
U	更新ロック	他のトランザクションからの読込は可能。更新は不可となる。 SELECTで WITH( UPDLOCK ）を指定するとこのロックになる。

使用したシステムビューについて

・sys.dm_tran_locks・・・ロック情報を参照できるビュー。　　

　　　　　　　　　　　ビュー詳細情報<Micrsoft MSDN>

・sys.partitions・・・テーブルまたはインデックスで使用されているパーティション情報を参照できるビュー。　　

　　　　　　　　　ビュー詳細情報<Micrsoft MSDN>

・sys.sysprocesses・・・接続されているProcess情報を参照できるビュー。　　

　　　　　　　　　　　ビュー詳細情報<Micrsoft MSDN>

ロックエラーをわざと発生させる方法

異常系のテストを行いたい場合などで、テーブルのロックをかけたい場合の手順。

・以下のロック用SQLを実行（sleepしている間ロックされている）

 BEGIN TRAN
    SELECT * FROM TABLEA WITH(TABLOCKX)
    WAITFOR DELAY '00:01:00'  --任意の時間スリープ。
  COMMIT TRAN

※SSMS(SQL Server Management Studio)などでデバッグ実行できるなら、

　COMMIT TRANまでにブレークしておけばよい。

・エラーを起こすSQL

  SET LOCK_TIMEOUT 0
  SELECT * FROM TABLEA

⇒ロックタイムアウトの値を０にしているため、即時ロックエラーになる。