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

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