トランザクション分離レベルとコンカレンシー制御を使用してデータの整合性を維持する
適切なハードウェアと構成の選択は、データベースのパフォーマンスの一部にすぎません。 2 人の顧客が在庫の最後の項目をまったく同じ時点で購入しようとするとどうなりますか? または、別のトランザクションが変更の途中にある価格をレポートが読み取るとき。 トランザクション分離レベルは、データベースがこのような状況を処理する方法を制御します。
分離レベルのしくみ
分離レベルは、あるトランザクションが別のトランザクションのコミットされていない作業をどの程度見ることができるかを定義します。 SQL 標準では、それらを 3 つの コンカレンシーの副作用に囲み、それぞれがアプリケーションの実際の問題にマップされます。 eコマース アプリケーションを使用して、それらを説明しましょう。
- ダーティ リード: 顧客の注文は、別のトランザクションで設定されたがまだコミットされていない割引価格を読み取ります。 価格を設定したトランザクションがロールバックした場合、注文では存在しない価格が使用されます。 ダーティ読み取りは、別のトランザクションからコミットされていないデータを読み取ると定義されます。この場合、他のトランザクションがコミットしない場合に誤った動作が発生する可能性があります。
- 反復不可能な読み取り: 同じトランザクションで、在庫サービスは品目の在庫数を読み取ります。 最初の読み取りの後、トランザクションはいくつかの計算を行い、同じ行をもう一度読み取ります。 これらの手順の間に、別のトランザクションが変更をコミットしたので、カウントが異なっています。 反復不可能な読み取りは、トランザクションが同じ行を 2 回読み取り、毎回異なる値を取得するときに発生します。
- ファントム読み取り: レポートは、過去 1 時間に行われたすべての注文に対してクエリを実行し、合計を計算した後、同じトランザクション内で同じクエリの瞬間を実行します。 別のトランザクションが間に新しい注文を挿入したので、合計が一致しません。 ファントム読み取りは、トランザクションがクエリを再実行し、以前は行われなかった新しい行を確認したときに発生します。
数百人の同時顧客を含むフラッシュセール中のeコマースアプリケーションを検討してください。 3 つの副作用はすべて一度に表示できます。. 選択する分離レベルによって、アプリケーションが許容するものと、そのアプリケーションが受け入れるブロックの量が決まります。
これらの各問題には、複数の操作を実行するトランザクションが含まれていることに注意してください。行の読み取り、作業の実行、再度読み取り、または決定を行います。 通常、単一ステートメント トランザクションは非常に迅速に完了するため、これらの競合はほとんど発生しません。 分離レベルは、トランザクションが複数のステートメントにまたがる場合に最も重要です。これは、データの読み取り、ルールの適用、結果の書き込みを行うビジネス ロジックで一般的です。
分離レベルが機能する一般的なパターンを次に示します。 2 人の顧客が、同じ製品の最後のユニットを購入しようとしているとします。
-- Transaction A (Customer 1) -- Transaction B (Customer 2)
BEGIN TRANSACTION;
BEGIN TRANSACTION;
SELECT @Stock = StockCount
FROM Products
WHERE ProductID = 42;
-- @Stock = 1, proceed with order
SELECT @Stock = StockCount
FROM Products
WHERE ProductID = 42;
-- @Stock = 1, proceed with order
UPDATE Products
SET StockCount = StockCount - 1
WHERE ProductID = 42;
INSERT INTO Orders (ProductID, Quantity)
VALUES (42, 1);
COMMIT TRANSACTION;
UPDATE Products
SET StockCount = StockCount - 1
WHERE ProductID = 42;
INSERT INTO Orders (ProductID, Quantity)
VALUES (42, 1);
COMMIT TRANSACTION;
-- StockCount is now -1. Two orders placed for one item.
どちらのトランザクションも在庫数 1 を読み取り、両方とも続行することを決定しました。 分離レベルは、トランザクション B に元の値が表示されるか、トランザクション A が最初に完了するまで待機するかを決定します。 分離レベルがダーティリードを許可している場合、トランザクション B は未コミットの値 1 を読み取り、そのまま続行することで過剰販売につながります。 分離レベルがより制限の厳しい場合、トランザクション B はトランザクション A がコミットするまで待機します。 トランザクション A が正常にコミットされると、トランザクション B は更新された在庫数 0 を確認し、2 番目の注文が完了するのを防ぐことができます。 トランザクション A が何らかの理由でロールバックした場合、トランザクション B は元の値 1 を確認し、コミットされていないデータを読み取ることなく続行できます。
SQL Server と Azure SQL Database では、6 つの分離レベルがサポートされています。 最初の 4 つでは 、ペシミスティック コンカレンシー (ロック) が使用されます。 最後の 2 つでは 、オプティミスティック コンカレンシー (行のバージョン管理) が使用されます。
ロックベースの分離レベル
ロックベースの分離では、データベース エンジンは共有ロックと排他ロックを使用して、トランザクションが相互に干渉しないようにします。 レベルの制限が厳しいほど、これらのロックが長く保持されます。 副次的な影響は少なくなりますが、待機状態になってしまうトランザクションが増えます。
READ UNCOMMITTED は、最も高速で最もリスクの高い分離レベルです。 読み取り時の共有ロックは完全にスキップされるため、読み取り/書き込みブロックはありません。 ただし、これは、トランザクションが別のトランザクションのコミットされていない変更を読み取ることができることも意味します。 その他のトランザクションがロールバックされた場合、アプリケーションは存在しないデータに対して動作しました。 このレベルは、大まかな注文数を示すダッシュボードのように、おおよその結果が許容される場合にのみ意味があります。
READ COMMITTED は SQL Server の既定の分離レベルであり、基本的なバランスを取ります。 各読み取りでは、共有ロックが取得され、データが取得され、ロックがすぐに解放されます。 ロックをすばやく解放するだけで、ダーティ リードを十分防ぐことができます。 ただし、2 つの読み取りの間で別のトランザクションが行を変更するのを止めないため、反復不可能な読み取りとファントム読み取りは引き続き発生する可能性があります。
REPEATABLE READ は、READ COMMITTED よりも 1 ステップ進みます。 トランザクションが完了するまで、読み取ったすべての行に対して共有ロックが保持されます。 トランザクションがアクティブな間、これらの行を変更できなくなり、ダーティ読み取りと反復不可能な読み取りの両方が防止されます。 ただし、他のトランザクションでもクエリ フィルターに一致する新しい行を挿入できるため、ファントム読み取りは引き続き可能性があります。
SERIALIZABLE は、以前のレベルで行われるすべての処理を行います。 読み取った行だけでなく、キー値間のギャップもカバーする範囲ロックを受け取り、それらの範囲への挿入をブロックします。 ダーティ読み取り、反復不可能な読み取り、ファントム読み取りはすべて禁止されます。 ただし、コストは実際の値です。範囲ロックによってブロックが大幅に増え、デッドロックの可能性が高くなります。 ファントム読み取りによって計算エラーが発生する財務調整や在庫予約などのシナリオでは、このレベルを予約します。
行のバージョン管理の分離レベル
読者がライターを待つ必要がまったくなかった場合はどうでしょうか。 重要なことは、行のバージョン管理の分離で何に対処しているのかです。 データベース エンジンは、ロックの背後でブロックするのではなく、以前のバージョンの行を バージョン ストアに保持します。 トランザクションは、別のトランザクションが変更しているデータを読み取る必要がある場合、待機するのではなく、バージョン ストアから読み取ります。 Azure SQL Database では、高速データベース復旧 (ADR) が常に有効になっているため、バージョン ストアは永続バージョン ストア (PVS) を使用してデータベース自体に存在します。
Read Committed Snapshot Isolation は、データベース レベルでの READ COMMITTED の動作を変更します。 Read Committed Snapshot Isolation (RCSI) が有効になっている場合、各読み取り操作では、その ステートメントの開始時に存在していたデータのスナップショットが表示されます。 書き手は変更した行に対して引き続きロックをかけ続けますが、読み手が後ろでブロックされることはありません。 重要な詳細: RCSI は Azure SQL Database で既定で有効になっているため、コードを変更せずにこの動作をすぐに使用できます。
SNAPSHOT 分離 によって、このソリューションはさらに進化します。 各読み取りでは、ステートメントごとのスナップショットではなく、 トランザクション全体の開始時に存在していたデータが表示されます。 アプリケーションで 1 つのトランザクション内で複数のクエリを実行し、それらのすべてに対して一貫した結果を得る必要がある場合、SNAPSHOT 分離によってその保証が提供されます。 これを使用するには、データベースで ALLOW_SNAPSHOT_ISOLATION を有効にし、セッションで分離レベルを明示的に設定する必要があります。 注意すべき点として、2 つのトランザクションが同じ行を変更しようとする場合、SNAPSHOT 分離では、更新の競合が生じるため、片方がもう片方を自動的に上書きすることはできません。
次の表は、各分離レベルの動作をまとめたものです。
| 分離レベル | ダーティ リード | 反復不可能な読み取り | ファントム読み取り | リーダーとライターの間のブロック |
|---|---|---|---|---|
| リード アンコミット (READ UNCOMMITTED) | イエス | イエス | イエス | いいえ |
| リードコミットメント (READ COMMITTED) | いいえ | イエス | イエス | イエス |
| 反復可能な読み取り | いいえ | いいえ | イエス | イエス |
| SERIALIZABLE | いいえ | いいえ | いいえ | はい (範囲ロック) |
| コミットされたスナップショットの読み取り | いいえ | イエス | イエス | いいえ |
| SNAPSHOT | いいえ | いいえ | いいえ | いいえ (更新の競合が発生する可能性があります) |
最適化されたロックを使用してブロックを減らす
RCSI を使用すると、読み取り/書き込みブロックが不要です。 では、書き込み時のブロッキングについてはどうでしょうか。 従来のロックでは、トランザクションがコミットされるまで、変更されたすべての行の行ロックとページ ロックが保持されます。 書き込みコンカレンシーが高い場合、保持されているロックは急速に蓄積されます。
Azure SQL Database では、 最適化されたロックに関するこの問題に対処しています。これは常に有効になっており、RCSI と共に機能します。 次の 2 つのメカニズムを使用します。
- トランザクション ID (TID) ロック: 変更されたすべての行に対して個々のキーまたは行ロックを保持する代わりに、エンジンはトランザクション ID (TID) に対して 1 つの排他的ロックを受け取ります。 同じ行にアクセスする必要がある他のトランザクションは、TID の共有ロックを取得し、変更トランザクションが完了するまで待機します。 その結果、メモリに保持されるロックがはるかに少なくなり、ロックのエスカレーションの可能性が大幅に低下します。
- 修飾後のロック: 行が変更される前に、エンジンはロックを取得せずに最新のコミット済みバージョンを読み取り、行がクエリ述語と一致するかどうかを確認します。 実際に条件に合う行だけがロックされ、その排他ロックは、トランザクションの最後ではなく、行の更新が完了した時点でただちに解放されます。 修飾後ロック (LAQ) では、RCSI を有効にする必要があります。
実用的な効果は重要です。 変更のためのページ ロックと行ロックは、トランザクションがコミットされるまで保持されるのではなく、各行が更新されるとすぐに解放されます。 異なる行に触れる同時書き込みでは、ピーク時の負荷の下でも、互いにブロックされることはほとんどありません。
適切な分離レベルを選択する
6 つの分離レベルを使用して、どのように決定すればよいですか? 既定値から始めます。 ほとんどの Azure SQL Database ワークロードでは、RCSI と最適化されたロックの組み合わせによって、一貫性とパフォーマンスの最適なバランスが既に提供されています。 閲覧者はライターをブロックしません。 ライターはリーダーをブロックしません。 ロックされたメモリは低いまま維持されます。 何も変更する必要はありません。
アプリケーションが 1 つのトランザクション内で複数のクエリを実行し、それらのすべてに一貫した結果が必要な場合は、SNAPSHOT の分離にステップ アップします。 口座残高を照会し、合計する財務レポートを考えてみましょう。同じポイントインタイム データを表示するには、両方のクエリが必要です。
ファントム読み取りによって、在庫の二重予約や調整ミスなど、実際のビジネスでのエラーが発生するシナリオに対して、SERIALIZABLE を予約します。 スループットが低下し、デッドロックの可能性が高くなることを受け入れます。
Important
選択したレベルに関係なく、トランザクションはできるだけ短くします。 実行時間の長いトランザクションは、長時間ロックを保持し、ブロックを増やし、バージョン ストア領域を消費します。 ユーザー入力または外部 API 呼び出しの待機中に開いたままのトランザクションは、コンカレンシーを停止させる可能性があります。
重要なポイント
トランザクション分離レベルは、データの整合性とコンカレンシーのトレードオフを制御します。 分離を高くすると、副作用が増えるのを防ぐことができますが、ブロックが増加します。 Azure SQL Database では RCSI が既定で有効になっています。これにより、共有ロックの代わりに行のバージョン管理を使用して、読み取り/書き込みブロックが不要になります。 最適化されたロックにより、各行が更新されるとすぐに行ロックとページ ロックが解放されるため、ブロックがさらに軽減されます。 ほとんどのワークロードでは、RCSI と最適化されたロックの既定の組み合わせが最適なバランスを提供します。 ファントム読み取りによってビジネス ロジック エラーが発生するシナリオに対して SERIALIZABLE を予約します。