トランザクション分離レベル：Serializable について説明してみる - 都内で働くSEの技術的なひとりごと / Technical soliloquy of System Engineer working in Tokyo

　先々週春一番が吹きましたが、まだまだ寒い日が続きますね。今日は打ち合わせwwと移動の時間を使って記事書きます。(移動中に記事書くと、乱文、乱筆になりがち。)
f:id:koogucc11:20160223110731p:plain

　トランザクション分離レベルについて、きちんと理解されていないことが多いので、今回はプロジェクト内で誤って使用されてしまった『Serializable』についての説明をします。Serializable とは、
SET TRANSACTION ISOLATION LEVEL (Transact-SQL)

•他のトランザクションで変更されたが、まだコミットされていないデータは、ステートメントで読み取れない。
•現在のトランザクションが完了するまで、現在のトランザクションで読み取ったデータは他のトランザクションで変更できない。
•現在のトランザクションが完了するまで、現在のトランザクションのステートメントで読み取ったキー範囲に該当するキー値の行は、他のトランザクションで新しく挿入できない。
トランザクションで実行される各ステートメントの検索条件に一致するキー値の範囲には、範囲ロックが設定されます。これにより、現在のトランザクションで実行されるステートメントの処理対象となる行はブロックされ、他のトランザクションによる行の更新や挿入ができなくなります。つまり、トランザクションのステートメントが 2 度実行された場合は、2 度目も同じ行セットが読み取られます。範囲ロックはトランザクションが完了するまで保持されます。このオプションではキー範囲全体がロックされ、トランザクションが完了するまでその状態が保持されるので、これは最も制限の厳しい分離レベルといえます。このオプションは同時実行性が低いため、必要なときにのみ使用してください。このオプションは、トランザクション内のすべての SELECT ステートメントで、すべてのテーブルに対して HOLDLOCK を設定するのと同じ効果があります。

です。

　動作を確認してみましょう。下記のクエリをSQL Server Management Studio で実行してみましょう。

-----①
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
SELECT 
    * 
FROM 
    HumanResources.EmployeePayHistory
-----①

-----②
UPDATE 
    HumanResources.EmployeePayHistory SET Rate = 8.0 
WHERE 
    BusinessEntityID = 4
-----②

-----③
COMMIT TRANSACTION;
-----③

ロック状態を確認するには、下記のクエリを実行します。(セッションIDは環境に応じて変更してください。)

-----④
SELECT 
    * 
FROM 
    sys.dm_tran_locks
WHERE
    request_session_id = 54
-----④

　下図のような感じで準備しましょう。
f:id:koogucc11:20160223103618p:plain

　まず、①を実行します。
f:id:koogucc11:20160223103750p:plain

　④を実行しましょう。SELECT した行が RangeS-S でロックされていることが確認できます。
f:id:koogucc11:20160223103915p:plain

　②を実行します。
f:id:koogucc11:20160223104232p:plain

　④を実行しましょう。UPDATE した行が RangeX-X でロックされていることが確認できます。
f:id:koogucc11:20160223104357p:plain

　③を実行しましょう。
f:id:koogucc11:20160223104527p:plain

　④を実行しましょう。ロックが解放されていることが確認できます。
f:id:koogucc11:20160223104620p:plain

　上記の動作は、下記のように HOLDLOCK にした場合も同様です。

-----①
BEGIN TRANSACTION;
SELECT 
    * 
FROM 
    HumanResources.EmployeePayHistory WITH(HOLDLOCK)
-----①

-----②
UPDATE 
    HumanResources.EmployeePayHistory SET Rate = 8.0 
WHERE 
    BusinessEntityID = 4
-----②

-----③
COMMIT TRANSACTION;
-----③

　トランザクション実行中に別のセッションから同様の分離レベル、クエリを実行すると COMMIT されるまで結果は帰ってきません。詳しくは下記の通りです。
キー範囲ロック

キー範囲ロックは、SERIALIZABLE トランザクション分離レベルを使用中に、Transact-SQL ステートメントで読み取っているレコードセットに含まれている行の範囲を暗黙的に保護します。SERIALIZABLE 分離レベルでは、トランザクション中に実行されるクエリは、そのトランザクション内で実行されるたびに同一の行セットを取得する必要があります。キー範囲ロックではこの要件を満たすために、新しい行のキーが SERIALIZABLE トランザクションで読み取られるキー範囲内にある場合に、他のトランザクションが新しい行を挿入できないようにします。
キー範囲ロックを使用すると、ファントム読み取りを回避できます。各行のキー範囲を保護することで、トランザクションからアクセスされるレコードセットへのファントム挿入も回避されます。
キー範囲ロックは、キー範囲の開始値と終了値を指定して、インデックスに対して設定されます。このロックでは、範囲内のキー値を持つ行を挿入、更新、または削除する操作がブロックされます。挿入操作、更新操作、または削除操作では、最初にインデックスに対するロックを取得する必要があるためです。たとえば、シリアル化可能なトランザクションでは、'AAA' と 'CZZ' の間のキー値を持つすべての行を読み取る SELECT ステートメントを実行できます。'AAA' から 'CZZ' の範囲内のキー値にキー範囲ロックをかけると、他のトランザクションからは 'ADG'、'BBD'、'CAL' など、その範囲内のキー値を持つ行は挿入されません。