「マイクロソフト系技術情報 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ストアドプロシージャ †

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

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に慣れると、
驚くことになるが、以下の非常に簡単な２つのトランザクションで、ブロッキングが発生する。

↑

トランザクション１（先発） †

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
↑

トランザクション２（後発） †

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 のロックのエスカレーションの発生を抑止できる。

↑

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

↑

ロック ヒント †

テーブル ヒントにロック手法をWITH (ROWLOCK)などと指定する。

↑

sp_indexoption †

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

↑

参考情報 †

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

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