最適化されたロック
適用対象: Azure SQL データベース
この記事は、最適化されたロック機能は、ロック メモリの消費量を減らし、同時トランザクション間でブロックする、強化されたトランザクションロック化 メカニズムを提供する新しいSQL Server データベース エンジンについてです。
最適化されたロックとは
最適化されたロックは、大きなトランザクションに対して保持されるロックが非常に少ないため、ロック メモリを減らすのに役立ちます。 さらに、最適化されたロックにより、ロックのエスカレーションも回避されます。 これにより、テーブルへの同時アクセスが増えます。
最適化されたロックは、トランザクション ID (TID) ロック化と修飾後ロック (LAQ) の 2 つの主要コンポーネントで構成されます。
- トランザクション ID (TID) は、トランザクションの一意の識別子です。 各行には、最後に変更された TID のラベルが付けられます。 キーまたは行識別子のロックが多くなる可能性がある代わりに、TID に対して 1 つのロックが使用されます。 詳細については、 トランザクション ID (TID) のロック化に関するセクションを参照してください。
- 修飾後のロック (LAQ) は、ロックを取得せずに、最新のコミット済みバージョンの行に対するクエリの述語を評価する最適化であり、コンカレンシーが向上します。 詳細については、修飾後のロック (LAQ) に関するセクションを参照してください。
次に例を示します。
- 最適化されたロックを使用しない場合、テーブル内の 100 万行を更新するには、トランザクションの最後まで 100 万 (X) 行の排他行ロックを保持する必要があります。
- 最適化されたロックを使用する場合、テーブル内の 100 万行を更新するには 100 万 X 行の行ロックが必要になる場合がありますが、各行が更新されるとすぐに各ロックが解放され、トランザクションが終了するまで 1 つの TID ロックのみが保持されます。
この記事では、最適化されたロックの 2 つの主要な概念について詳しく説明します。
可用性
現在、最適化されたロックはAzure SQL Database でのみ使用できます。 詳細については、 最適化されたロックが現在使用できる場所を参照してください
最適化されたロックは有効になっていますか?
最適化されたロックは、ユーザー データベースごとに有効になります。 データベースに接続し、次のクエリを使用して、データベースで最適化されたロックが有効になっているかどうかを確認します。
SELECT IsOptimizedLockingOn = DATABASEPROPERTYEX('testdb', 'IsOptimizedLockingOn');
DATABASEPROPERTYEX
で指定されたデータベースに接続されていない場合、結果は NULL
。 0
を受け取る必要があります (最適化されたロックが無効になっています)、または 1
(有効になっています)。
最適化されたロックは、他のデータベース機能に基づいて構築されます。
- 最適化されたロックでは、データベースで高速データベース復旧 (ADR) を有効にする必要があります。
- 最適化されたロックの利点を最大限に活用するには、データベースに対して 読み取りコミット済みスナップショット分離 (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();
ロック化の概要
これは、最適化されたロックが有効になっていない場合の動作の概要です。 詳細については、「トランザクションのロック化および行のバージョン管理ガイド」を参照してください。
データベース エンジンでは、ロック化は、データ整合性と整合性を保護するために、複数のトランザクションが同じデータを同時に更新できないようにするメカニズムです。
トランザクションでデータを変更する必要がある場合は、データのロックを要求できます。 データに対して他の競合するロックが保持されておらず、トランザクションが変更を続行できる場合は、ロックが付与されます。 データに対して別の競合するロックが保持されている場合、トランザクションはロックが解放されるのを待ってから続行する必要があります。
複数のトランザクションが同じデータに同時にアクセスできる場合、データベース エンジンは、同時の読み取りと書き込みによる複雑な競合を解決する必要があります。 ロック化は、データベース エンジンが ANSI SQL トランザクション分離レベルのセマンティクスを提供できるメカニズムの 1 つです。 データベースでのロック化は必須ですが、コンカレンシーの削減、デッドロック、複雑さ、ロックのオーバーヘッドがパフォーマンスとスケーラビリティに影響を与える可能性があります。
最適化されたロックと、トランザクション ID (TID) のロック化
行のバージョン管理が使用されている場合、データベース エンジン内のすべての行にはトランザクション ID (TID) が内部的に含まれます。 この TID はディスクに保持されます。 行を変更するすべてのトランザクションでは、対象の行に TID でスタンプが付けられます。
TID ロック化では、行のキーをロックする代わりに、行の TID に対してロックが行われます。 変更中のトランザクションは、TID に対して X ロックを保持します。 他のトランザクションでは、最初のトランザクションがまだアクティブかどうかを確認するために、TID に対する S ロックが取得されます。 TID ロック化では、ページロックと行ロックは更新のために引き続き行われますが、各行が更新されるとすぐに各ページと行ロックが解放されます。 トランザクションが終了するまで保持される唯一のロックは、次のデモで示すように、ページロックと行ロック (キー) ロックを置き換える TID リソースの X ロックです。 (その他の標準データベースおよびオブジェクト ロックは、最適化されたロックの影響を受けません。)
最適化されたロックは、大きなトランザクションに対して保持されるロックが非常に少ないため、ロック メモリを減らすのに役立ちます。 さらに、最適化されたロックにより、ロックのエスカレーションも回避されます。 これにより、他のコンカレンシートランザクションがテーブルにアクセスできるようになります。
ユーザーの現在のセッションでロックを検索する次の T-SQL サンプル シナリオを考えてみましょう。
CREATE TABLE t0
(a int PRIMARY KEY not null
,b int null);
INSERT INTO t0 VALUES (1,10),(2,20),(3,30);
GO
BEGIN TRAN
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 TRAN
GO
DROP TABLE IF EXISTS t0;
最適化されたロックの利点のない同じクエリでは、次の 4 つのロックが作成されます。
sys.dm_tran_locks動的管理ビュー (DMV) は、最適化されたロックの動作の監視など、ロック化の問題の調査またはトラブルシューティングに役立ちます。
最適化されたロックと修飾後のロック (LAQ)
TID インフラストラクチャに基づいて、最適化されたロックによって、クエリ述語がロックをセキュリティで保護する方法が変更されます。
最適化されたロックを使用しない場合、クエリの述語は、最初に更新 (U) 行ロックを行うことで、スキャンで行ごとにチェックされます。 述語が満たされている場合は、行を更新する前に X 行ロックが実行されます。
最適化されたロックを使用し、読み取りコミット済みスナップショット分離レベル (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 TRAN UPDATE t1 SET b=b+10 WHERE a=1; |
|
BEGIN TRAN UPDATE t1 SET b=b+10 WHERE a=2; |
|
COMMIT TRAN |
|
COMMIT TRAN |
ブロックの動作は、前述の例の最適化されたロックを使用して変更されます。 最適化されたロックがないと、セッション 2 はブロックされます。
ただし、最適化されたロックでは、行 1 の最新のコミット済みバージョンに a=1 が含まれており、セッション 2 の述語を満たしていないため、セッション 2 はブロックされません。
述語が満たされている場合は、行のアクティブなトランザクションが完了するまで待機します。 S TID ロックを待機する必要がある場合は、行が変更され、最新のコミット済みバージョンが変更されている可能性があります。 その場合、データベース エンジンは、更新の競合のためにトランザクションを中止する代わりに、同じ行で述語の評価を再試行します。 述語が再試行時に修飾される場合、行は更新されます。
述語の変更が自動的に再試行される場合の次の例を考えてみましょう。
CREATE TABLE t2
(a int not null
,b int null);
INSERT INTO t2 VALUES (1,10),(2,20),(3,30);
GO
セッション 1 | セッション 2 |
---|---|
BEGIN TRAN UPDATE t2 SET b=b+10 WHERE a=1; |
|
BEGIN TRAN UPDATE t2 SET b=b+10 WHERE a=1; |
|
COMMIT TRAN |
|
COMMIT TRAN |
最適化されたロックと RCSI によるクエリ動作変更
トランザクションの厳密な実行順序に依存するワークロードを使用する、読み取りコミット済みスナップショット分離レベル (RCSI) の同時実行システムでは、最適化されたロックが有効になっている場合に異なるクエリ動作が発生する可能性があります。
トランザクション T2 がトランザクション T1 中に更新された列 b
に基づいたテーブル t1
を更新する例を次に示します。
CREATE TABLE t1 (a int not null, b int null);
INSERT INTO t1 VALUES (1,1);
GO
セッション 1 | セッション 2 |
---|---|
BEGIN TRAN T1 更新 t1 SET b=2 WHERE a=1; |
|
BEGIN TRAN T2 更新 t1 SET b=3 WHERE b=2; |
|
COMMIT TRAN | |
COMMIT TRAN |
最適化されたロックの不可欠な部分である、修飾後のロック (LAQ) の有無に関する上記のシナリオの結果を評価してみましょう。
LAQ を使用しない場合
LAQ がない場合、トランザクション T2 はブロックされ、トランザクション T1 が完了するまで待機します。
両方のトランザクションがコミットされると、テーブル t1
には次の行が含まれます。
a | b
1 | 3
LAQ の場合
LAQ では、トランザクション T2 は、行 b (b
=バージョン ストアにある1) の最新のコミット済みバージョンを使用してその述語 (b
=2) を評価します。 この行は修飾されません。そのため、トランザクション T1 によってブロックされることなく、それはスキップされ、T2 は次の行に移動します。 この例では、LAQ はブロッキングを削除しますが、結果が異なります。
両方のトランザクションがコミットされると、テーブル t1
には次の行が含まれます。
a | b
1 | 2
重要
LAQ がなくても、アプリケーションでは、SQL Server (バージョン管理の分離レベル) では、ロック化 ヒントを使用せずに厳密な順序付けが保証されることを想定しないでください。 (前の演習で示したように) トランザクションの厳密な実行順序に依存するワークロードを使用する RCSI の同時実行 システムのお客様に対する一般的な推奨事項は、 より厳密な分離レベルを使用することです。
最適化されたロックの診断に関する追加機能
最適化されたロックでブロックとデッドロックの監視とトラブルシューティングをサポートするには、次の追加機能を探します。
- 最適化されたロックの待機の種類
XACT
sys.dm_os_wait_stats (Transact-SQL) 内の待機の種類とリソースの説明:LCK_M_S_XACT_READ
- タスクが、読み取りを目的としているXACT
wait_resource
型の共有ロックを待機中の場合に発生します。LCK_M_S_XACT_MODIFY
- タスクが、変更を目的としているXACT
wait_resource
型の共有ロックを待機中の場合に発生します。LCK_M_S_XACT
- タスクが、目的を推測できないXACT
wait_resource
型の共有ロックを待機中の場合に発生します。 まれ。
- ロック リソースの可視性
XACT
ロック リソース。 詳しくは、「sys.dm_tran_locks (Transact-SQL)」の「resource_description
」をご覧ください。
- リソースの可視性を待機する
XACT
はリソースを待機します。 詳細については、「sys.dm_exec_requests (Transact-SQL)」の「wait_resource
」を参照してください。
- デッドロック グラフ
- デッドロック レポートの
<resource-list>
の各リソースで、各<xactlock>
要素は、基になるリソースと、デッドロックの各メンバーのロックに関する特定の情報を報告します。 詳細と例については、「最適化されたロックとデッドロック」を参照してください。
- デッドロック レポートの
最適化されたロックを使用したベスト プラクティス
読み取りコミット済み スナップショット分離 (RCSI) を有効にする
最適化されたロックの利点を最大限に活用するには、データベースで読み取りコミット済みスナップショット分離 (RCSI) を有効にし、既定の分離レベルとして読み取りコミットされた分離を使用することをおすすめします。 有効になっていない場合は、次の例を使用して RCSI を有効にします。
ALTER DATABASE databasename SET READ_COMMITTED_SNAPSHOT ON;
Azure SQL Database では、RCSI は既定で有効になっており、読み取りコミット済みは既定の分離レベルです。 RCSI を有効にし、読み取りコミット済み分離レベルを使用する場合、閲覧者はライターをブロックせず、ライターは閲覧者をブロックしません。 閲覧者は、クエリの開始時に作成されたスナップショットから行のバージョンを読み取ります。 LAQ を使用すると、ライターは U ロックを取得せずに、行の最新のコミット済みバージョンに基づいて述語ごとに行を修飾します。 LAQ では、クエリは行が修飾され、その行にアクティブな書き込みトランザクションがある場合にのみ待機します。 最新のコミット済みバージョンに基づいて修飾し、修飾された行のみをロックすると、ブロックが減り、コンカレンシーが向上します。
ブロックの削減に加えて、必要なロック メモリも削減されます。 これは、閲覧者がロックを取得せず、ライターはトランザクションの終了時に期限切れになるロックではなく、短時間のロックのみを受け取るためです。 反復可能な読み取りやシリアル化可能などのより厳密な分離レベルを使用する場合、データベース エンジンは、閲覧者とライターの両方に対して、トランザクションの最後まで行ロックとページ ロックを保持することを強制され、ブロックとロックのメモリが増加します。
ヒントのロック化を回避する
テーブルとクエリのヒントは受け入れられますが、最適化されたロックの利点が軽減されます。 クエリ内の UPDLOCK
、READCOMMITTEDLOCK
、XLOCK
、HOLDLOCK
などのロック ヒントにより、最適化されたロックのすべての利点を削減できます。 このようなロック ヒントをクエリに含めて、データベース エンジンは、ロック ヒントの意図を尊重するために、行/ページ ロックを取得し、トランザクションの最後まで保持するように強制します。 一部のアプリケーションには、ロック ヒントが必要なロジックがあります。たとえば、UPDLOCK
で選択した行を読み取り、後で更新する場合です。 ロック ヒントは、必要な場合にのみ使用することをおすすめします。
最適化されたロックでは、既存のクエリに制限はなく、クエリを書き換える必要はありません。 ヒントを使用していないクエリは、最適化されたロックの利点を最大限に活用できます。
クエリ内の 1 つのテーブルのテーブル ヒントでは、同じクエリ内の他のテーブルの最適化されたロックは無効になりません。 さらに、最適化されたロックは、UPDATE ステートメントによって更新されるテーブルのロック化動作にのみ影響します。 次に例を示します。
CREATE TABLE t3
(a int not null
, b int not null);
CREATE TABLE t4
(a int not null
, b int not null);
GO
INSERT INTO t3 VALUES (1,10),(2,20),(3,30);
INSERT INTO t4 VALUES (1,10),(2,20),(3,30);
GO
UPDATE t3 SET t3.b = t4.b
FROM t3
INNER JOIN t4 WITH (UPDLOCK) ON t3.a = t4.a;
前のクエリの例では、テーブル t4
のみがロック ヒントの影響を受けますが、t3
は最適化されたロックの利点を引き続き利用できます。
UPDATE t3 SET t3.b = t4.b
FROM t3 WITH (REPEATABLEREAD)
INNER JOIN t4 ON t3.a = t4.a;
前のクエリ例では、繰り返し可能な読み取り分離レベルを使用し、トランザクションが終了するまでロックを保持するのはテーブルt3
だけです。 その他は、t3
が最適化されたロックの恩恵を受けるよう更新します。 HOLDLOCK
ヒントにも同じことが当てはまります。
よく寄せられる質問 (FAQ)
最適化されたロックは現在どこで利用できますか?
現在、最適化されたロックはAzure SQL Database で使用できます。
最適化されたロックは、次のサービス レベルで使用できます。
- すべてのDTU サービス レベル
- プロビジョニング済みおよびサーバーレスを含むすべての仮想コア サービス レベル
最適化されたロックは以下では現在利用できません。
- Azure SQL Managed Instance
- SQL Server 2022 (16.x)
新規データベースと既存データベースの両方で、最適化されたロックは既定でオンになっていますか?
Azure SQL Database 内、はい。
最適化されたロックが有効になっているかどうかを検出するにはどうすればよいですか?
データベースで高速データベース復旧 (ADR) が有効になっていない場合はどうなりますか?
ADR が無効になっている場合、最適化されたロックも自動的に無効になります。
最適化されたロックにもかかわらずクエリを強制的にブロックする場合はどうすればよいでしょうか。
RCSI を使用しているお客様は、最適化されたロックが有効になっているときに 2 つのクエリ間でブロックを強制するには、READCOMMITTEDLOCK
クエリ ヒントを使用します。