Open棟梁Project - マイクロソフト系技術情報 Wiki

目次

前提知識

SQL Serverは参照処理にもロック(共有ロック)を
かけるということを知っておくことが重要です。

ロックの種類は

によって変わってきます。(ただし、DDLによるロックもある)

各ロックの互換性についても考慮が必要です。

また、指定された分離レベルを実現するため、
トランザクション中のロックがホールドされるか?
・・・などで、ロックの動きが変わってきます。

確認方法

こちら(SQL Server 問題の分析方法)も参考にして下さい。

ロックをかける

以下のSQLで、

ロックをホールドしておきます。

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
SELECT * FROM Shippers WHERE ShipperID = 1
--COMMIT

ロック タイムアウト値を設定

SQL Serverでロック タイムアウト値を設定する方法は以下のとおりである。
※ この設定は、接続確立毎に実行する必要がある。

SET LOCK_TIMEOUT timeout_period

現在のロック タイムアウト値を調べるには、次に示す@@LOCK_TIMEOUT関数を実行する。

SELECT @@lock_timeout;

ロック状況の確認方法

ロック タイムアウトやコマンド タイムアウトが発生した場合、そのタイムアウトがロックによるものか、コマンドによるものかを切り分ける必要がある。基本的に、ロック タイムアウト値をコマンド タイムアウト値より短く設定しておけば、ロック タイムアウトであるか、コマンド タイムアウトであるかを切り分けることが出来る。

sp_lock_detailストアドプロシージャ

ロック タイムアウトであった場合は、以下のHPにある方法でロック状況を確認すると良い。

sp_lock_detail
CREATE PROCEDURE sp_lock_detail
  @spid1 int = NULL, /* server process id to check for locks */
  @spid2 int = NULL  /* other process id to check for locks */
AS

SET NOCOUNT ON

/* Show the locks for both parameters. */
IF @spid1 IS NOT NULL

  BEGIN

    SELECT CONVERT (SMALLINT, req_spid) AS spid,
      db_name(rsc_dbid) AS dbid,
      object_name(rsc_objid) AS ObjId,
      rsc_indid AS IndId,
      SUBSTRING (v.name, 1, 4) AS Type,
      SUBSTRING (rsc_text, 1, 16) AS Resource,
      SUBSTRING (u.name, 1, 8) AS Mode,
      SUBSTRING (x.name, 1, 5) AS Status

    FROM master.dbo.syslockinfo,
      master.dbo.spt_values v,
      master.dbo.spt_values x,
      master.dbo.spt_values u

    WHERE master.dbo.syslockinfo.rsc_type = v.number
      and v.type = 'LR'
      and master.dbo.syslockinfo.req_status = x.number
      and x.type = 'LS'
      and master.dbo.syslockinfo.req_mode + 1 = u.number
      and u.type = 'L'
      and req_spid in (@spid1, @spid2)
  END
/* No parameters, so show all the locks. */
ELSE

  BEGIN

    SELECT CONVERT (SMALLINT, req_spid) AS spid,
      db_name(rsc_dbid) AS dbid,
      object_name(rsc_objid) AS ObjId,
      rsc_indid AS IndId,
      SUBSTRING (v.name, 1, 4) AS Type,
      SUBSTRING (rsc_text, 1, 16) AS Resource,
      SUBSTRING (u.name, 1, 8) AS Mode,
      SUBSTRING (x.name, 1, 5) AS Status

    FROM master.dbo.syslockinfo,
      .dbo.spt_values v,
      .dbo.spt_values x,
      .dbo.spt_values u
    
    WHERE master.dbo.syslockinfo.rsc_type = v.number
      and v.type = 'LR'
      and master.dbo.syslockinfo.req_status = x.number
      and x.type = 'LS'
      and master.dbo.syslockinfo.req_mode + 1 = u.number
      and u.type = 'L'
    ORDER BY spid

  END

RETURN (0) -- sp_lock

動的管理ビュー

ロック状況の監視には、

で紹介されているSQLを使用できそうです。
(sys.dm_tran_locks 動的管理ビューを使用する)

SELECT
 resource_type as オブジェクトの種類,
 resource_associated_entity_id as エンティティID,
 request_mode as ロックの種類,
 request_type as 要求の種類,
 request_status as 状態
From sys.dm_tran_locks
WHERE [resource_type]<>'Northwind';

※ Northwindの所にはDB名を入力する。

SQL Server 2005 以降では、代わりに sys.dm_tran_locks 動的管理ビューを使用

CASEを使った拡張

上記の「エンティティID」をCASEを使って自己解決するバージョンです。

SELECT
  resource_type as オブジェクトの種類,
  (CASE 
    WHEN resource_type = N'ALLOCATION_UNIT' THEN 
      (SELECT 
        OBJECT_NAME(sp.object_id) + N':' +  si.name
        FROM 
          sys.allocation_units 
          LEFT JOIN 
            sys.partitions sp 
            ON 
              container_id = hobt_id 
          LEFT JOIN 
            sys.indexes si 
            ON 
              sp.object_id = si.object_id 
              AND sp.index_id = si.index_id
        WHERE 
          allocation_unit_id = resource_associated_entity_id)  
    WHEN  resource_type = N'OBJECT' THEN 
      OBJECT_NAME(CONVERT(bigint, resource_associated_entity_id))
    WHEN resource_type IN(N'HOBT', N'KEY', N'PAGE') THEN 
      (SELECT 
        OBJECT_NAME(sp.object_id) + N':' +  si.name
      FROM 
        sys.partitions sp 
        LEFT JOIN 
          sys.indexes si 
          ON 
            sp.object_id = si.object_id 
            AND sp.index_id = si.index_id 
      WHERE 
        hobt_id = resource_associated_entity_id) 
    ELSE    
      CONVERT(nvarchar(40), resource_associated_entity_id) 
  END) as エンティティID,
  request_mode as ロックの種類,
  request_type as 要求の種類,
  request_status as 状態
 From sys.dm_tran_locks
 WHERE [resource_type]<>'Northwind';

非クラスター化インデックス再構築時の同時実行性について « SE の雑記
http://engineermemo.wordpress.com/2011/06/18/%E9%9D%9E%E3%82%AF%E3%83%A9%E3%82%B9%E3%82%BF%E3%83%BC%E5%8C%96%E3%82%A4%E3%83%B3%E3%83%87%E3%83%83%E3%82%AF%E3%82%B9%E5%86%8D%E6%A7%8B%E7%AF%89%E6%99%82%E3%81%AE%E5%90%8C%E6%99%82%E5%AE%9F%E8%A1%8C/

SQLトレースを使用して確認

SQLトレースを取って問題を起こしているSQLを確認できます。

以下の様な問題に起因していることが多いです。

よくあるパターンに、

があります。

また、統計情報が更新されていないと、
インデックス・シークが適用されず、

により、広範囲にロックがかかることがあります。

なお、統計情報の更新タイミングについては、

を参照して下さい。

案外知られていない

以下の2つのトランザクションで、ブロッキングされます。

ポイントは、参照処理が、インデックスをキー範囲ロックしている点です。

こちらが後発なので、共有ロックをホールドしなくてもブロックされる。

このため、

というケースもありました(本番環境では再現せずでした)。

参考情報


トップ   新規 一覧 単語検索 最終更新   ヘルプ   最終更新のRSS