最適化されたロック

適用対象:Azure SQL DatabaseMicrosoft Fabric SQL Database

この記事では、最適化されたロックについて説明します。データベース エンジンの機能では、ロック メモリの消費量を減らし、同時トランザクションのブロックを減らすためのトランザクション ロック メカニズムが強化されています。

最適化されたロックとは

最適化されたロックは、大きなトランザクションに対して保持されるロックが極めて少ないため、ロック メモリの低減に役立ちます。 さらに、最適化されたロックにより、ロックのエスカレーションも回避されます。 これにより、テーブルへの同時アクセスが増えます。

最適化されたロックは、トランザクション ID (TID) のロックと修飾後ロック (LAQ) の 2 つの主要コンポーネントで構成されます。

• トランザクション ID (TID) は、トランザクションの一意の識別子です。 各行には、最後に変更された TID のラベルが付けられます。 キーまたは行識別子のロックが多くなる可能性がある代わりに、TID に対して 1 つのロックが使用されます。 詳細については、「トランザクション ID (TID) のロック」を参照してください。
• 修飾後のロック (LAQ) は、ロックを取得せずに、最新のコミット済みバージョンの行に対するクエリの述語を評価する最適化であり、コンカレンシーを向上させます。 詳細については、「修飾後のロック (LAQ)」を参照してください。

次に例を示します。

• 最適化されたロックを使用しない場合、テーブル内の 1,000 行を更新するには、トランザクションの終了まで保持される 1,000 個の排他 (X) 行ロックが必要になる可能性があります。
• 最適化されたロックを使用している場合、テーブル内の 1,000 行を更新するには、1,000 個の X 行ロックが必要になる可能性がありますが、各ロックは各行が更新されるとすぐに解放され、トランザクションの終了まで保持される TID ロックは 1 つだけです。 ロックは迅速に解放されるため、ロック メモリの使用量が減り、ロックのエスカレーションが発生する可能性が大幅に低くなり、ワークロードのコンカレンシーが向上します。

Note

最適化されたロックを有効にすると、データ変更言語 (DML) ステートメント ( INSERT、 UPDATE、 DELETE、 MERGEなど) によって取得される行およびページのロックが減少または排除されます。 スキーマ ロックなど、他の種類のデータベース ロックやオブジェクト ロックには影響しません。

可用性

最適化されたロックは、すべてのサービス レベルとコンピューティング サイズで、Azure SQL Database と Microsoft Fabric SQL Database でのみ使用できます。

最適化されたロックは、現在、Azure SQL Managed Instance または SQL Server では使用できません。

最適化されたロックは有効になっていますか?

最適化されたロックは、ユーザー データベースごとに有効になります。 データベースに接続し、次のクエリを使用して、データベースで最適化されたロックが有効になっているかどうかを確認します。

SELECT IsOptimizedLockingOn = DATABASEPROPERTYEX(DB_NAME(), 'IsOptimizedLockingOn');

結果	説明
0	最適化されたロックが無効になっています。
1	最適化されたロックが有効になっています。
NULL	最適化されたロックは使用できません。

最適化されたロックは、他のデータベース機能に基づいて構築されます。

• 最適化されたロックでは、データベースで高速データベース復旧 (ADR) を有効にする必要があります。
• 最適化されたロックの利点を最大限に活用するには、データベースに対して 読み取りコミット済みスナップショット分離 (RCSI) を有効にする必要があります。 最適化されたロックの LAQ コンポーネントは、RCSI が有効になっている場合にのみ有効です。

Azure SQL Database では、ADR と RCSI の両方が既定で有効になっています。 これらのオプションが現在のデータベースで有効になっていることを確認するには、データベースに接続し、次の T-SQL クエリを実行します。

SELECT name,
       is_read_committed_snapshot_on,
       is_accelerated_database_recovery_on
FROM sys.databases
WHERE name = DB_NAME();

ロック化の概要

これは、最適化されたロックが有効になっていない場合の動作の簡単な概要です。 詳細については、「トランザクションのロック化および行のバージョン管理ガイド」を参照してください。

データベース エンジンにおけるロックとは、トランザクションの ACID プロパティを保証するために、複数のトランザクションが同じデータを同時に更新することを防ぐメカニズムです。

トランザクションでデータを変更する必要がある場合は、データのロックを要求します。 データに対して他の競合するロックが保持されておらず、トランザクションが変更を続行できる場合は、ロックが付与されます。 データに対して別の競合するロックが保持されている場合、トランザクションはロックが解放されるのを待ってから続行する必要があります。

複数のトランザクションが同じデータに同時にアクセスしようとすると、同時読み取りと同時書き込みによる複雑な競合が発生する可能性があり、データベース エンジンでそれを解決する必要があります。 ロックは、エンジンが ANSI SQL トランザクション分離レベルのセマンティクスを提供できるメカニズムの 1 つです。 データベースのロックは不可欠ですが、コンカレンシー、デッドロック、複雑さ、ロックのオーバーヘッドの低下は、パフォーマンスとスケーラビリティに影響する可能性があります。

トランザクション ID (TID) のロック

行のバージョン管理ベースの分離レベルが使用されている場合、または ADR が有効になっている場合、データベース内のすべての行に内部的にトランザクション ID (TID) が含まれます。 この TID はディスクに保持されます。 行を変更するすべてのトランザクションでは、対象の行に TID でスタンプが付けられます。

TID ロック化では、行のキーをロックする代わりに、行の TID に対してロックが行われます。 変更中のトランザクションは、TID に対して X ロックを保持します。 その他のトランザクションは、TID に対する S ロックを取得し、最初のトランザクションが完了するまで待機します。 TID ロックでは、変更のためにページ ロックと行ロックが引き続き取得されますが、各ページ ロックと行ロックは各行が変更されるとすぐに解放されます。 トランザクションが終了するまで保持される唯一のロックは、複数のページロックと行 (キー) ロックを置き換える TID リソースの X ロックです。

書き込みトランザクションがアクティブな間の現在のセッションのロックを示す、次の例を考えてみましょう。

/* Is optimized locking is enabled? */
SELECT IsOptimizedLockingOn = DATABASEPROPERTYEX(DB_NAME(), 'IsOptimizedLockingOn');

CREATE TABLE t0
(
a int PRIMARY KEY,
b int NULL
);

INSERT INTO t0 VALUES (1,10),(2,20),(3,30);
GO

BEGIN TRANSACTION;

UPDATE t0
SET b = b + 10;

SELECT *
FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID
      AND
      resource_type IN ('PAGE','RID','KEY','XACT');

COMMIT TRANSACTION;
GO

DROP TABLE IF EXISTS t0;

最適化されたロックが有効になっている場合、要求は、X (トランザクション) リソースに対して 1 つの XACT ロックのみを保持します。

最適化されたロックが有効になっていない場合、同じ要求は 4 つのロック (行を含むページに対して 1 つの IX (意図排他) ロック、各行に 3 つの X キー ロック) を保持します。

sys.dm_tran_locks動的管理ビュー (DMV) は、最適化されたロック動作の監視など、ロックの問題の調査またはトラブルシューティングに役立ちます。

修飾後ロック (LAQ)

TID インフラストラクチャに基づいて、最適化されたロックによって、INSERT、UPDATE、DELETE などの DML ステートメントがロックを取得する方法が変わります。

最適化されたロックを使用しない場合、最初に更新 (U) 行ロックを取得することで、クエリ述語がスキャンで行ごとにチェックされます。 述語が満たされている場合、行を更新する前に排他的 (X) 行ロックが取得され、トランザクションが終了するまで保持されます。

最適化されたロックを使用しており、READ COMMITTED スナップショット分離レベル (RCSI) が有効な場合、ロックを取得せずに、最新のコミットされたバージョンの行の述語をオプティミスティックに確認できます。 述語が満たされない場合、クエリはスキャンの次の行に移動します。 述語が満たされている場合は、行を更新するために X 行ロックが取得されます。

つまり、変更する行の "修飾後" にロックが取得されます。 X 行ロックは、トランザクションの終了前に行の更新が完了するとすぐに解放されます。

述語の評価はロックを取得せずに実行されるため、異なる行を変更する同時クエリが相互にブロックされることはありません。

次に例を示します。

CREATE TABLE t1
(
a int NOT NULL,
b int NULL
);

INSERT INTO t1
VALUES (1,10),(2,20),(3,30);
GO

セッション 1	セッション 2
BEGIN TRANSACTION; UPDATE t1 SET b = b + 10 WHERE a = 1;
	BEGIN TRANSACTION; UPDATE t1 SET b = b + 10 WHERE a = 2;
COMMIT TRANSACTION;
	COMMIT TRANSACTION;

セッション 1 は行セッション 2 の更新が必要な U ロックを保持しているため、最適化されたロックがないとセッション 2 はブロックされます。 一方、最適化されたロックを使用する場合、セッション 2 はブロックされません。これは、U ロックが取得されておらず、最新のコミットされたバージョンの行 1 の列 a が 1 と等しい (セッション 2 の述語を満たしていない) からです。

LAQ は、述語をチェックした後に行が変更されないという前提で、楽観的に実行されます。 述語が満たされ、述語を確認した後に行が変更されていない場合は、現在のトランザクションによって変更されます。

U ロックは取得されないため、述語の評価後に同時トランザクションによって行が変更される可能性があります。 行に対して X TID ロックを保持しているアクティブなトランザクションがある場合、データベース エンジンはその完了を待機します。 述語が以前に評価された後に行が変更された場合、データベース エンジンは行を変更する前に述語をもう一度再評価 (再修飾) します。 述語がまだ満たされている場合は、行が変更されます。

述語の再修飾は、クエリ エンジン演算子のサブセットによってサポートされます。 述語の再評価が必要であっても、クエリ プランで述語の再修飾をサポートしていない演算子が使用されている場合、データベース エンジンは内部的にステートメント処理を中止し、LAQ なしで再起動します。 このような中止が発生すると、lock_after_qual_stmt_abort 拡張イベントが発生します。

一部のステートメント (たとえば、変数の代入 UPDATE ステートメント、OUTPUT 句を含むステートメントなど) は、セマンティクスを変更しないと中止および再開できません。 このようなステートメントでは、LAQ は使用されません。

次の例では、別のトランザクションによって行が変更されたため、述語が再評価されます。

CREATE TABLE t3
(
a int NOT NULL,
b int NULL
);

INSERT INTO t3 VALUES (1,10),(2,20),(3,30);
GO

セッション 1	セッション 2
BEGIN TRANSACTION; UPDATE t3 SET b = b + 10 WHERE a = 1;
	BEGIN TRANSACTION; UPDATE t3 SET b = b + 10 WHERE a = 1;
COMMIT TRANSACTION;
	COMMIT TRANSACTION;

LAQ ヒューリスティック

修飾後のロック (LAQ) ので説明されているように、LAQ を使用すると、一部のステートメントが内部的に再起動され、LAQ なしで処理される場合があります。 これが頻繁に発生する場合、繰り返し処理のオーバーヘッドが大きくなる可能性があります。 このオーバーヘッドを最小限に抑えるために、最適化されたロックではヒューリスティック メカニズムを使用して繰り返し処理を追跡します。 このメカニズムは、オーバーヘッドがしきい値を超えた場合に、データベースの LAQ を無効にします。

ヒューリスティック メカニズムの目的上、ステートメントによって実行される処理は、処理されたページ数 (論理読み取り) で測定されます。 ステートメント処理の開始後にデータベース エンジンが別のトランザクションによって変更された行を変更している場合、ステートメントが中止されて再起動される可能性があるため、ステートメントによって実行された処理は無駄になる可能性があるとして扱われます。 システムは、無駄な可能性がある作業の合計と、データベース内のすべてのステートメントによって実行された合計作業を追跡します。

無駄になる可能性のある作業の割合がしきい値を超えた場合、データベースに対して LAQ は無効になります。 再起動されたステートメントの数がしきい値を超えた場合、LAQ も無効になります。

無駄な作業と再起動されたステートメントの数がそれぞれのしきい値を下回った場合、LAQ はデータベースに対して再度有効になります。

最適化されたロックと RCSI によるクエリ動作変更

トランザクションの厳密な実行順序に依存する読み取りコミット スナップショット分離 (RCSI) の同時実行ワークロードでは、最適化されたロックが有効になっている場合、クエリのビヘイビアーに違いが生じる可能性があります。

トランザクション T2 がトランザクション T1 中に更新された列 t4 に基づいたテーブル b を更新する例を次に示します。

CREATE TABLE t4
(
a int NOT NULL,
b int NULL
);

INSERT INTO t4
VALUES (1,1);
GO

セッション 1	セッション 2
BEGIN TRANSACTION T1; UPDATE t4 SET b = 2 WHERE a = 1;
	BEGIN TRANSACTION T2; UPDATE t4 SET b = 3 WHERE b = 2;
COMMIT TRANSACTION;
	COMMIT TRANSACTION;

前のシナリオの結果を、適格性判断後のロック (LAQ) の有無にかかわらず評価してみましょう。

LAQ を使用しない場合

LAQ がない場合、トランザクション T2 の UPDATE ステートメントはブロックされ、トランザクション T1 が完了するまで待機します。 T1 が完了すると、T2 は述語が満たされるため、行設定列 b を 3 に更新します。

両方のトランザクションがコミットされると、テーブル t4 には次の行が含まれます。

 a | b
 1 | 3

LAQ の場合

LAQ では、トランザクション T2 は、列 b が 1 と等しい行の最新のコミット済みバージョンを使用して、述語 (b = 2) を評価します。 行は適格でないため、スキップされ、トランザクション T1 によってブロックされることなくステートメントが完了します。 この例では、LAQ はブロッキングを削除しますが、結果が異なります。

両方のトランザクションがコミットされると、テーブル t4 には次の行が含まれます。

 a | b
 1 | 2

重要

LAQ を使用しない場合でも、行バージョン管理ベースの分離レベルが使用されている場合、アプリケーションは、データベース エンジンがロック ヒントを使用せずに厳密な順序付けを保証すると想定すべきではありません。 (前の演習で示したように)、トランザクションの厳密な実行順序に依存する RCSI でワークロードを同時実行しているお客様に対する一般的な推奨事項は、ことです。

最適化されたロックの診断に関する追加機能

次の改善点は、最適化されたロックが有効になっている場合の、ブロックとデッドロック状態の監視とトラブルシューティングに役立ちます。

• 最適化されたロックの待機の種類
  • TID の XACT ロックの S 待機タイプ、および sys.dm_os_wait_stats (Transact-SQL) のリソースの説明:
    • LCK_M_S_XACT_READ - タスクが、読み取りを目的としている XACTwait_resource 型の共有ロックを待機中の場合に発生します。
    • LCK_M_S_XACT_MODIFY - タスクが、変更を目的としている XACTwait_resource 型の共有ロックを待機中の場合に発生します。
    • LCK_M_S_XACT - タスクが、目的を推測できない XACTwait_resource 型の共有ロックを待機しているときに発生します。 このシナリオは一般的ではありません。
• ロック リソースの可視性
  • XACT ロック リソース。 詳しくは、「resource_description」の「」をご覧ください。
• リソースの可視性を待機する
  • XACT はリソースを待機します。 詳細については、「wait_resource」の「」を参照してください。
• デッドロック グラフ
  • デッドロック状態レポート <resource-list> の各リソースにおいて、各 <xactlock> 要素は、基になるリソースとデッドロック状態の各メンバーのロックに関する特定の情報を報告します。 詳細と例については、「最適化されたロックとデッドロック」を参照してください。
• 拡張イベント
  • lock_after_qual_stmt_abort イベントは、別のトランザクションとの競合が原因でステートメントが内部的に中止され、再起動されたときに発生します。 詳細については、「修飾後のロック (LAQ)」を参照してください。

最適化されたロックを使用したベスト プラクティス

読み取りコミット済み スナップショット分離 (RCSI) を有効にする

最適化されたロックの利点を最大限に活用するには、データベースで読み取りコミット済みスナップショット分離 (RCSI) を有効にし、デフォルトの分離レベルとして READ COMMITTED 分離を使用することをおすすめします。 まだ有効になっていない場合は、 master データベースに接続し、次のステートメントを実行して RCSI を有効にします。

ALTER DATABASE [your-database-name] SET READ_COMMITTED_SNAPSHOT ON;

Azure SQL データベースでは、RCSI は既定で有効になっており、READ COMMITTED はデフォルトの分離レベルです。 RCSI が有効で、READ COMMITTED 分離レベルを使用している場合、閲覧者はステートメントの開始時に取得されたスナップショットから行のバージョンを読み取ります。 LAQ を使用すると、ライターは U ロックを取得せずに、行の最新のコミット済みバージョンに基づいて述語ごとに行を修飾します。 LAQ では、クエリは行が条件を満たし、その行にアクティブな書き込みトランザクションがある場合にのみ待機します。 最新のコミット済みバージョンに基づいて修飾し、修飾された行のみをロックすると、ブロックが減り、コンカレンシーが向上します。

ブロックの削減に加えて、必要なロック メモリも削減されます。 これは、閲覧者がロックを取得せず、ライターはトランザクションの終了まで保持されるロックではなく、短期間のロックのみを取得するためです。 REPEATABLE READ や SERIALIZABLE などのより厳格な分離レベルを使用する場合、最適化されたロックがリーダーとライターの両方で有効な場合でも、トランザクションが終了するまでデータベース エンジンは行とページのロックを保持します。その結果、ブロック メモリとロック メモリの使用量が増加します。

ヒントのロック化を回避する

、UPDLOCK、READCOMMITTEDLOCK、XLOCK などのHOLDLOCKは、最適化されたロックが有効になっている場合に受け入れられますが、最適化されたロックの利点が低減されます。 ロック ヒントは、ロック ヒントの意図を尊重するために、行またはページのロックを取得し、トランザクションの終了までそれらを保持することをデータベース エンジンに強制します。 一部のアプリケーションには、ロック ヒントが必要なロジックがあります。たとえば、UPDLOCK ヒントで行を読み取り、後で更新する場合です。 ロック ヒントは、必要な場合にのみ使用することをおすすめします。

最適化されたロックを使用する場合、既存のクエリに制限はなく、クエリを書き直す必要もありません。 ヒントを使用していないクエリは、最適化されたロックから最も大きな利益を得ます。

クエリ内の 1 つのテーブルに対するテーブル ヒントによって、同じクエリ内の他のテーブルに対する最適化されたロックが無効になることはありません。 さらに、最適化されたロックは、INSERT、UPDATE、DELETE、MERGE などのDML ステートメントによって更新されるテーブルのロック ビヘイビアーにのみ影響します。 次に例を示します。

CREATE TABLE t5
(
a int NOT NULL,
b int NOT NULL
);

CREATE TABLE t6
(
a int NOT NULL,
b int NOT NULL
);
GO

INSERT INTO t5 VALUES (1,10),(2,20),(3,30);
INSERT INTO t6 VALUES (1,10),(2,20),(3,30);
GO

UPDATE t5 SET t5.b = t6.b
FROM t5
INNER JOIN t6 WITH (UPDLOCK)
ON t5.a = t6.a;

前のクエリの例では、テーブル t6 のみがロック ヒントの影響を受けますが、t5 は最適化されたロックの利点を引き続き利用できます。

UPDATE t5
SET t5.b = t6.b
FROM t5 WITH (REPEATABLEREAD)
INNER JOIN t6
ON t5.a = t6.a;

前のクエリの例では、t5 分離レベルを使用し、トランザクションが終了するまでロックを保持するのはテーブル REPEATABLE READ だけです。 その他は、t5 が最適化されたロックの恩恵を受けるよう更新します。 HOLDLOCK ヒントにも同じことが当てはまります。

よく寄せられる質問 (FAQ)

新規データベースと既存データベースの両方で、最適化されたロックは既定でオンになっていますか?

Azure SQL Database 内、はい。

最適化されたロックが有効になっているかどうかを検出するにはどうすればよいですか?

「最適化されたロックは有効になっていますか?」を参照してください。

データベース上で高速データベース復旧 (ADR) が有効になっていない場合はどうなりますか?

ADR が無効になっている場合、最適化されたロックも自動的に無効になります。

最適化されたロックにもかかわらずクエリを強制的にブロックする場合はどうすればよいでしょうか。

RCSI を使用しているお客様は、最適化されたロックが有効になっているときに 2 つのクエリ間でブロックを強制するには、READCOMMITTEDLOCK クエリ ヒントを使用します。

最適化されたロックは読み取り専用セカンダリ レプリカで使用されますか?

いいえ。読み取り専用レプリカ上では DML ステートメントを実行できず、対応する行およびページのロックは取得されないからです。

tempdb および一時テーブルのデータを変更するときに、最適化されたロックは使用されますか?

現時点ではありません。

関連するコンテンツ

• トランザクションのロックおよび行のバージョン管理ガイド
• 読み取りコミット済み スナップショット分離 (RCSI)
• sys.dm_tran_locks (Transact-SQL)
• データベース復旧の高速化