PolarDBPostgreSQL版有哪些事务隔离级别 - PolarDB - Alibaba Cloud ドキュメントセンター

SQL標準は、トランザクション分離の4つのレベルを定義する。最も厳密なのはSerializableです。これは、Serializableトランザクションのセットの同時実行が、ある順序で一度に1つずつ実行するのと同じ効果をもたらすことが保証されるという段落の標準によって定義されています。他の3つのレベルは、各レベルで発生してはならない同時トランザクション間の相互作用から生じる現象に関して定義される。規格は、Serializableの定義により、これらの現象はそのレベルでは不可能であると述べています。 (これは驚くことではありません。トランザクションの効果が一度に1つずつ実行されたことと一致している必要がある場合、相互作用によって引き起こされる現象をどのように確認できますか?)

さまざまなレベルで禁止されている現象は次のとおりです。

dirty read: トランザクションは、同時コミットされていないトランザクションによって書き込まれたデータを読み取ります。
非反復読み取り: トランザクションは、以前に読み取ったデータを再読み取りし、データが別のトランザクション (最初の読み取り以降にコミットされた) によって変更されたことを発見する。
phantom read: トランザクションは、検索条件を満たす行のセットを返すクエリを再実行し、条件を満たす行のセットが別の最近コミットされたトランザクションのために変更されたことを発見します。
シリアル化異常: トランザクションのグループを正常にコミットした結果は、それらのトランザクションを1つずつ実行するすべての順序と一致しません。

次の表では、SQL標準とPolarDB PostgreSQL実装のトランザクション分離レベルについて説明します。

分離レベル	汚れた読み取り	非反復読み取り	ファントム読み取り	シリアル化異常
コミットされていない読み取り	許可されているが、PGではない	可能	可能	可能
コミット済みの読み取り	不可能	可能	可能	可能
繰り返し読み取り	不可能	不可能	許可されているが、PGではない	可能
シリアル化可能	不可能	不可能	不可能	不可能

PolarDB PostgreSQLでは、4つの標準トランザクション分離レベルのいずれかを要求できますが、内部では3つの異なる分離レベルのみが実装されます。つまり、PostgreSQLのRead UncommittedモードはRead Committedのように動作します。これは、標準の分離レベルをPostgreSQLのマルチバージョン同時実行制御アーキテクチャにマップする唯一の賢明な方法だからです。

この表はまた、PostgreSQLのRepeatable Read実装ではファントム読み取りが許可されていないことを示しています。これは、SQL標準では特定の分離レベルで発生してはならない異常を指定しているため、SQL標準では受け入れられます。より高い保証は受け入れられます。使用可能な分離レベルの動作については、次のサブセクションで詳しく説明します。

トランザクションのトランザクション分離レベルを設定するには、set transactionコマンドを使用します。

重要

一部のPostgreSQLデータ型と関数には、トランザクションの動作に関する特別なルールがあります。特に、シーケンス (したがって、シリアルを使用して宣言された列のカウンタ) に対して行われた変更は、他のすべてのトランザクションにすぐに表示され、変更を行ったトランザクションがアボートしてもロールバックされません。

コミット分離レベルの読み取り

Read Committedはデフォルトの分離レベルinPostgreSQLです。トランザクションがこの分離レベルを使用する場合、SELECTクエリ (FOR UPDATE/SHARE句なし) は、クエリが開始される前にコミットされたデータのみを参照します。実際には、SELECTクエリは、クエリの実行が開始された時点でデータベースのスナップショットを確認します。ただし、SELECTは、まだコミットされていない場合でも、独自のトランザクション内で実行された以前の更新の影響を確認します。また、2つの連続するSELECTコマンドは、1つのトランザクション内にある場合でも、最初のSELECTの開始後と2番目のSELECTの開始前に他のトランザクションが変更をコミットすると、異なるデータを表示できます。

UPDATE、DELETE、SELECT FOR UPDATE、およびSELECT FOR SHAREコマンドは、ターゲット行の検索に関してSELECTと同じように動作します。ただし、このようなターゲット行は、見つかった時点までに別の同時トランザクションによってすでに更新 (または削除またはロック) されている可能性があります。この場合、更新される予定のトランザクションは、最初の更新トランザクションがコミットまたはロールバックするのを待ちます (まだ進行中の場合) 。第1のアップデータがロールバックすると、その効果は否定され、第2のアップデータは最初に見つかった行の更新を進めることができる。第1のアップデータがコミットした場合、第2のアップデータは、第1のアップデータがそれを削除した場合、その行を無視する。コマンドの検索条件 (WHERE句) を再評価して、更新された行のバージョンが検索条件と一致するかどうかを確認します。そうである場合、第2のアップデータは、行の更新されたバージョンを使用してその動作を進める。 SELECT FOR UPDATEおよびSELECT FOR SHAREの場合、これはロックされてクライアントに返される行の更新バージョンを意味します。

ON CONFLICT DO UPDATE句を持つINSERTも同様に動作します。読み取りコミットモードでは、挿入のために提案された各行は挿入または更新されます。無関係なエラーがない限り、これら2つの結果のいずれかが保証されます。 INSERTに効果がまだ表示されていない別のトランザクションで競合が発生した場合、UPDATE句は、その行のバージョンが従来コマンドに表示されていない場合でも、その行に影響を与えます。

ON CONFLICT DO NOTHING句を持つINSERTでは、INSERTスナップショットに影響が表示されない別のトランザクションの結果により、行の挿入が進行しない場合があります。ここでも、これは読み取りコミットモードの場合のみである。

上記のルールのため、更新コマンドが一貫性のないスナップショットを確認する可能性があります。更新しようとしているのと同じ行に対する同時更新コマンドの影響は確認できますが、データベース内の他の行に対するそれらのコマンドの影響は確認できません。この動作により、読み取りコミットモードは複雑な検索条件を含むコマンドには適していません。たとえば、次のようなトランザクションで銀行残高を更新することを検討してください。

BEGIN;
    UPDATEアカウントSET balance = balance + 100.00 WHERE acctnum = 12345;
    UPDATEアカウントSET balance = balance - 100.00 WHERE acctnum = 7534;
    コミット;

2つのそのようなトランザクションが同時にアカウント12345の残高を変更しようとする場合、2番目のトランザクションはアカウントの行の更新されたバージョンから開始することが望ましいことは明らかです。各コマンドは所定の行のみに影響を及ぼすので、行の更新されたバージョンをそれに表示させても面倒な不整合は生じない。

より複雑な使用は、読み取りコミットモードで望ましくない結果を生み出す可能性があります。たとえば、別のコマンドによって制限基準から追加および削除されているデータに対して動作するDELETEコマンドを考えてみましょう。たとえば、Webサイトが9と10に等しいwebsite.hitsを持つ2行のテーブルであると仮定します。

BEGIN;
    更新ウェブサイトSET hits = hits + 1;
    -別のセッションから実行します。ウェブサイトWHEREヒット=10から削除します。
    コミット;

UPDATEの前後にwebsite.hits = 10行があっても、DELETEは効果がありません。これは、更新前の行値9がスキップされ、updateが完了してDELETEがロックを取得すると、新しい行値が10されずに11され、基準に一致しなくなります。

Read Committedモードでは、その時点までにコミットされたすべてのトランザクションを含む新しいスナップショットで各コマンドが開始されるため、同じトランザクション内の後続のコマンドでは、いずれの場合も、コミットされた同時トランザクションの影響が表示されます。上記の問題点は、1つのコマンドがデータベースの完全に一貫したビューを表示するかどうかです。

Read Committedモードによって提供される部分的なトランザクション分離は、多くのアプリケーションに適しており、このモードは、高速で使用が簡単ですが、すべての場合に十分ではありません。複雑なクエリや更新を行うアプリケーションでは、読み取りコミットモードよりも厳密に一貫したデータベースのビューが必要になる場合があります。

繰り返し読み取り分離レベル

Repeatable Read分離レベルでは、トランザクションが開始される前にコミットされたデータのみが表示されます。コミットされていないデータや、同時トランザクションによるトランザクション実行中にコミットされた変更は表示されません。 (ただし、クエリでは、以前の更新がまだコミットされていない場合でも、独自のトランザクション内で実行された結果が表示されます。) これは、この分離レベルのSQL標準で必要とされるよりも強力な保証であり、シリアル化の異常を除いて上の表に記載されているすべての現象を防ぎます。前述のように、これは、各分離レベルが提供しなければならない最小保護のみを記述する規格によって特に許可されている。

このレベルは、反復可能な読み取りトランザクション内のクエリが、トランザクション内の現在のステートメントの開始時ではなく、トランザクション内の最初の非トランザクション制御ステートメントの開始時にスナップショットを見るという点で、読み取りコミットとは異なります。したがって、単一のトランザクション内の連続するSELECTコマンドは、同じデータを見る。すなわち、それらは、それら自身のトランザクションが開始した後にコミットされた他のトランザクションによって行われた変更を見ない。

このレベルを使用するアプリケーションは、シリアル化の失敗によりトランザクションを再試行する準備が必要です。

UPDATE、DELETE、SELECT FOR UPDATE、およびSELECT FOR SHAREコマンドは、ターゲット行の検索に関してSELECTと同じように動作します。ただし、このようなターゲット行は、見つかった時点までに別の同時トランザクションによってすでに更新 (または削除またはロック) されている可能性があります。この場合、繰り返し可能な読み取りトランザクションは、最初の更新トランザクションがコミットまたはロールバックするのを待ちます (まだ進行中の場合) 。第1のアップデータがロールバックする場合、その効果は否定され、反復可能な読み出しトランザクションは、最初に見つかった行の更新を進めることができる。ただし、最初のアップデータがコミットした場合 (ロックされただけでなく、実際に行を更新または削除した場合) 、繰り返し可能な読み取りトランザクションはメッセージとともにロールバックされます。

エラー: 同時更新のためにアクセスをシリアル化できませんでした

繰り返し可能な読み取りトランザクションは、繰り返し可能な読み取りトランザクションの開始後に他のトランザクションによって変更された行を変更またはロックできないためです。

アプリケーションがこのエラーメッセージを受信すると、現在のトランザクションを中止し、トランザクション全体を最初から再試行する必要があります。 2回目は、トランザクションは以前にコミットされた変更をデータベースの最初のビューの一部として見るので、新しいトランザクションの更新の開始点として新しいバージョンの行を使用する際に論理的な競合はありません。

更新トランザクションのみを再試行する必要がある場合があります。読み取り専用トランザクションでは、シリアル化の競合は発生しません。

Repeatable Readモードは、各トランザクションがデータベースの完全に安定したビューを見ることを厳密に保証します。しかしながら、このビューは、必ずしも、同じレベルの同時トランザクションのいくつかの連続的な (一度に1つの) 実行と常に一致するとは限らない。例えば、このレベルの読み取り専用トランザクションでさえ、バッチが完了したことを示すために更新された制御レコードを見ることができるが、制御レコードの以前のリビジョンを読み取るので、バッチの論理的な一部である詳細レコードの1つは見られない。この分離レベルで実行されるトランザクションによってビジネスルールを適用しようとする試みは、競合するトランザクションをブロックする明示的なロックを慎重に使用しないと正しく機能しない可能性があります。

Repeatable Read分離レベルは、学術データベース文献やその他のデータベース製品でSnapshot分離として知られている技術を使用して実装されています。並行性を低減する従来のロック技法を使用するシステムと比較すると、挙動および性能の差が観察され得る。他のいくつかのシステムは、異なる動作を有する別個の分離レベルとして、繰り返し可能な読み取り及びスナップショット分離を提供し得る。 2つの手法を区別する許容される現象は、SQL標準が開発されるまでデータベース研究者によって形式化されておらず、このマニュアルの範囲外です。

シリアル化可能な分離レベル

Serializable分離レベルは、最も厳格なトランザクション分離を提供します。このレベルは、すべてのコミットされたトランザクションのシリアルトランザクション実行をエミュレートします。ただし、Repeatable Readレベルと同様に、このレベルを使用するアプリケーションは、シリアル化の失敗によりトランザクションを再試行する準備が必要です。実際、この分離レベルは、シリアル化可能なトランザクションの同時セットの実行を、それらのトランザクションのすべての可能なシリアル (一度に1つ) 実行と矛盾するように動作させる可能性のある条件を監視することを除いて、Repeatable Readとまったく同じように機能します。この監視は、繰り返し可能な読み取りに存在するものを超えるブロッキングを導入しませんが、監視にはいくらかのオーバーヘッドがあり、シリアル化異常を引き起こす可能性のある状態の検出はシリアル化失敗をトリガーします。

例として、最初に次のものを含むテーブルmytabを考えます。

クラス | 値
    -------+-------
         1 | 10
         1 | 20
         2 | 100
         2 | 200

シリアル化可能なトランザクションAが計算するとします。

SELECT SUM (値) FROM mytab WHEREクラス=1;

結果 (30) をclass= 2の新しい行にvalueとして挿入します。同時に、シリアル化可能なトランザクションBは次のように計算します。

SELECT SUM (値) FROM mytab WHEREクラス=2;

結果300を取得し、class = 1の新しい行に挿入します。次に、両方のトランザクションがコミットしようとします。いずれかのトランザクションがRepeatable Read分離レベルで実行されている場合、両方ともコミットが許可されます。しかし、結果と一致する実行の連続順序がないため、シリアル化可能トランザクションを使用すると、一方のトランザクションがコミットでき、もう一方のトランザクションがこのメッセージでロールバックされます。

エラー: トランザクション間の読み取り /書き込みの依存関係により、アクセスをシリアル化できませんでした

これは、AがBの前に実行された場合、Bが300ではなく330和を計算し、同様に他の順序がaによって計算された異なる和をもたらすからである。

異常を防ぐためにシリアル化可能なトランザクションに依存する場合、永続的なユーザーテーブルから読み取られたデータは、読み取られたトランザクションが正常にコミットされるまで有効と見なされないことが重要です。これは、読み取り専用トランザクションにも当てはまります。ただし、延期可能な読み取り専用トランザクション内で読み取られたデータは、読み取られるとすぐに有効であることがわかっています。他のすべての場合、アプリケーションは後でアボートされたトランザクション中に読み取られた結果に依存してはなりません。

真のシリアル化可能性を保証するために、PolarDB PostgreSQLは述語ロックを使用します。つまり、ロックを保持して、書き込みが最初に実行された場合に、同時トランザクションからの以前の読み取りの結果に影響があるかどうかを判断できます。 PolarDB PostgreSQLでは、これらのロックはブロッキングを引き起こさないため、デッドロックを引き起こす役割を果たすことはできません。これらは、特定の組み合わせでシリアル化異常につながる可能性のある同時シリアル化可能トランザクション間の依存関係を識別してフラグを立てるために使用されます。対照的に、データの一貫性を確保したいRead CommittedまたはRepeatable Readトランザクションは、テーブル全体のロックを解除する必要があります。これにより、そのテーブルを使用しようとする他のユーザーがブロックされる可能性があります。または、SELECT FOR UPDATEまたはSELECT FOR SHAREを使用できます。

PostgreSQLの述語ロックは、他のほとんどのデータベースシステムと同様に、トランザクションによって実際にアクセスされるデータに基づいています。これらは、モードがSIReadLockでpg_locksシステムビューに表示されます。クエリの実行中に取得される特定のロックは、クエリによって使用されるプランに依存し、ロックを追跡するために使用されるメモリの枯渇を防止するために、トランザクションの進行中に、複数のよりきめの細かいロック (たとえば、タプルロック) をより少ないきめのロック (たとえば、ページロック) に組み合わせることができる。 READ ONLYトランザクションは、シリアル化異常につながる可能性のある競合がまだ発生し得ないことを検出した場合、完了前にそのSIReadロックを解放することができる。実際、READ ONLYトランザクションは、多くの場合、起動時にその事実を確立し、述語ロックを取ることを回避することができる。 SERIALIZABLE READ ONLY DEFERRABLEトランザクションを明示的にリクエストした場合、この事実を確立できるまでブロックされます。 (これは、シリアル化可能なトランザクションがブロックされ、繰り返し可能な読み取りトランザクションがブロックされない唯一のケースです。) 一方、SIReadロックは、重複する読み出し書き込みトランザクションが完了するまで、トランザクションコミットを過ぎて保持される必要があることが多い。

シリアル化可能トランザクションを一貫して使用すると、開発を簡素化できます。正常にコミットされた同時シリアル化可能なトランザクションのセットが、1つずつ実行されたのと同じ効果を持つという保証は、記述されたように、単一のトランザクションが単独で実行されたときに正しいことを実行することを実証できれば、他のトランザクションが何をするかについての情報がなくても、シリアル化可能なトランザクションの任意の組み合わせで正しいことを実行するという確信を持つことができます。または正常にコミットされません。この手法を使用する環境には、シリアル化の失敗を処理する一般化された方法があることが重要です (常に「40001」のSQLSTATE値で返されます) 。これは、どのトランザクションが読み取り /書き込み依存関係に寄与し、シリアル化の異常を防ぐためにロールバックする必要があるかを正確に予測することが非常に難しいためです。シリアル化の失敗で終了したトランザクションの再起動と同様に、読み取り /書き込み依存関係の監視にはコストがかかりますが、明示的なロックとSELECT FOR UPDATEまたはSELECT FOR SHAREの使用に伴うコストとブロッキングとのバランスが取れています。

PolarDB PostgreSQLのシリアル化可能なトランザクション分離レベルでは、同じ効果を生成する実行の連続順序があることを証明できる場合にのみ同時トランザクションをコミットできますが、真の連続実行では発生しないエラーが発生するのを常に防ぐとは限りません。特に、キーを挿入しようとする前にキーが存在しないことを明示的にチェックした後でも、重複するシリアル化可能トランザクションとの競合によって引き起こされる一意の制約違反を確認できます。これは、競合する可能性のあるキーを挿入するすべてのシリアル化可能トランザクションが最初にできるかどうかを明示的に確認することで回避できます。たとえば、ユーザーに新しいキーを要求し、最初にキーを選択しようとすることでキーがまだ存在していないことを確認したり、既存の最大キーを選択してキーを追加して新しいキーを生成したりするアプリケーションを想像してみてください。いくつかの直列化可能なトランザクションがこのプロトコルに従わずに新しいキーを直接挿入すると、同時トランザクションの連続実行では発生できない場合でも、一意の制約違反が報告される可能性があります。

同時実行制御のシリアル化可能トランザクションに依存する場合のパフォーマンスを最適化するには、次の問題を考慮する必要があります。

可能な場合、トランザクションをREAD ONLYとして宣言します。
必要に応じて接続プールを使用して、アクティブな接続の数を制御します。これは常に重要なパフォーマンスの考慮事項ですが、シリアル化可能トランザクションを使用するビジーシステムでは特に重要です。
整合性の目的で必要以上に1つのトランザクションに投入しないでください。
「トランザクションでアイドル」にぶら下がっている接続を必要以上に長くしないでください。設定パラメータidle_in_transaction_session_timeoutを使用して、長引くセッションを自動的に切断できます。
シリアル化可能なトランザクションによって自動的に提供される保護のために不要になった明示的なロック、SELECT FOR UPDATE、SELECT FOR SHAREを削除します。
述語ロックテーブルのメモリが不足しているため、システムが複数のページレベルの述語ロックを単一の関係レベルの述語ロックに結合することを余儀なくされると、シリアル化失敗の割合が増加する可能性があります。これを回避するには、max_pred_locks_per_transaction、max_pred_locks_per_relation、および /またはmax_pred_locks_per_pageを増やします。
シーケンシャルスキャンは、常に関係レベルの述語ロックを必要とします。これにより、シリアル化失敗の割合が増加する可能性があります。 random_page_costを減らし、および /またはcpu_tup_costを増やすことによって、インデックススキャンの使用を奨励することが役立つ場合があります。トランザクションのロールバックと再起動の減少と、クエリの実行時間の全体的な変化とを比較検討してください。

シリアル化可能分離レベルは、シリアル化異常のチェックを追加することによってスナップショット分離に基づいて構築されるシリアル化可能スナップショット分離として学術データベース文献で知られている手法を使用して実装されます。従来のロック技術を使用する他のシステムと比較した場合、挙動および性能におけるいくつかの違いが観察され得る。