Skip to content

MS_SQLServerLockTimeout

Jump to bottom
nishi_74322014 edited this page Aug 13, 2026 · 2 revisions

SQL Server でのロック・タイムアウト

前提知識

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

ロックの種類

ロックの種類は

  • DML の種類
  • 分離レベルの指定

によって変わってくる（ただし、DDL によるロックもある）。

ロックの互換性

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

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

分離レベル

補足（SQL Server が「参照でも詰まる」理由）: Oracle / PostgreSQL は
既定が MVCC（多バージョン） で「読み取りは書き込みをブロックしない」。
一方、SQL Server の既定 READ COMMITTED は
ロック方式で分離レベルを実現している。

既定の分離レベル 参照時
SQL Server（既定） READ COMMITTED（ロック） 共有ロックを取る → 更新中の行で待つ
Oracle / PostgreSQL READ COMMITTED（MVCC） 待たない（旧バージョンを読む）
SQL Server（RCSI 有効時） READ COMMITTED SNAPSHOT 待たない（tempdb の行バージョンを読む）

つまり本ページが繰り返し扱う「ブロッキング」は
SQL Server 固有の設計に由来する問題であり、
後述の「MVCC に変更する」（RCSI / SNAPSHOT）が根本対策になりうる。

確認方法

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;

なお、ロック タイムアウトとコマンド タイムアウトは別物であり、

  • コマンド タイムアウトはデータプロバイダに設定する。
  • 値の設定方法は、データプロバイダ毎に個別となる。

補足（既定値は「無制限」）: SET LOCK_TIMEOUT の既定値は
-1（無期限に待つ） である。したがって何も設定しなければ
ロック待ちでタイムアウトすることはなく、
先にコマンド タイムアウト（既定 30 秒）が発動する。

待ち発生 ──30秒──> [ADO.NET のコマンド タイムアウト] ← 既定ではこちらが先
         ──∞───> [ロック タイムアウト]（既定 -1 なので来ない）

だからこそ次項のとおり、ロック タイムアウトを短く設定して
先に発動させるという切り分け手法が有効になる。

ロック状況の確認方法

  • ロック タイムアウトやコマンド タイムアウトが発生した場合、
    そのタイムアウトがロックによるものか、コマンドによるものかを
    切り分ける必要がある。
  • 基本的に、ロック タイムアウト値をコマンド タイムアウト値より短く
    設定しておけば、どちらであるかを切り分けることが出来る。
  • 参考: ASP.NETで考慮すべきタイムアウト値

sp_lock_detailストアドプロシージャ

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

以下はロック状況を確認する 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,
      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'
    ORDER BY spid
  END

RETURN (0) -- sp_lock

移行メモ（元ページのスクリプトの誤り／syslockinfo の非推奨）:

  • 元ページの ELSE 側は master.dbo.spt_values が
    .dbo.spt_values となっており、そのままでは実行できない
    （上記では master. を補って修正した）。
  • master.dbo.syslockinfo は非推奨であり、
    SQL Server 2005 以降は次項の sys.dm_tran_locks を使用する。

動的管理ビュー

ロック状況の監視には、

で紹介されている 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 名を入力する。

移行メモ（この WHERE 句は意図と合っていない）: resource_type は
OBJECT / KEY / PAGE などの資源の種類が入る列であり、
DB 名が入る列ではない。したがって
WHERE [resource_type] <> 'Northwind' は
「全件が条件を満たす（＝絞れていない）」だけの式になる。
DB で絞るなら次のように書く。

WHERE resource_database_id = DB_ID('Northwind')

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

補足（誰が誰を待たせているかを見るには）: sys.dm_tran_locks は
「今どんなロックがあるか」を示すが、
ブロッキングの連鎖（誰が根本原因か） を見るなら
sys.dm_exec_requests の blocking_session_id が早い。

SELECT r.session_id, r.blocking_session_id, r.wait_type, r.wait_time,
       t.text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.blocking_session_id <> 0;

blocking_session_id を辿って 0 に行き着くセッションが
ブロッキングの根本である。
後追いなら system_health 拡張イベントの
blocked_process_report（sp_configure 'blocked process threshold' が要る）も使える。

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

SQL トレースを取って問題を起こしている SQL を確認できる。
以下の様な問題に起因していることが多い。

  • スキャンにより広範囲にロックがかかる。
  • ロック・エスカレーションにより広範囲にロックがかかる。

なお、デッドロック チェーンは基本的に直ちに検出されるので、
プログラミングにおけるデッドロックのように、
ハングアップ（ロック・タイムアウト）にはならない。

よくあるパターンに、

がある。

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

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

により、広範囲にロックがかかることがある。
なお、統計情報の更新タイミングについては、
SQL Server のオプティマイザを参照にできる。

補足（暗黙の型変換は最頻出の落とし穴）: 列が varchar なのに
パラメータが nvarchar（.NET の string は既定で nvarchar）だと、
列側が変換されてインデックスが使えなくなる。

結果
WHERE varchar列 = @nvarchar引数 列を変換 → Index Scan（型の優先順位で nvarchar が上）
WHERE nvarchar列 = @nvarchar引数 Index Seek

対策は、

  • 列の型と引数の型を揃える（SqlParameter に SqlDbType.VarChar を明示）
  • 実行プランで CONVERT_IMPLICIT の有無を確認する

ADO.NETデータプロバイダでの型指定が
性能に直結する典型例である。

対策

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

MVCCに変更する

SQL Server 2005 から、動作を MVCC に変更することも可能
（DBMSのロック・分離戦略と同時実行制御）。

補足（RCSI と SNAPSHOT の違い）: SQL Server の行バージョン管理には
2 つある。

RCSI
READ_COMMITTED_SNAPSHOT ON 		SNAPSHOT 分離
ALLOW_SNAPSHOT_ISOLATION ON
適用 既定の READ COMMITTED が置き換わる（アプリ改修不要） SET TRANSACTION ISOLATION LEVEL SNAPSHOT が必要
見えるもの ステートメント開始時点 トランザクション開始時点
更新競合 通常どおり 更新競合でエラー（3960）になりうる

既存アプリのブロッキングを止めたいなら RCSI が第一選択である。
ただし副作用として、

  • tempdb の消費が増える（バージョン ストア）
  • 「読んだ値が最新とは限らない」前提になる

点は事前に確認が要る。Azure SQL Database では RCSI が既定で有効である。

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

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

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

ロック ヒント

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

sp_indexoption

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

移行メモ（最新化）: sp_indexoption は非推奨であり、
現在は ALTER INDEX ... SET (ALLOW_ROW_LOCKS = , ALLOW_PAGE_LOCKS = ) を使う。

事例

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

Oracle など、多バージョン法（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 * FROM T WITH(UPDLOCK) WHERE PK=1

トランザクション2（後発）

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

ポイント

上記で PK=1 がロック非互換でブロッキングされないのは、使用するインデックスが、

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

と、異なるためである。

移行メモ（元の SQL の誤字）: 元ページは SELECT * FORM T ... と
記しているが、FROM の誤記である（上記では修正した）。

補足（この事例が示す本質）: ロックは「行」ではなく
「アクセス経路（インデックス）上の資源」に対して取られる。
したがって、

  • 同じ行を指していても、経路が違えばぶつからない（後者の事例）
  • 逆に、別の行を狙っていても、同じ経路を広く読めばぶつかる（前者の事例）

ということが起きる。ブロッキングの調査で
「SQL の見た目」ではなく実行プランを見るべき理由がここにある。
ただし後者は「たまたま別インデックスが選ばれている」だけで、
統計情報やデータ量の変化でプランが変われば再びぶつかりうる
点には注意したい（意図的な設計として依存すべきではない）。

参考

関連

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

このWikiは「Open棟梁Project」,「OSSコンソーシアム 開発基盤部会」によって運営されています。

NetDevInfraWiki

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

（未着手）

開発基盤部会 Wiki

移行管理: DONE ／ TODO

Clone this wiki locally