マイクロソフト系技術情報 Wiki」は、「Open棟梁Project」,「OSSコンソーシアム .NET開発基盤部会」によって運営されています。

目次

前提知識

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';

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

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

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

よくあるパターンに、

がある。

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

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

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

を参照にできる。

こんな単純なSQLでもブロッキングになる。

Oracleなど、多バージョン法(MultiVersion Concurrency Control:MVCC)のDBMSに慣れると、
驚くことになるが、以下の非常に簡単な2つのトランザクションで、ブロッキングが発生する。

トランザクション1(先発)

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION;

INSERT INTO [Northwind].[dbo].[Orders]
 ([OrderID], ・・・) VALUES (11078, ・・・);

  若しくは

DELETE FROM [Northwind].[dbo].[Orders] WHERE [OrderID] = 11078;

--COMMIT TRANSACTION
--ROLLBACK TRANSACTION

トランザクション2(後発)

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION;

SELECT * FROM [Northwind].[dbo].[Orders]
 WHERE [OrderID] BETWEEN 11070 AND 11080

--COMMIT TRANSACTION
--ROLLBACK TRANSACTION

ポイント

ポイントは、後発の参照処理が、インデックスをキー範囲ロックしようとしている点。

特に、メモリの少ないクライアントOS上のSQL Server Express Edition等で、

クエリ・プランがインデックス・スキャンを選択した場合
主キーでのSELECTが、主キーのキー範囲ロックになってしまい、
他の排他ロック(INSERTやDELETEを含む)にブロッキングされる。」

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

対策

適切な分離レベルを選択する。

MVCCに変更する。

SQL Server2005から、動作をMVCCに変更することも可能。

エスカレーションを抑止する。

SQL Server のロックのエスカレーションの発生を抑止できる。

ロックをカスタマイズする。

アクセス パターンが一定していることがわかっている場合、
ページまたは行のロックを禁止することが効果的なケースもある。

参考情報


Tags: :データアクセス, :SQL Server, :障害対応, :性能, :デバッグ


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