最適化されたロック
適用対象:
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 の
最適化されたロックは、現在、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 つのロックが保持されます。各行に対して 3 つの X キー ロックと、その行を含むページに対して 1 つの IX (インテント排他) ロックが保持されます。
sys.dm_tran_locks動的管理ビュー (DMV) は、最適化されたロック動作の監視など、ロックの問題の調査またはトラブルシューティングに役立ちます。
最適化されたロックと修飾後のロック (LAQ)
TID インフラストラクチャ上に構築された最適化ロックにより、INSERT、UPDATE、DELETE、MERGE などの 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 t1SET b = b + 10WHERE a = 1; |
|
BEGIN TRANSACTION;UPDATE t1SET b = b + 10WHERE a = 2; |
|
COMMIT TRANSACTION; |
|
COMMIT TRANSACTION; |
セッション 1 は行セッション 2 の更新が必要な U ロックを保持しているため、最適化されたロックがないとセッション 2 はブロックされます。 ただし、最適化されたロックでは、セッション 2 はブロックされません。U ロックが行われないためです。また、最新のコミット済みバージョンの行 1 では、列 a は 1 に等しく、セッション 2 の述語を満たしていません。
LAQ U ロックは取得されないため、述語が評価された後、同時実行トランザクションによって行が変更される可能性があります。 述語が満たされ、その行にその他のアクティブなトランザクションがない (X TID ロックがない) 場合、行は変更されます。 アクティブなトランザクションがある場合、データベース エンジンはその完了を待機し、他のトランザクションが行を変更した可能性があるため、変更時に述語を再評価します。 述語がまだ満たされている場合は、行が変更されます。
別のトランザクションによって行が変更されたため、述語の評価が自動的に再試行される次の例を考えてみましょう。
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 t3SET b = b + 10WHERE a = 1; |
|
BEGIN TRANSACTION;UPDATE t3SET b = b + 10WHERE a = 1; |
|
COMMIT TRANSACTION; |
|
COMMIT TRANSACTION; |
最適化されたロックと 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 t4SET b = 2WHERE a = 1; |
|
BEGIN TRANSACTION T2;UPDATE t4SET b = 3WHERE 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 でワークロードを同時実行しているお客様に対する一般的な推奨事項は、SERIALIZABLEことです。
最適化されたロックの診断に関する追加機能
次の改善点は、最適化されたロックが有効になっている場合の、ブロックとデッドロック状態の監視とトラブルシューティングに役立ちます。
- 最適化されたロックの待機の種類
- 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型の共有ロックを待機しているときに発生します。 このシナリオは一般的ではありません。
- TID の
- ロック リソースの可視性
- リソースの可視性を待機する
- デッドロック グラフ
- デッドロック状態レポート
<resource-list>の各リソースにおいて、各<xactlock>要素は、基になるリソースとデッドロック状態の各メンバーのロックに関する特定の情報を報告します。 詳細と例については、「最適化されたロックとデッドロック」を参照してください。
- デッドロック状態レポート
最適化されたロックを使用したベスト プラクティス
読み取りコミット済み スナップショット分離 (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 および一時テーブルのデータを変更するときに、最適化されたロックは使用されますか?
現時点ではありません。