Clever Geek Handbook

適切なSQLトランザクションに向けて（パート2）





前のパートでは、トランザクション分離レベルの基本について説明しました。 ここで、もう少し掘り下げて、MS SQL Serverが分離レベルを実装しているツールについて説明します。



前のセクションでわかるように、分離を維持する方法は2つあります。



データのコピーの作成に基づくモードは理解するのに十分単純であり、特別な注意を必要としないと思います。 それらの実装の詳細を掘り下げたい場合は、 MSDNで悪くない説明に目を向けることをお勧めします 。 ロックベースのメカニズムがどのように実装されているかを検討したいと思います。





ロックの動作原理は、動作の違いの分類と説明から明らかになります。 ただし、最初の分類を導入する前に、MS SQL Serverのメモリ構成のいくつかの側面を思い出す必要があります。



MS SQLのメモリの構成



データベース内のすべてのデータはページに分割され、ページはエクステントを形成します。

ページ


ページ-8Kbを占有します。最初の96バイトはヘッダーであり、ページの説明が含まれます。

ページの種類（保存される情報の種類が異なります）：





範囲


エクステントは、8つの連続したページ（8 * 8Kb）を含むメインのスペース管理ユニット（64Kb）です。 同種（均一）エクステントと混合（混合）エクステントがあります。 同種のページには1つのタイプが含まれ、混合ページには異なるタイプが含まれます。 同じタイプの新しいページが混合エクステントの一部として表示されると、サーバーは混合エクステント間でページを移動することで均一なエクステントを作成しようとします。

サーバーでメモリがどのように構成されているかがわかったので、ロックの最初の分類、つまりブロックされるリソースのタイプを入力できます。



ブロックされたリソースの種類ごとのロックの種類



ブロックされたリソース：





エスカレーションロック



これは、サーバーの最適化を目的としたプロセスであり、多くの低レベルのロックをより高いレベルの1つ以上のロックに置き換えることができます。 たとえば、行レベルのロックを多数作成し、すべての行が1つのページに属している場合、サーバーはリソースを節約するために、それらすべてを1つのページレベルのロックに置き換えることができます。



ブロックモードによるロックの種類



共有ロック


読み取り操作（SELECT）に使用され、ロックされたリソースの変更（UPDATE、DELETE）を防ぎます。 名前が示すように、このロックは他のロック（以下で説明する互換ロックまたは更新ロック）と組み合わせることができます。 つまり、トランザクションT1がデータを読み取り、読み取り可能な行に互換性のあるロックを設定すると、別のトランザクションT2は、トランザクションT1によるロックの解放を待たずに、同じ行にロックを設定できます。

トランザクションの分離レベルに応じて、データが読み取られるとすぐにロックを解放するか（コミットの読み取り）、トランザクションの終了までロックを保持できます（繰り返し読み取り以上）。 リクエストに適切なテーブルヒント （たとえば、HOLDLOCK、SERIALIZABLEなど）を指定することにより、トランザクションが終了するまでロックを保持することもできます。

データが読み取られるときにロックが解放される場合、クエリの完了前（SELECT）でもロックを解放できます。 つまり 10行を選択し、互換性のある10の行レベルロックを設定すると、最初の行のデータが読み取られるとすぐに、残りの9行が読み取られるのを待たずにロックが解除されます。



排他ロック


データ変更操作（UPDATE、DELETE）に使用されます。 リソースに他のロックがある場合、このロックは設定できません。 チームは、既存のすべてのロックが解除されるのを待ちます。 正常にインストールされると、このロックはどのタイプの新しいロックもインストールできません。 ロックされたリソースにアクセスしようとするすべてのリクエストは、排他ロックが解除されるのを待ちます。



更新ロック


このタイプのロックを導入する必要があった理由を理解するために、データを更新するプロセスを詳しく見てみましょう。 論理的には、2つの段階に分けることができます。

  1. 更新用の検索データ
  2. 見つかったデータを更新しています。


データを正しく更新するには、排他ロックを確立する必要があることをすでに知っています。 ただし、クエリの最初（データ検索段階）から排他ロックが確立されると、他のトランザクションからデータを読み取ることさえできなくなります。 したがって、最初の段階（データ検索）では、データが見つかった場合にのみ共有ロックを確立し、それを排他的に変換して変更することをお勧めします。 これにより、トランザクションが更新するデータを探している間、他のトランザクションがデータの読み取り中に待機することを回避できます。

提案されたアプローチですべてがうまくいくようです。 最初の段階で共有ロックを作成し、2番目の段階でそれを独占に変換します。 パフォーマンスが向上し、誰もが満足しています。 しかし、悲しいかな、キャッチがあります。 上記のアルゴリズムに従って、2つの異なるトランザクションが同じデータを同時に更新しようとすると、デッドロックの受信が保証されます。 以下に、その発生方法を示します。



したがって、新しいロックモードが必要でした-ロックを更新します。 共有（共有）ロックと排他（排他）ロックの間の何かのように動作します。 「モノポリー」とは、リソースに1つの更新ロックしか存在できないことを意味し、「互換性」とは、データ検索段階で、そのロックを他の互換性のあるロックと組み合わせることができることです。



注 ：更新ロックは、UPDATE操作だけでなく、削除されたデータを検索する段階でデータを削除（DELETE）するためにも使用されます。 クラスターインデックスINSERTを使用してテーブルに新しい行を挿入する場合、このタイプのロックは、インデックス内の正しい位置を見つける段階でも適用できます。 位置が見つかると、更新ロックが排他的インデックスロックに更新され、新しい行が挿入されます。



意図的ロック

このタイプのロックは特別なモードではありません。 上記のロック設定アルゴリズムの動作を最適化するのに役立ちます。

それは単純な考えに基づいています。 低レベルのロック（行またはページレベル）を設定する前に、常に最初に上位レベル-テーブルレベルでインテントロック（Intent）を設定します。 そのようなロックがない場合は、関心のあるリソース（行、ページ）の既存のロックのチェックを回避し、すぐにインストールできます。 ある場合、特定の低レベルロックをインストールする可能性をより最適に決定することができます。 たとえば、意図的な互換性のあるロックがある場合、テーブルレベルで排他ロックを取得しようとすることは意味がありません。

低レベルロックのタイプに応じて、次のタイプのロックを意図的に区別できます。





スキーマロック


これらのロックは、データベース構造（テーブル、ビュー、インデックスなど）の変更を防ぐために使用され、2つのタイプに分けられます。





一括更新ロック


同じテーブルへのバルクデータロードの複数の同時ストリームをサポートし、同時にバルクデータロード以外の他のプロセスへのテーブルへのアクセスを禁止できます。

使用時にインストールされます：



次の場合：





キー範囲ロック（範囲）


このロックにより、選択した行の範囲をブロックすることにより、ファントムの問題を防ぐことができます。 つまり 選択された行セットについては、（クエリ条件に一致する）新しい行がないこと、および選択された行セットから行が削除されることが保証されます。 このロックに基づいて、SERIALIZABLE分離レベルが実装されます。 詳細はこちら 。



ロックの互換性



前述のように、一部のタイプのロックは同じリソースに正常にインストールできます。 それどころか、すべてのロックが完了することを期待する人もいます。 以下に完全な互換性マトリックスを示します。これは、既存のロックがある場合に特定のタイプのロックを設定できるかどうかを示しています。

たとえば、リソースに排他ロック（Exclusive）が設定されている場合に共有ロック（Shared）を設定できるかどうかに関心があります。 これを行うには、要求されたロックに対応する行を見つけ（青で強調表示）、対応する列で値を見つけます（赤で強調表示）。 この例では、競合を示す値「K」が表示されます。 共有ロック（S）は、排他ロック（X）がリソースから削除されるまで強制的に待機します。





おわりに



ロックメカニズムの実装から秘密のカーテンを取り除くことができたので、さまざまなシナリオでどの分離レベルを使用すべきかを明確に理解できたと思います。 結局のところ、実装を理解するだけで、さまざまなレベルの分離のパフォーマンスに関連するすべてのニュアンスを比較することができ、その結果、リソースを最も効率的に使用するシステムを開発できます。


More articles:


All Articles