都内で働くSEの技術的なひとりごと / Technical soliloquy of System Engineer working in Tokyo

都内でサラリーマンやってます。SQL Server を中心とした (2023年からは Azure も。) マイクロソフト系(たまに、OSS系などマイクロソフト以外の技術も...)の技術的なことについて書いています。日々の仕事の中で、気になったことを技術要素関係なく気まぐれに選んでいるので記事内容は開発言語、インフラ等ばらばらです。なお、当ブログで発信、発言は私個人のものであり、所属する組織、企業、団体等とは何のかかわりもございません。ブログの内容もきちんと検証して使用してください。英語の勉強のため、英語の

トランザクション分離レベル:Serializable について説明してみる

 先々週春一番が吹きましたが、まだまだ寒い日が続きますね。今日は打ち合わせ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' など、その範囲内のキー値を持つ行は挿入されません。

 多重でクエリが発行されるような状況下では、トランザクション分離レベル:Serializable は使用すべきではないです。設計からどのような要件があるかしっかり見極めてトランザクション設計を行いましょう。

※ただ今、絶賛勉強中!

VMware徹底入門  第4版 VMware vSphere 6.0対応

VMware徹底入門 第4版 VMware vSphere 6.0対応

できるPRO VMware vSphere 6 (できるPROシリーズ)

できるPRO VMware vSphere 6 (できるPROシリーズ)