SQL Server ロック機構の内部構造を徹底解説：ロックエスカレーション、Key-Range Lock、NOLOCK まで

はじめに

この記事では、SQL Serverのロック機構の内部実装を深掘りします。

シリーズ4本目です。SQL ServerはロックエスカレーションとKey-Range Lockが特徴的です。また、NOLOCKヒントという他のDBにはない（そして危険な）機能もあります。SQL Server 2019 / 2022を中心に解説します。

ロックの粒度

SQL Serverは最も細かいロック粒度の階層を持ちます：

Database → Table → Partition → Page → Row / Key

InnoDBやPostgreSQLにはPage粒度のロックがありません。

ロックエスカレーション

SQL Server最大の特徴です。行ロックが多くなると、自動的にテーブルロックにエスカレーションします。

エスカレーションの閾値

• 1つのテーブルに対して約5,000行のロックを超えたとき
• ロックに使用するメモリがシステムの閾値を超えたとき

行ロック 5,000個 → テーブルロック1個にエスカレーション
  メリット: メモリ節約
  デメリット: 他のトランザクションが全ブロック

エスカレーションの制御

-- テーブル単位でエスカレーションを無効化
ALTER TABLE employees SET (LOCK_ESCALATION = DISABLE);

-- パーティション単位にエスカレーション（テーブル全体ではなく）
ALTER TABLE employees SET (LOCK_ESCALATION = AUTO);

各DBとの比較

Key-Range Lock

SQL ServerのSERIALIZABLE分離レベルでは、InnoDBのNext-Key Lockに相当するKey-Range Lockが使われます。

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT * FROM employees WHERE id BETWEEN 3 AND 7;
-- → id=3〜7の範囲にKey-Range Lockがかかる
-- → この範囲へのINSERTがブロックされる

InnoDBはREPEATABLE READでNext-Key Lockを使いますが、SQL ServerはSERIALIZABLEでのみKey-Range Lockを使います。

NOLOCK（WITH (NOLOCK)）

SQL Server固有の悪名高い機能です。ロックを一切取らずに読み取ります。

-- ダーティリード（未コミットデータを読む）
SELECT * FROM employees WITH (NOLOCK) WHERE department = 'Sales';

NOLOCKの問題： – ダーティリード：未コミットのデータが見える – 非反復読み取り：同じクエリで異なる結果 – ファントムリード：存在しない行が見える – ページ分割中のデータ読み取り：同じ行が2回見えたり、行が消えたりする

NOLOCKの代わりにRCSIを使うべき:
  ALTER DATABASE MyDB SET READ_COMMITTED_SNAPSHOT ON;
  -- → ロックなしで一貫した読み取りが可能

他のDBにはNOLOCK相当の機能がありません（READ UNCOMMITTEDはあるが、ヒントで個別テーブルに指定する仕組みはない）。

ロックヒント

SQL Serverはクエリレベルで細かくロック動作を制御できます：

-- 行ロックを強制
SELECT * FROM employees WITH (ROWLOCK) WHERE id = 1;

-- ページロックを強制
SELECT * FROM employees WITH (PAGLOCK) WHERE department = 'Sales';

-- テーブルロックを強制
SELECT * FROM employees WITH (TABLOCK);

-- 排他ロック
SELECT * FROM employees WITH (XLOCK) WHERE id = 1;

-- 更新ロック（デッドロック防止用）
SELECT * FROM employees WITH (UPDLOCK) WHERE id = 1;

この柔軟性は他のDBにはない特徴ですが、誤用するとパフォーマンス問題やデッドロックの原因になります。

デッドロック検出

SQL Serverもデッドロックを自動検出します。コストの低いトランザクションをvictimとしてロールバックします。

-- デッドロックの優先度を設定（victimになりやすさ）
SET DEADLOCK_PRIORITY LOW;    -- victimになりやすい
SET DEADLOCK_PRIORITY HIGH;   -- victimになりにくい
SET DEADLOCK_PRIORITY 5;      -- -10〜10の数値指定

まとめ

次回はDb2 ロック機構を解説し、シリーズ全体の比較表でまとめます。

参考文献

• SQL Server Documentation: Locking in the Database Engine
• SQL Server Documentation: Lock Escalation
• SQL Server Documentation: Table Hints
• SQL Server Documentation: Key-Range Locking