マイクロソフト系技術情報 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ストアドプロシージャの作成スクリプトをコピペ利用可能にしたもの。
  • このsp_lock_detailストアドプロシージャは、次の1ステートメントで実行可能である。
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を確認できる。

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

  • スキャンにより広範囲にロックがかかる。
  • ロック・エスカレーションにより広範囲にロックがかかる。
  • なお、デッドロック チェーンは基本的に直ちに検出されるので、
    プログラミングにおけるデッドロックのように、
    ハングアップ(ロック・タイムアウト)にはならない。

よくあるパターンに、

  • 暗黙の型変換によるインデックス・スキャンによる、
    インデックス・ロック(広範囲にロックがかかる)

がある。

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

  • インデックス・スキャン
  • テーブル・スキャン

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

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

を参照にできる。

対策

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

MVCCに変更する。

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

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

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

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

ロック ヒント

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

sp_indexoption

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

事例

こんな単純な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を含む)にブロッキングされる。」

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

逆に、これでブロッキングしない。

使用するインデックスが異り、ブロッキングしなくなる。

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

SELECT * FORM T WITH(UPDLOCK) WHERE PK=1

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

SELECT * FORM T WITH(UPDLOCK) WHERE PK=1 AND XXX=YYY

ポイント

上記で、PK=1がロック非互換でブロッキングされないのは、

使用するインデックスが、

  • 主キーの「クラスタ化インデックス」
  • 複合インデックスの「非クラスタ化インデックス」

と、使用するインデックスが異り、ブロッキングしなくなる。

参考


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


トップ   編集 凍結 差分 バックアップ 添付 複製 名前変更 リロード   新規 一覧 単語検索 最終更新   ヘルプ   最終更新のRSS
Last-modified: 2018-10-22 (月) 15:35:36 (412d)