ロックは SQL Server トランザクション処理を正常に行うために不可欠で、SQL Server がマルチユーザー環境でシームレスに動作するように設計されています。 ロックは、SQL Server がトランザクションの並行性を管理する方法です。 基本的にロックは、所有者、タイプ、および保護すべきリソースのハッシュを持つメモリ内の構造体です。 SQL Server のロックについてよりよく理解するために、ロックはデータベース内のデータの整合性を保証するために設計されており、すべての SQL Server トランザクションが ACID テストに合格するように強制されることを理解することが重要です。
ACID テストは 4 つの要件で構成されており、すべてのトランザクションが正常にパスする必要があります。
- 原子性 – 2 つ以上の個別の情報部分を含むトランザクションは、すべての部分をコミットするか、何もしないかを決定する必要がある
- 整合性 – トランザクションが新しいデータの有効な状態を作成するか、すべてのデータをトランザクションが実行する前の状態にロールバックしなければならない
- 分離 – まだ実行中でデータをすべてコミットしていないトランザクションを決定する必要がある
- 分離 – まだ実行していない、または、まだデータをコミットしない。
- Durability – コミットされたデータは、障害が発生した場合でも、すべてのデータを正しい状態で保存し、ユーザーが利用できる方法で保存されなければならない
SQL Server ロック は分離要件に不可欠な部分で、トランザクションによって影響を受けるオブジェクトをロックする役割を担います。 オブジェクトがロックされている間、SQL Server は他のトランザクションがロックされたオブジェクトに格納されているデータを変更することを防ぎます。 変更をコミットするか、変更を初期状態にロールバックすることでロックが解除されると、他のトランザクションは必要なデータの変更を行うことができるようになります。
SQL Server 言語に翻訳すると、これは、あるトランザクションがオブジェクトにロックをかけると、そのオブジェクトへのアクセスを必要とする他のすべてのトランザクションは、ロックが解放されるまで待機させられ、その待機は適切な待機タイプで登録されることを意味します
SQL Server ロックでは、ロック モードとロック粒度
Lock modes
Lock mode ではロックしなければならないリソースに適用できる各種のロック タイプが考慮される。
- Exclusive (X)
- Shared (S)
- Update (U)
- Intent (I)
- Schema (Sch)
- Bulk update (BU)
Exclusive Lock (X) – このロック タイプ。 が課されると、そのトランザクションがロックを保持している限り、ページまたは行が排他的ロックを課したトランザクションのために排他的に予約されることが保証されます。
DMLステートメントDELETE、INSERT、UPDATEの場合、トランザクションがページまたは行のデータを変更したいときに、排他ロックが課されます。 排他的ロックは、ターゲットにすでに他の共有ロックまたは排他的ロックが課せられていない場合にのみ、ページまたは行に課されることができます。 これは実質的に、1 つのページまたは行に課すことができる排他的ロックは 1 つだけで、一度課されたロックされたリソースに他のロックを課すことはできないことを意味します
Shared Lock (S) – このロック タイプを課すと、ページまたは行を読み取り専用に予約し、ロックがアクティブである限り、他のトランザクションがロックしたレコードを変更できないようにすることを意味します。 しかし、共有ロックは同じページや行に対して同時に複数のトランザクションがかけることができ、そうすれば、読み取り処理自体は実際のページや行のデータに影響を与えないため、複数のトランザクションがデータの読み取り能力を共有することができます。 さらに、共有ロックは書き込み操作を許可するが、DDL の変更は許可しない
Update ロック (U) – このロックは排他ロックに似ているが、ある意味でより柔軟に設計されたものである。 更新ロックは、すでに共有ロックを持っているレコードに課されることがあります。 この場合、アップデート・ロックはターゲット行に別の共有ロックをかけます。 更新ロックを保持するトランザクションがデータを変更する準備が整うと、更新ロック（U）は排他ロック（X）に変換されます。 アップデート・ロックは共有ロックとは非対称であることを理解することが重要です。 更新ロックは共有ロックを持つレコードに課すことができますが、共有ロックはすでに更新ロックを持つレコードに課すことはできません
インテント ロック（I） – このロックは、トランザクションが他のトランザクションにロックを取得する意図を知らせるために使用する手段である。 このようなロックの目的は、他のトランザクションが次の階層のオブジェクトに対するロックを取得するのを防ぐことによって、データの変更が適切に実行されるようにすることである。 実際には、あるトランザクションが行のロックを取得したい場合、上位階層のオブジェクトであるテーブルのインテント・ロックを取得することになる。 インテント・ロックを取得することで、トランザクションは他のトランザクションがそのテーブルの排他ロックを取得することを許しません（そうしないと、他のトランザクションが課した排他ロックによって行のロックが解除されてしまうからです）。
これはパフォーマンス面で重要なロックタイプです。SQL Server データベースエンジンはテーブルレベルのみでインテント ロックを検査し、そのテーブルでトランザクションが安全な方法でロックを取得できるかどうかをチェックするため、インテント ロックによって、テーブル内の各行/ページ ロックを検査して、トランザクションがテーブル全体に対してロックを取得できることを確認する必要がなくなるからです。
通常のインテント・ロックです。
Intent exclusive (IX) – 意図的排他ロック (IX) が取得されると、トランザクションが下位階層のリソースに対して個別に排他 (X) ロックを取得し、下位階層のリソースを変更する意図があることを SQL Server に示します
Intent shared (IS) – 意図的共有ロック (IS) が取得されると、トランザクションが
Intent update (IU) – インテント共有ロック (IS) が取得されると、トランザクションは、階層内の下位のリソースで共有ロック (S) を個別に取得して、下位階層のリソースの一部を読み取る意図があることを SQL Server に示します。 インテント更新ロック（IU）はページ レベルでのみ取得でき、更新操作が行われるとすぐにインテント排他ロック（IX）
Conversion locks:
Shared with intent exclusive (SIX) – このロックを取得すると、トランザクションが下位階層のすべてのリソースを読み取り、下位階層のすべてのリソースの共有ロックを取得し、それらの一部（すべてではない）を変更しようとすることを示すことになる。 その際、変更すべき下位階層のリソースに対して意図的排他（IX）ロックを取得します。 実際には、トランザクションがテーブルのSIXロックを取得すると、変更されたページのインテント排他ロック（IX）と変更された行の排他ロック（X）を取得することを意味します。
Shared with Intent exclusive lock (SIX) は一度に 1 つのテーブルに対してのみ取得でき、他のトランザクションによる更新をブロックしますが、他のトランザクションがテーブル上の Intent shared (IS) ロックを取得できる下位階層リソースを読み取ることは妨げません。
Shared with Intent update (SIU) – これは shared (S) および intent update (IU) ロックを結合しているのでもう少し特殊なロックと言えるでしょう。 このロックの典型的な例は、トランザクションが PAGELOCK ヒントとクエリ、そして更新クエリで実行されたクエリを使用しているときです。 トランザクションがテーブルのSIUロックを取得した後、PAGELOCKヒントのクエリは共有（S）ロックを取得し、更新クエリは意図更新（IU）ロックを取得します
Update with intent exclusive (UIX) – 更新ロック（U）と意図排除（IX）がテーブルの下位階層リソースで同時に取得されているとき。
Schema locks (Sch) – SQL Serverデータベースエンジンは、2種類のスキーマロックを認識します。 スキーマ変更ロック (Sch-M) とスキーマ安定ロック (Sch-S)
- スキーマ変更ロック (Sch-M) は DDL 文が実行されると取得され、オブジェクトの構造が変更されると、ロックされたオブジェクト データへのアクセスを防止するようになります。 SQL Serverでは、ロックされたオブジェクトに対して1つのスキーマ・モディフィケーション・ロック（Sch-M）ロックを許可しています。 テーブルを変更するには、トランザクションはターゲット・オブジェクトの Sch-M ロックを取得するのを待つ必要があります。 スキーマ変更ロック（Sch-M）を取得すると、トランザクションはオブジェクトを変更でき、変更が完了するとロックは解放されます。 インデックスの再構築はテーブルの変更処理であるため、Sch-Mロックの典型的な例はインデックスの再構築である。 インデックス再構築IDが発行されると、そのテーブルに対してスキーマ変更ロック（Sch-M）が取得され、インデックス再構築処理が完了した後に解放されます（ONLINEオプションと併用すると、インデックス再構築は処理の終了時にまもなくSch-Mロックを取得します）
- スキーマ安定ロック（Sch-S）はスキーマ依存クエリーが編集、実行されて実行計画が生成されている間に取得されます。 この特定のロックは、オブジェクト・データにアクセスする他のトランザクションをブロックせず、スキーマ変更ロック (Sch-M) を除くすべてのロック・モードと互換性があります。 基本的に、スキーマ安定ロックは、テーブル構造の整合性を確保するために、すべての DML および選択クエリによって取得されます（クエリの実行中にテーブルが変更されないことを保証します）。
Bulk Update locks (BU) – このロックは、TABLOCK 引数/ヒントで発行されたときに、バルク インポート操作で使用されるように設計されています。 バルク更新ロックが取得されると、バルク・ロードの実行中、他のプロセスはテーブルにアクセスできなくなります。 ただし、バルク・アップデート・ロックは、別のバルク・ロードの並列処理を妨げるものではありません。 ただし、クラスタ化インデックス・テーブルでTABLOCKを使用すると、並列バルク・インポートができなくなることに注意してください。 これについての詳細は、Guidelines for Optimizing Bulk Import
Locking hierarchy
SQL Serverでは、データの読み取りや変更が行われるときに適用されるロック階層が導入されています。 ロック階層は、最上位階層のデータベースから始まり、テーブルやページを経由して最下位階層の行に至るまで
基本的に、データベース レベルには常に共有ロックがあり、トランザクションがデータベースに接続されると常に課されることになります。 データベース・レベルの共有ロックは、データベースのドロップや、使用中のデータベースに対するデータベース・バックアップの復元を防ぐために課される。 例えば、あるデータを読み込むためにSELECT文が発行されると、データベースレベルで共有ロック（S）が、テーブルとページレベルでインテント共有ロック（IS）が、そして行自体で共有ロック（S）が課せられる
DML文（すなわち、. 挿入、更新、削除）の場合、データベース レベルでは共有ロック (S)、テーブルおよびページ レベルではインテント排他ロック (IX) またはインテント更新ロック (IU) が、行では排他ロックまたは更新ロック (X または U) が適用されます
ロックが常に上から下へと取得されるのは、いわゆるレース状態が起こらないよう SQL Server によって防止されているためです。
ロック モードとロック階層が説明されたので、ロック モードとそれがどのようにロック階層に変換されるかをさらに詳しく説明しましょう。
すべてのロックモードがすべての階層で適用できるわけではありません。
行レベルでは、次の 3 つのロック モードが適用できます。
- Exclusive (X)
- Shared (S)
- Update (U)
それらのモードの互換性を理解するには、次のテーブルを参照してください。
| Shared (S) | Update (U) | |
| Exclusive (X) | ✗ | Shared (S) |
| 」となります。 | ✗ | ✓ |
| 更新 (U) | ✗ | ✓ |
✓ -互換 ✗ -不一致
テーブル レベルでは、次のようになります。 には、5 種類のロックがあります。
- Exclusive (X)
- Shared (S)
- Intent exclusive (IX)
- Intent shared (IS)
- Shared with intent exclusive (SIX)
これらのモードの互換性は以下の表で確認できる
。
| (s) | (ix) | (is) | (six) | |||||||
| ✗ | ✗ | ✗ | ||||||||
| (s) | ✗ | (s) | （以下同じ） ✗ | ✓ | ✗ | |||||
| ⑨ | ✗ | (IX) | ✗ | ✓ | ✓ | ✗ | ||||
| (is) | ✓ | ✗ | ✓ | ✓ | ||||||
| (six) | ✗ | ✗ | ✓ | ✗ | ✗ | 。 スキーマ ロック (Sch) は、テーブル レベル ロックでもあります。 これらのロックタイプの互換性をよりよく理解するために、この表を参照してください。
Lock escalationロックがあまりにも多くのリソースを使用する状況を防ぐために、SQL Server ではロック エスカレーション機能を導入しています。 エスカレーションを使用しないと、ロックはかなりの量のメモリ リソースを必要とする可能性があります。 削除操作を実行するために、各行のサイズが 500 バイトである 3 万行のデータに対してロックを課す必要がある場合を例に挙げてみましょう。 エスカレーションを行わない場合、データベースには共有ロック（S）、テーブルには1つのインテント排他ロック（IX）、ページには1875個のインテント排他ロック（IX）（8KBページには500バイトの16行が入るので、1875ページには3万行が入る）、行自体には3万個の排他ロック（X）が課されることになります。 各ロックのサイズは96バイトなので、31,877個のロックは、1回の削除操作で約3MBのメモリを消費することになります。 多数の操作を並行して実行すると、ロック マネージャーがスムーズに操作を実行できるようにするためだけに、かなりのリソースが必要になることがあります このような状況を防ぐために、SQL Server ではロック エスカレーションを使用します。 これは、1 つのレベルで 5,000 を超えるロックが取得される状況では、SQL Server はこれらのロックを 1 つのテーブル レベル ロックにエスカレーションします。 デフォルトでは、SQL Server は常にテーブル レベルに直接エスカレーションされるため、ページ レベルへのエスカレーションは発生しません。 多数の行やページ ロックを取得する代わりに、SQL Server はテーブル レベルで排他ロック (X) にエスカレーションします これによりリソースの必要性は減少しますが、テーブルでの排他ロック (X) は、他のトランザクションがロックされたテーブルにアクセスできないこと、そのテーブルにアクセスしようとするすべてのクエリがブロックされることを意味します。 したがって、これはシステムのオーバーヘッドを削減しますが、同時実行の競合の確率が高くなります エスカレーションの制御を提供するために、SQL Server 2008 R2 以降では。 LOCK_EXCALATION オプションは ALTER TABLE ステートメントの一部として導入されました。
USE AdventureWorks2014GOALTER TABLE Table_nameSET (LOCK_ESCALATION = < TABLE | AUTO | DISABLE > -いずれかのオプション）GO
これらのオプションはそれぞれロック エスカレーション プロセスを特定の制御できるように定義します。 Table – これは新しく作成されたすべてのテーブルのデフォルト オプションです。デフォルトでは、SQL Server は常にテーブル レベルへのロック エスカレーションを実行し、パーティション化されたテーブルも含まれます Auto – このオプションでは、テーブルがパーティション化されるとパーティション レベルへのロック エスカレーションを許可します。 5,000 個のロックが 1 つのパーティションで取得されると、ロック・エスカレーションはそのパーティションで排他ロック (X) を取得し、テーブルはインテント排他ロック (IX) を取得します。 テーブルがパーティション化されていない場合、ロック・エスカレーションはテーブル・レベルのロックを取得します（Tableオプションに等しい）。 これは非常に便利なオプションのように見えますが、デッドロックの原因になりやすいため、非常に慎重に使用する必要があります。 2 つのパーティションに 2 つのトランザクションがあり、排他ロック (X) を取得している状況で、トランザクションが他のトランザクションが使用しているパーティションから日付にアクセスしようとすると、デッドロックが発生します というわけです。 このオプションが有効な場合、データ アクセス パターンを慎重に制御することが非常に重要であり、これを達成することは容易ではありません。 繰り返しますが、このオプションは、SQL Server ロック マネージャーが過剰な量のメモリを使用することを避けるために、慎重に使用する必要があります このように、ロック エスカレーションは DBA にとって課題となる可能性があります。 アプリケーションの設計上、一度に 5,000 行以上を削除または更新する必要がある場合、ロック エスカレーションとその影響を避けるための解決策は、単一のトランザクションを、それぞれが 5,000 行未満を処理する 2 つ以上のトランザクションに分割することで、この方法でロック エスカレーションを回避できるかもしれません Get info about active SQL Server locks SQL Server には Dynamics Management View (DMV) sys.dm_tran_locks は、現在使用されているロック マネージャー リソースに関する情報を返すもので、トランザクションによって取得されたすべての「ライブ」ロックを表示します。 このDMVについての詳細は、sys.dm_tran_locks (Transact-SQL) に記載されています。 ロックの識別に使用される最も重要なカラムは、resource_type、request_mode、および resource_description です。 必要に応じて、情報情報の追加のリソースとしてより多くの列は、トラブルシューティング中に含めることができます ここでは、クエリの例です
SELECT resource_type, request_mode, resource_descriptionFROM sys.dm_tran_locksWHERE resource_type <> ‘DATABASE’
このクエリの where 節が除去するリソース_タイプのフィルターとして使用されています。 これらは常にデータベース レベルに存在するため、一般にデータベース上で取得された共有ロックが結果から除外されます。 ここで示される 3 つの列の簡単な説明： resource_type – ロックが取得されているデータベース リソースが表示されます。 この列には、次の値のいずれかを表示できます。 ALLOCATION_UNIT、APPLICATION、DATABASE、EXTENT、FILE、HOBT、METADATA、OBJECT、PAGE、KEY、RID request_mode – resource resource_description – short resource descriptionを表示し、すべてのロックモードに対して入力されるわけではありません。 ほとんどの場合、この列には行、ページ、オブジェクト、ファイルの ID が含まれます。 etc
Nikola is computer freak since 1981 and an SQL enthusiast with intention to become a freak.Nikola is the 1981 コンピュータ・フリーク。 SQL Server の監査、コンプライアンス、およびパフォーマンス監視を専門としています。 Nikola Dimitrijevic (see all)
|
