ヘッダーをスキップ
Oracle Databaseアドバンスト・アプリケーション開発者ガイド
11gリリース1(11.1)
E05687-02
  目次
目次
索引
索引

戻る
戻る
 
次へ
次へ
 

2 アプリケーション開発者用のSQL処理

この章では、Oracle DatabaseでのSQL文の処理方法について、アプリケーション開発者が知っておく必要がある内容について説明します。この章を読む前に、『Oracle Database概要』でSQL処理の基本情報について参照してください。

内容は次のとおりです。

SQL文の処理の説明

ここでは、SQL文の実行時に処理の各ステージで何が行われるかを例を示して説明します。例では特にDML文の処理について説明しますが、内容はその他のSQL文にも適用できます。その他の種類のSQL文を実行する方法との違いについては、「その他の種類のSQL文の処理」を参照してください。

ある部門の全従業員の昇給を行うためにPro*Cプログラムを使用しているとします。使用しているプログラムはOracle Databaseに接続しており、操作を行うユーザーはemployees表を更新するための正しいスキーマに接続しています。プログラムには次のSQL文を埋め込むことができます。

EXEC SQL UPDATE employees SET salary = 1.10 * salary
    WHERE department_id = :department_id;

department_idは、部門番号の値を含むプログラム変数です。このSQL文が実行されるときは、アプリケーション・プログラムで提供されるdepartment_idの値が使用されます。

SQL文の処理のステージ

次に各種類の文の処理に必要なステージを示します。このプロセスのフローチャートは、『Oracle Database概要』を参照してください。

  1. カーソルをオープンまたは作成します。

    プログラム・インタフェース・コールによりカーソルがオープンまたは作成されます。カーソルの作成はSQL文には依存しません。これは、SQL文の準備のために作成されます。ほとんどのアプリケーションではカーソルの作成は自動的に行われます。ただし、プリコンパイラ・プログラムでは、カーソル作成を暗黙的に行うか、または明示的に宣言することができます。

  2. 文を解析します。

    解析中にSQL文がユーザー・プロセスからOracle Databaseに渡され、SQL文の解析済表現が共有SQL領域にロードされます。文の処理のこのステージでは多くのエラーを検出できます。


    関連項目:

    解析の詳細は、『Oracle Database概要』を参照してください。

  3. 文が問合せかどうかを判別します。

    このステージでは、SQL文の最初に問合せがあるかどうかを判別します。


    関連項目:

    • 問合せの詳細は、『Oracle Database概要』を参照してください。

    • 「共有SQL領域」


  4. 文が問合せの場合、結果を記述します。

    このステージが必要になるのは、問合せ結果の性質が不明な場合のみです。たとえば、ユーザーが対話的に問合せを入力する場合などです。この場合は、記述ステージで問合せ結果の性質(データ型、長さおよび名前)が決まります。

  5. 文が問合せの場合、出力を定義します。

    このステージでは、フェッチされる各値を受け取るために定義する変数の場所、サイズおよびデータ型を指定します。このような変数は定義変数と呼ばれます。必要な場合にはOracle Databaseによってデータ型の変換が行われます。


    関連項目:

    DEFINEステージの詳細は、『Oracle Database概要』を参照してください。

  6. 変数がある場合はバインドします。

    この時点で、Oracle DatabaseはSQL文の意味を認識していますが、文を実行するために十分な情報は得ていません。Oracle Databaseでは、文に指定されているすべての変数の値(たとえばdepartment_idの値)が必要です。これらの値を獲得するプロセスは変数のバインドと呼ばれます。

    値がある場所(メモリー・アドレス)をプログラムで指定する必要があります。アプリケーションのエンド・ユーザーは、Oracle Databaseユーティリティから新しい値の入力を求められるだけであるため、バインド変数を指定していると認識していない場合もあります。

    場所(参照によるバインド)が指定されるため、再実行の前に変数を再バインドする必要はありません。値を変更することもできます。Oracle Databaseは実行のたびにメモリー・アドレスを使用して値を調べます。

    Oracle Databaseでデータ型変換を実行する必要がある場合は、デフォルトでまたは暗黙的に指定する場合を除き、各値のデータ型と長さも指定する必要があります。


    関連項目:

    値のデータ型と長さの指定の詳細は、次の資料を参照してください。
    • 『Oracle Call Interfaceプログラマーズ・ガイド』

    • 『Pro*C/C++プログラマーズ・ガイド』


  7. (オプション)文をパラレル化します。

    Oracle Databaseでは、問合せと一部のDDL操作(索引作成、副問合せによる表作成、パーティションに対する操作など)をパラレル化することができます。パラレル化では複数のサーバー・プロセスでSQL文の処理を実行するため、処理が早く完了します。


    関連項目:

    パラレル実行の概要は、『Oracle Database概要』を参照してください。

  8. 文を実行します。

    この時点で、Oracle Databaseは必要なすべての情報とリソースを得ており、文が実行されます。文が問合せまたはINSERT文の場合、データは変更されないため行をロックする必要はありません。ただし、文がUPDATE文またはDELETE文の場合は、影響を受けるすべての行が、そのトランザクションの次のCOMMITROLLBACKまたはSAVEPOINTまでロックされます。これによりデータの整合性が確保されます。

    一部の文では実行回数を指定できます。これは配列処理と呼ばれます。実行回数をnと指定すると、バインドおよび定義の場所が、サイズnの配列の先頭であるとみなされます。

  9. 文が問合せの場合、行をフェッチします。

    フェッチ・ステージでは、行の選択と順序付けが行われます(問合せで指定されている場合)。後続の各フェッチにより結果の行が1行ずつ取得され、最後の行がフェッチされるまで続きます。

  10. カーソルをクローズします。

    SQL文処理の最後のステージでは、カーソルがクローズされます。

共有SQL領域

Oracle Databaseは、アプリケーションが類似したSQL文をデータベースに送信すると自動的に認識します。その文の最初の処理時に使用されたSQL領域が共有されます。つまり、後で出現する同じ文の処理に使用されます。このため、一意の文に対して存在する共有SQL領域は1つのみです。共有SQL領域は共有メモリー領域であるため、すべてのOracle Databaseプロセスが共有SQL領域を使用できます。SQL領域の共有により、データベース・サーバーでのメモリー使用量が減るため、システム・スループットが向上します。

Oracle Databaseでは、文が類似しているか同一かを評価する際に、ユーザーやアプリケーションによって直接発行されたSQL文か、DDL文によって内部的に発行された再帰的SQL文かが考慮されます。


関連項目:

共有SQL領域の詳細は、次の資料を参照してください。
  • 『Oracle Databaseアドバンスト・アプリケーション開発者ガイド』

  • 『Oracle Databaseパフォーマンス・チューニング・ガイド』


その他の種類のSQL文の処理

ここでは、DDL文、トランザクション制御文およびその他のSQL文が、「SQL文の処理の説明」で説明したプロセスとどのように異なるかを示します。

DDL文の処理

DDL文の実行は、DML文や問合せの実行とは異なります。DDL文が正常終了するとデータ・ディクショナリへの書込みアクセスが必要になるためです。これらの文の場合、実際には解析、データ・ディクショナリ検索および実行が解析(ステージ2)に含まれます。

トランザクション制御の処理

一般的に、1トランザクションにまとめるアクションの種類を考慮するのは、Oracle Databaseに対するプログラミング・インタフェースを使用するアプリケーション設計者のみです。トランザクションは、処理が論理的な単位で完了し、データの一貫性が保たれるように定義する必要があります。1つのトランザクションは、1つの論理的な作業単位のために必要なすべての要素で構成され、過不足は認められません。

  • 参照されるすべての表中のデータは、トランザクションが開始する前および終了した後で、一貫した状態であることが必要です。

  • トランザクションは、データに対して一貫性のある1つの変更を行うSQL文のみで構成されることが必要です。

たとえば、2つの口座間での送金(トランザクションすなわち論理的作業単位)は、一方の口座への借方(1つのSQL文)ともう一方の口座への貸方(1つのSQL文)を含む必要があります。2つのアクションで1つの作業単位となり、両方が成立するか両方が不成立かのいずれかである必要があります。つまり、借方を伴わない貸方をコミットしないようにしてください。一方の口座への新規預金など、関連のないアクションは、送金トランザクションに含めないでください。

その他の処理の種類

トランザクション管理、セッション管理およびシステム管理のSQL文は、解析ステージと実行ステージを使用して処理されます。これらの文を再実行する場合は、もう一度実行を行います。

操作のトランザクションへのグループ化

内容は次のとおりです。

トランザクションへの操作のグループ化方法の決定

一般に、トランザクションへの操作のグループ化方法の決定は、Oracle Databaseのプログラム・インタフェースを使用するアプリケーション設計者が考慮します。トランザクションのグループ化方法を決定する際は、次の規則に従います。

  • 論理単位ごとに作業が完成し、データの一貫性が保たれるように、トランザクションを定義します。

  • すべての参照表の中のデータは、トランザクションが開始する前および終了した後で、一貫した状態であることを確認します。

  • 各トランザクションは、データに対する一貫した変更を1つ含むSQL文またはPL/SQLブロックのみで構成されていることを確認します。

たとえば、口座間で預金を移動できるWebアプリケーションを作成すると仮定します。このトランザクションには、一方の口座の借方への記帳(1つのSQL文で実行)ともう一方の口座の貸方への記帳(もう1つのSQL文で実行)が含まれる必要があります。2つの文で1つの作業単位となり、両方が成立するか両方が不成立かのいずれかである必要があります。つまり、借方を伴わない貸方はコミットしないようにしてください。一方の口座への新規預金など、関連のないアクションは、同じトランザクションに含めないでください。

トランザクションのパフォーマンスの改善

アプリケーション開発者は、パフォーマンスが改善可能か検討する必要があります。アプリケーションの設計および作成では、次のパフォーマンス要件を考慮する必要があります。

  • USE ROLLBACK SEGMENT句を指定したSET TRANSACTION文を使用して、トランザクションをロールバック・セグメントに明示的に割り当てます。これによって、システムのパフォーマンスを低下させる可能性のある追加エクステントの動的割当ての必要がなくなります。この句は、ロールバック・セグメントをUNDOに使用する場合にのみ有効です。自動UNDO管理を使用する場合、Oracle Databaseではこの句は無視されます。

  • 共有SQL領域を利用できるように、SQL文の発行基準を確立します。Oracle Databaseが同一のSQL文を認識し、SQL文がメモリー領域を共有できるようにします。これによって、データベース・サーバー上のメモリー使用量が減少し、システム・スループットが向上します。

  • 統計を収集します。Oracle Databaseは統計を使用して、SQL文を最適化するコストベースの方法を実装することができます。オプティマイザには、必要に応じてヒントを追加できます。

    ほとんどの統計の収集にはDBMS_STATSパッケージを使用します。このパッケージでは、パラレルでの統計の収集、パーティション化されたオブジェクトのグローバル統計の収集、その他の方法での統計収集の微調整を行うことができます。このパッケージの詳細は、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください。

    コストベースのオプティマイザに関係しない統計収集(空きリスト・ブロックに関する情報収集など)では、SQL文ANALYZEを使用します。この文の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。

  • トランザクションを開始する前にDBMS_APPLICATION_INFO.SET_ACTIONプロシージャを起動し、トランザクションを登録して名前を付け、アプリケーション全体にわたるパフォーマンス測定で使用できるようにします。後でシステムをチューニングする際に、どのトランザクションが最もシステム・リソースを必要とするかがわかるように、トランザクションで実行するアクティビティのタイプを指定します。

  • 「SQL文からのストアドPL/SQLファンクションの起動」に説明するとおり、ユーザーが作成したPL/SQLファンクションをSQL式に組み込んで、ユーザーの生産性および問合せ効率を向上させます。

  • PL/SQLアプリケーションを作成するときに、明示的カーソルを作成します。

  • MAX_OPEN_CURSORSを使用してカーソル数を増加させ、プリコンパイラ・プログラムで解析頻度を削減し、パフォーマンスを改善します。

  • ISOLATION LEVELSERIALIZABLEに設定したSET TRANSACTION文を使用して、ANSI/ISOシリアライズ可能トランザクションを生成します。


関連項目:


トランザクションのコミット

トランザクションをコミットするには、COMMIT文を使用します。次の2つの文は同等で、現行のトランザクションをコミットします。

COMMIT WORK;
COMMIT;

COMMIT文には、コミットされるトランザクションに関する情報を示すコメントを指定したCOMMENTパラメータを含めることができます。このオプションは、分散トランザクションをコミットするときに、トランザクションの起点に関する情報を含める場合に有効です。

COMMIT COMMENT 'Dallas/Accts_pay/Trans_type 10B';

コミットREDO動作の管理

トランザクションがデータベースを更新するとき、この更新に対応するREDOエントリが生成されます。トランザクションが完了するまで、このREDOはOracle Databaseによりメモリーにバッファリングされます。トランザクションがコミットされると、それに応じてログ・ライター・プロセス(LGWR)がトランザクション内のすべての変更の蓄積されたREDOをディスクに書き込みます。デフォルトでは、REDOは、コールがクライアントに戻される前に、Oracle Databaseによりディスクに書き込まれます。この動作のためにアプリケーションはREDOがディスクに永続的に書き込まれるのを待機する必要があり、コミットに待機時間が発生します。

高いトランザクション・スループットを必要とするアプリケーションを作成する場合を考えます。コミットでの待機時間を短くするためにコミットの永続性を放棄してもよい場合は、デフォルトのCOMMITオプションを変更して、データベースでデータがオンラインREDOログへ書き込まれるのをアプリケーションが待機しなくて済むようにできます。

Oracle Databaseでは、アプリケーションのニーズに応じてコミットREDOの処理を変更できます。コミット動作は次の場所で変更できます。

  • システムまたはセッション・レベルのCOMMIT_WRITE初期化パラメータ

  • COMMIT

COMMIT文のオプションを使用すると、初期化パラメータの現行の設定がオーバーライドされます。表2-1に、このどちらかの場所で設定可能なREDO永続オプションの説明を示します。


注意:

COMMITまたはCOMMIT_WRITENOWAITオプションを指定した場合、コミット・メッセージの受信後、REDOログ・レコードへの書込み前に障害が発生すると、トランザクションにその変更が永続的であると誤って示す可能性があります。

表2-1 COMMIT文およびCOMMIT_WRITE初期化パラメータのオプション

オプション 効果

WAIT(デフォルト)

コミットに対応するREDO情報がオンラインREDOログに永続的に書き込まれるまで、コミットが戻されないことを確認します。このCOMMIT文から成功として戻されたコミットをクライアントが受信した場合、そのトランザクションは永続的なメディアにコミットされています。

ログに正常に書き込まれた後に障害が発生すると、成功メッセージがクライアントに戻されない場合があります。その場合、クライアントはトランザクションがコミットされたかどうかを判断できません。

NOWAIT

コミットは、REDOログへの書込みが完了したかどうかにかかわらず、クライアントに戻されます。この動作はトランザクションのスループットを向上させます。

BATCH

REDO情報は、同時に実行している他のトランザクションとともにREDOログ・バッファに格納されます。十分なREDO情報が収集されると、REDOログのディスク書込みが開始されます。この動作はグループ・コミットと呼ばれます。複数のトランザクションのREDO情報が1回のI/O操作でログに書き込まれるためです。

IMMEDIATE(デフォルト)

LGWRがトランザクションのREDO情報をログに書き込みます。この操作オプションではディスクI/Oが発生するため、トランザクションのスループットが低下する場合があります。


次の例は、初期化パラメータ・ファイルでコミット動作をBATCHおよびNOWAITに設定する方法を示しています。

COMMIT_WRITE = BATCH, NOWAIT

コミット動作は、次の例のようにALTER SYSTEMを実行することにより、システム・レベルで変更できます。

ALTER SYSTEM SET COMMIT_WRITE = BATCH, NOWAIT

初期化パラメータの設定後、オプションを指定していないCOMMIT文は、パラメータのオプションの設定に準拠するようになります。また、次の例のように、COMMIT文で直接オプションを指定することにより、現行の初期化パラメータの設定をオーバーライドできます。

COMMIT WRITE BATCH NOWAIT

どちらの例でも、コミット時に即時にログ・ライターがREDOをオンラインREDOログへ書き込む必要がないこと、および、REDOがディスクへ書き込まれるのを待機しないことが、アプリケーションで指定されています。


注意:

分散トランザクションの場合、デフォルトのIMMEDIATEおよびWAITアクションは変更できません。

OCIを使用するアプリケーションの場合、アプリケーション内でOCITransCommitファンクションに次のフラグを設定することにより、REDO動作を変更できます。

  • OCI_TRANS_WRITEBATCH

  • OCI_TRANS_WRITENOWAIT

  • OCI_TRANS_WRITEIMMED

  • OCI_TRANS_WRITEWAIT


注意:

OCI_TRANS_WRITENOWAITの使用時には、トランザクションが警告なしに消失する可能性があります。トランザクションの消失は、強制終了、強制起動、インスタンス障害またはノード障害で警告なしに行われます。RACシステムでは非同期にコミットした変更は、他のインスタンスで変更をすぐに読み込めない場合があります。

NOWAITおよびBATCHオプションの指定では、問題が発生しやすい期間が短くなります(このような期間には、アプリケーションがコミット済として認識するトランザクションをOracle Databaseがロールバックできます)。アプリケーションは、次のような状況に対応できる必要があります。

  • データベース・ホストの障害。バッファには格納されていても、まだオンラインREDOログに書き込まれていないREDOが失われる可能性があります。

  • ファイルI/Oの問題で、ログ・ライターによるバッファ済REDOのディスクへの書込みが妨げられる場合。REDOログが多重化されていない場合、コミットが失われます。


関連項目:

  • COMMIT文の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。

  • OCITransCommitファンクションの詳細は『Oracle Call Interfaceプログラマーズ・ガイド』を参照してください。


トランザクションのロールバック

トランザクションの全体または一部を(セーブポイントまで)ロールバックするには、ROLLBACK文を使用します。たとえば、次の文はどちらも、現行のトランザクション全体をロールバックします。

ROLLBACK WORK;
ROLLBACK;

ROLLBACK文のWORKオプションには、何も機能はありません。

現行のトランザクション内に定義されたセーブポイントまでロールバックするには、ROLLBACK文のTOオプションを使用します。たとえば、次の文はいずれもPOINT1という名前のセーブポイントまで現行のトランザクションをロールバックします。

SAVEPOINT Point1;
...
ROLLBACK TO SAVEPOINT Point1;
ROLLBACK TO Point1;

トランザクションのセーブポイントの定義

トランザクション内にセーブポイントを定義するには、SAVEPOINT文を使用します。次の文は、現行のトランザクション内にADD_EMP1という名前のセーブポイントを作成します。

SAVEPOINT Add_emp1;

前のセーブポイントと同じ識別子で2番目のセーブポイントを作成すると、前のセーブポイントが消去されます。セーブポイントを作成した後は、そのセーブポイントまでロールバックできます。

セッション当たりのアクティブ・セーブポイントの数に制限はありません。アクティブ・セーブポイントとは、最後のコミットまたは最後のロールバック以降に指定されたセーブポイントのことです。

表2-2に、トランザクション内のCOMMIT文、SAVEPOINT文およびROLLBACK文の使用を示す一連のSQL文を示します。

表2-2 COMMIT、SAVEPOINTおよびROLLBACKの使用方法

SQL文 結果

SAVEPOINT a;

このトランザクションの最初のセーブポイント

DELETE...;

このトランザクションの最初のDML文

SAVEPOINT b;

このトランザクションの2番目のセーブポイント

INSERT INTO...;

このトランザクションの2番目のDML文

SAVEPOINT c;

このトランザクションの3番目のセーブポイント

UPDATE...;

このトランザクションの3番目のDML文

ROLLBACK TO c;

UPDATE文がロールバックされ、セーブポイントCは定義されたままになります。

ROLLBACK TO b;

INSERT文がロールバックされ、セーブポイントCは失われます。セーブポイントBは定義されたままです。

ROLLBACK TO c;

ORA-01086

INSERT INTO...;

このトランザクションの新しいDML文

COMMIT;

最初のDML文(DELETE文)および最後のDML文(2番目のINSERT文)によって行われたすべてのアクションがコミットされます。

トランザクションのその他すべての文(2番目および3番目の文)はCOMMITの前にロールバックされています。セーブポイントAは、すでにアクティブではありません。


読取り専用トランザクションでの反復可能読取りの保証

デフォルトでは、Oracle Databaseの一貫性モデルは、文レベルの読取り一貫性は保証しますが、トランザクション・レベルの読取り一貫性(反復可能読取り)は保証しません。トランザクション・レベルの読取り一貫性が必要で、トランザクションが更新を必要としない場合は、読取り専用トランザクションを指定できます。トランザクションを読取り専用に指定すると、どのデータベース表に対しても必要な数の問合せを実行でき、その読取り専用トランザクション内の各問合せの結果は、特定の1つの時点における一貫性が保たれています。

読取り専用トランザクションでは、トランザクション・レベルの読取り一貫性を保持するためにデータ・ロックを加えることはありません。文レベルの読取り一貫性のために使用されるマルチバージョンの一貫性モデルが、トランザクション・レベルの読取り一貫性を保持するために使用されます。すべての問合せは、読取り専用トランザクションが開始したときに決定されたシステム変更番号(SCN)の情報を戻します。データがロックされないため、読取り専用トランザクションが問い合せているデータを他のトランザクションが同時に問い合せたり更新することができます。

実行時間の長い問合せでは、読取り一貫性(CR)操作に必要なUNDO情報が無効になったためにエラーが発生することがあります。これは、アクティブなトランザクションによって、コミット済UNDOブロックが上書きされた場合に起こります。自動UNDO管理を行うと、UNDO領域を再利用できる時点、つまり、UNDO情報の保存期間を明示的に制御できます。データベース管理者は、パラメータUNDO_RETENTIONを使用して保存期間を指定できます。


関連項目:

実行時間の長い問合せおよび再開可能領域割当ての詳細は、『Oracle Database管理者ガイド』を参照してください。

たとえば、UNDO_RETENTIONを30分に設定すると、システムのすべてのコミット済UNDO情報は、最低30分間保持されます。この設定により、通常の環境下であれば、実行時間が30分以内の問合せでOERエラー「スナップショットが古すぎます。」が発生することはなくなります。

読取り専用トランザクションは、READ ONLYオプションを含むSET TRANSACTION文で始まります。次に例を示します。

SET TRANSACTION READ ONLY;

SET TRANSACTION文は、新しいトランザクションの最初の文として指定する必要があります。DDL文以外の文がSET TRANSACTION READ ONLY文の前にあると、エラーが返されます。SET TRANSACTION READ ONLY文が正常に実行されると、そのトランザクションでは、SELECT文(FOR UPDATE句なし)、COMMIT文、ROLLBACK文またはDML以外の文(たとえば、SET ROLEALTER SYSTEMLOCK TABLE)のみが使用できます。これら以外の文では、エラーが戻されます。COMMIT文、ROLLBACK文またはDDL文によって、読取り専用トランザクションは終了します(DDL文によって、読取り専用トランザクションは暗黙的にコミットされ、それ自身のトランザクション内でコミットされます)。

カーソルの使用

PL/SQLでは、1行のみ戻す問合せも含めて、すべてのSQLデータ操作文に対してカーソルを暗黙的に宣言します。複数行を戻す問合せの場合、カーソルを明示的に宣言して行を別々に処理できます。

カーソルは、特定のプライベートSQL領域へのハンドルです。カーソルは、特定のプライベートSQL領域の名前と考えることができます。PL/SQLカーソル変数を使用すると、ストアド・サブプログラムから複数の行を取得できます。カーソル変数を使用すると、3GLアプリケーション内のパラメータとしてカーソルを渡すことができます。カーソル変数の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。

ほとんどのOracle Databaseユーザーは、データベース・ユーティリティの自動カーソル処理を使用しますが、アプリケーション設計者は、プログラム・インタフェースを使用した方がカーソルを制御しやすくなります。アプリケーション開発では、カーソルはプログラムで使用できる名前付きのリソースであり、アプリケーションに埋め込まれたSQL文の解析に特に有効です。

内容は次のとおりです。

1つのセッションでオープンできるカーソルの数

1つのセッションで同時にオープンできるカーソルの総数に絶対的な制限はありませんが、次の2つの制約があります。

  • 各カーソルは仮想メモリーを必要とするため、セッションの総カーソル数は、プロセスで使用できるメモリーによって制限されます。

  • セッションごとのカーソル数に関するシステム全体の上限は、パラメータ・ファイル(INIT.ORAなど)内のOPEN_CURSORS初期化パラメータによって設定されます。


    関連項目:

    OPEN_CURSORSの詳細は、『Oracle Databaseリファレンス』を参照してください。

プリコンパイラ・プログラムに対して明示的にカーソルを作成すると、アプリケーションのチューニング時に有効です。たとえば、カーソル数を増加させると、解析頻度が削減されパフォーマンスが改善されます。ある時点で必要となるカーソル数がわかっている場合、その数のカーソルを同時にオープンできます。

文を再実行するためのカーソルの使用

各ステージの実行後、カーソルはそのSQL文に関する十分な情報を保持しているため、同じカーソルに他のSQL文が対応付けられていないかぎり、最初から実行しなおさなくてもその文を再実行できます。文は、解析ステージを含めなくても再実行できます。

カーソルをいくつかオープンすることによって、いくつかのSQL文の解析済表現を保存できます。同じSQL文を繰り返し実行する場合、記述手順、定義手順、バインド手順または実行手順から開始することができ、カーソルのオープンおよび解析を繰り返す必要がなくなります。

あるカーソルのパフォーマンス特性を理解するために、DBAは、V$SQL動的パフォーマンス・ビューを使用して、そのカーソルが表現する問合せのテキストを取得できます。元の問合せに対するEXPLAIN PLANの結果は、問合せの実際の処理方法とは異なる場合があります。そのためDBAは、次の動的パフォーマンス・ビューを調べることによってさらに正確な情報を取得できます。

ビュー 説明
V$SQL_PLAN ライブラリ・キャッシュにロードされた各子カーソルの実行計画情報。
V$SQL_STATISTICS 各子カーソルの行ソース・レベルでの実行統計。
V$SQL_STATISTICS_ALL (ソートまたはハッシュ結合に)SQLメモリーを使用する行ソースのメモリー使用量統計。このビューは、V$SQL_PLAN内の情報をV$SQL_PLAN_STATISTICSおよびV$SQL_WORKAREAからの実行統計と連結します。


関連項目:

前述の動的パフォーマンス・ビューの詳細は、『Oracle Databaseリファレンス』を参照してください。

カーソルのクローズ

カーソルのクローズとは、関連付けられているプライベート領域に現在ある情報が失われ、そのメモリーの割当てが解除されることです。カーソルは、一度オープンされると次のいずれかが発生するまでクローズされません。

  • ユーザー・プログラムがサーバーとの接続を終了した場合。

  • ユーザー・プログラムがOCIプログラムまたはプリコンパイラ・アプリケーションのとき、そのプログラムの実行中にオープン状態のカーソルを明示的にクローズした場合。(ただし、これらのプログラムの終了時にオープンしたままのカーソルがあると、カーソルは暗黙的にクローズされます。)

カーソルの取消し

カーソルを取り消すと、現在のフェッチからリソースが解放されます。対応付けられたプライベート領域に現在ある情報は失われますが、カーソルはオープンしたままになり、解析され、バインド変数に対応付けられます。


注意:

Pro*C/C++またはPL/SQLを使用してカーソルを取り消すことはできません。


関連項目:

OCIStmtFetch2文によるカーソルの取消しの詳細は、『Oracle Call Interfaceプログラマーズ・ガイド』を参照してください。

明示的な表のロック

Oracle Databaseでは、データの同時実行性、整合性、および文レベル読取り一貫性を保持するために、常に必要なロックが実行されます。これらのデフォルト・ロック・メカニズムはオーバーライドできます。たとえば、次のような場合に、Oracle Databaseのデフォルト・ロックのオーバーライドが必要になります。

自動ロック・メカニズムは、トランザクション・レベルでオーバーライドできます。次のSQL文を含むトランザクションは、Oracle Databaseのデフォルト・ロックをオーバーライドします。

これらの文によって取得されるロックは、トランザクションのコミット後またはロールバック後に解除されます。

次の項では、Oracle Databaseのデフォルト・ロックをオーバーライドするために使用できる各オプションを説明します。DML_LOCKS初期化パラメータによって、DMLロックの最大数が決定されます。


関連項目:

DML_LOCKSの詳細は、『Oracle Databaseリファレンス』を参照してください。

通常、デフォルト値には十分な値が設定されていますが、手動ロックを追加して使用する際に、この値を大きくする必要がある場合があります。


注意:

いずれかのレベルでOracle Databaseのデフォルト・ロックをオーバーライドする場合は、新しいロック・サブプログラムが正しく動作することを確認する必要があります。データ整合性が保証されていること、データ同時実行性が許容されていること、およびデッドロックの可能性がないこと、またはデッドロックが適切に処理されていることを確認してください。

内容は次のとおりです。

必要な権限

自スキーマ内の表に対しては、どの種類の表ロックでも自動的に取得できます。他スキーマ内の表に対して表ロックを取得するには、LOCK ANY TABLEシステム権限またはその表に対する(SELECTUPDATEなどの)オブジェクト権限が必要です。

ロック方法の選択

LOCK TABLE文が実行されると、トランザクションは指定された表ロックを明示的に取得します。LOCK TABLE文は、デフォルト・ロックを手動でオーバーライドします。ビューに対してLOCK TABLE文が発行されると、基礎となる実表がロックされます。次の文は、emp_tab表およびdept_tab表を含むトランザクションにかわって、この2つの表に対する排他表ロックを取得します。

LOCK TABLE emp_tab, dept_tab
    IN EXCLUSIVE MODE NOWAIT;

ロック・モードが同じ場合は、ロックする表またはビューを複数指定できます。ただし、1つのLOCK TABLE文に指定できるロック・モードは1つのみです。


注意:

表がロックされると、その表のすべての行がロックされます。他のユーザーは、その表を変更できません。

LOCK TABLE文では、表ロックの待ち時間も指示できます。

  • ロックを待機しない場合は、NOWAITまたはWAIT 0を指定します。

    表ロックがすぐに使用可能である場合にのみ表ロックを取得します。すぐに使用可能でない場合は、その時点ではロックを使用できないことを示すエラーが戻されます。

  • 表ロックを取得するためにn秒まで待機する場合は、WAIT nを指定します。ここで、nは0より大きく、100000以下です。

    n秒経過しても表ロックを使用できない場合は、その時点ではロックを使用できないことを示すエラーが戻されます。

  • ロックを取得するまで無限に待機する場合は、NOWAITおよびWAITのいずれも指定しないでください。

    データベースは表が使用可能になるまで無限に待機し、表をロックして制御を戻します。データベースでDML文と同時にDDL文を実行すると、タイムアウトやデッドロックが発生する場合があります。これらのタイムアウトやデッドロックがデータベースで検出されると、エラーが戻されます。

LOCK TABLE文の構文については、『Oracle Database SQL言語リファレンス』を参照してください。

内容は次のとおりです。

ROW SHARE MODEおよびROW EXCLUSIVE MODEでロックする場合

行共有(ROW SHARE)表ロックおよび行排他(ROW EXCLUSIVE)表ロックは、最も高い同時実行性を提供します。次のような場合に使用します。

  • トランザクション内で表を更新する前に、別のトランザクションが共有表ロック、行共有表ロックまたは排他表ロックを割り込んで取得しないようにする必要がある場合。

    別のトランザクションが共有表ロック、行共有表ロックまたは排他表ロックを割り込んで取得した場合、他のどのトランザクションも、そのロックしているトランザクションがコミットまたはロールバックされるまで、表を更新できません。

  • トランザクションで表を変更できるようになるまで、表の変更または削除を防止する必要がある場合。

SHARE MODEでロックする場合

共有(SHARE)表ロックは非常に制限の多いデータ・ロックです。次のような場合に使用します。

  • トランザクションが表を問い合せるのみで、そのトランザクションの存続中一貫した一連の表データを必要とする場合。

  • 表に対してSHAREロックを保持するすべてのトランザクションがコミットまたはロールバックするまで、ロックされている表を更新しようとする他のトランザクションを阻止できる場合。

  • 他のトランザクションが、ロックされている表に対して同時にSHARE表ロックを取得でき、またトランザクション・レベルの読取り一貫性のオプションを使用できる場合。


    注意:

    同じトランザクション内で、後で表を更新することもしないこともあります。ただし、複数のトランザクションが同じ表に対して共有表ロックを同時に保持している場合は(SELECT FOR UPDATE文の結果によって行ロックが保持されている場合でも)、どのトランザクションも表を更新できません。したがって、同じ表に対する同時共有表ロックがよく発生する場合は、更新処理を継続できず、デッドロックがよく発生することになります。このような場合には、かわりに共有行排他ロックまたは排他表ロックを使用してください。

シナリオ:employeesおよびbudget_tabは、第3の表departmentsの一貫した一連のデータを必要とします。特定の部門番号に関して、employeesおよびbudget_tabの情報を更新し、この2つのトランザクションの間に新しいメンバーが部門に追加されないように保証するものとします。

解決方法: 例2-1のようにdepartments表をSHARE MODEでロックします。departments表の更新はまれなため、ロックしても他の多くのトランザクションの待機時間が長くなることはありません。

例2-1 SHARE MODEでのLOCK TABLE

SQL> -- Create and populate table:
SQL>
SQL> DROP TABLE budget_tab;
DROP TABLE budget_tab
           *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> CREATE TABLE budget_tab (
  2    sal     NUMBER(8,2),
  3    deptno  NUMBER(4));

Table created.

SQL> INSERT INTO budget_tab
  2    SELECT salary, department_id
  3      FROM employees;

107 rows created.

SQL> -- Lock departments and update employees and budget_tab:
SQL>
SQL> LOCK TABLE departments IN SHARE MODE;

Table(s) Locked.

SQL> UPDATE employees
  2    SET salary = salary * 1.1
  3      WHERE department_id IN
  4        (SELECT department_id FROM departments WHERE location_id = 1700);

18 rows updated.

SQL> UPDATE budget_tab
  2    SET sal = sal * 1.1
  3      WHERE deptno IN
  4        (SELECT department_id FROM departments WHERE location_id = 1700);

18 rows updated.

SQL> -- COMMIT releases lock
SQL> COMMIT;
SQL>

SHARE ROW EXCLUSIVE MODEでロックする場合

共有行排他(SHARE ROW EXCLUSIVE)表ロックは、次のような場合に使用します。

  • トランザクションで、特定の表についてトランザクション・レベルの読取り一貫性があり、かつ、ロックされている表が更新可能であることが必要な場合。

  • 他のトランザクションによる(SELECT FOR UPDATEを使用した)明示的な行ロックの可能性を考慮していない場合。ロック中のトランザクション内のUPDATEおよびINSERT文が待機させられ、デッドロックが発生する可能性があります。

  • このように動作するトランザクションが1つのみ必要な場合。

EXCLUSIVE MODEでロックする場合

排他(EXCLUSIVE)表ロックは、次のような場合に使用します。

  • トランザクションが、ロックされている表にすぐに更新アクセスをする必要がある場合。トランザクションが排他表ロックを保持していると、他のトランザクションはロックされた表の中の特定の行をロックできません。

  • トランザクションがコミットまたはロールバックされるまで、ロックされた表に対してトランザクション・レベルの読取り一貫性が保持される場合。

  • 低レベルのデータ同時実行性を意識する必要がなく、排他表ロックを要求するトランザクションを順次待機させて表を順番に更新させる場合。

Oracle Databaseによる表ロック制御

Oracle Databaseに表ロック制御を任せると、アプリケーションに必要なプログラム・ロジックが少なくて済みます。ただし、表ロックを自分で管理する場合より制御範囲が小さくなります。

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE文またはALTER SESSION ISOLATION LEVEL SERIALIZABLE文を発行すると、基礎となるロック・プロトコルを変更しなくても、ANSIのシリアライズ可能性を維持できます。この手法によって、ANSIのシリアライズ可能性を維持しながら表へ同時アクセスできます。表をロックすることによって、同時実行性が大幅に減少します。


関連項目:

  • SET TRANSACTION文の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。

  • ALTER SESSION文の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。


これらのパラメータの設定変更は、インスタンスの停止時にのみ行ってください。複数インスタンスが単一データベースにアクセスする場合は、すべてのインスタンスでこれらのパラメータの設定を同じにする必要があります。

明示的な行ロック

FOR UPDATE句を含むSELECT文を使用すると、デフォルト・ロックをオーバーライドできます。この文は、選択されている行がその後の文で更新されることを想定し、(UPDATE文のように)選択された行に対する明示的な行ロックを取得します。

SELECT FOR UPDATE文を使用すると、実際にその行を変更せずに行をロックできます。たとえば、『Oracle Database PL/SQL言語リファレンス』では、いくつかのトリガーで参照整合性を実装する方法を示しています。EMP_DEPT_CHECKトリガーでは、参照される親キー値を含む行が、トランザクションの存続中は同じ値のままであることを保証するためにロックされます。親キーが更新または削除された場合、参照整合性違反になります。

SELECT FOR UPDATE文は、ユーザーが1行以上の特定行のフィールドを変更できるような対話型プログラムでよく使用されます(時間がかかることがあります)。行を更新している対話型プログラム・ユーザーが常に1ユーザーのみであるように、行がロックされます。

カーソル定義にSELECT FOR UPDATE文が使用される場合は、カーソルがオープンされるとき(最初のフェッチの前)に結果セット内の行がロックされます。行は、カーソルからフェッチされるときに、個別にロックされるわけではありません。カーソルをオープンしたトランザクションがコミットまたはロールバックされたときにのみ、ロックが解除されます。カーソルがクローズされるときには、ロックは解除されません。

SELECT FOR UPDATE文の結果セット内の各行は個別にロックされます。SELECT FOR UPDATE文は、競合している行ロックを他のトランザクションが解除するまで待機します。したがって、SELECT FOR UPDATE文が表の行を多数ロックし、表に対して非常に多くの更新アクティビティが発生する場合は、EXCLUSIVE表ロックを取得する方がパフォーマンスが改善する場合があります。


注意:

問合せの実行中に、SELECT FOR UPDATEに対する結果セットが変化する場合があります。たとえば、問合せ開始後に問合せで選択された列が更新されたり、行が削除された場合です。このような場合、SELECT FOR UPDATEは変更されなかった行でロックを取得し、このロックを使用して表の新しい読取り一貫性スナップショットを取得してから、残りのロックを取得するために問合せを再起動します。

行がランダムにロックされたときにこの処理が実行されると、DML操作を使用して表を同時に問い合せているセッション間にデッドロックが発生する場合があります。このようなデッドロックの発生を防ぐために、表の同時DMLが問合せの結果セットに影響しないようにアプリケーションを設計してください。無理な場合は、アプリケーションでの問合せをシリアライズするという方法もあります。


デフォルトでは、要求された行ロックが取得されるまでトランザクションは待機します。行ロック取得時の待機を避ける場合は、LOCK TABLE文のNOWAIT句を使用するか(「ロック方法の選択」を参照)、またはSELECT FOR UPDATE文のSKIP LOCKED句を使用します。

要求した行のすべてはロックできず、その一部しかロックできない場合は、SKIP LOCKEDオプションを使用すると、ロックできない行はスキップされ、ロックできる行はロックされます。


関連項目:

SELECT FOR UPDATE文の詳細とSKIP LOCKED句の例については、『Oracle Database SQL言語リファレンス』を参照してください。

Oracle Lock Managementサービスの使用

DBMS_LOCKパッケージのサブプログラムを起動することで、アプリケーションでOracle Lock Managementサービス(ユーザー・ロック)を使用できます。特定のモードのロックを要求し、同一または別のインスタンスの別のサブプログラムで認識できる一意の名前を付け、ロック・モードを変更し、解除できます。確保されるユーザー・ロックはOracle Databaseロックと同一であるため、デッドロックの検出などのデータベース・ロックのすべての機能を持っています。分散トランザクションで使用されるユーザー・ロックは、COMMITと同時に解除されるようになっていることを確認してください。解除されないと、検出されないデッドロックが発生する可能性があります。


関連項目:

DBMS_LOCKパッケージの詳細は、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください。

内容は次のとおりです。

ユーザー・ロックを使用する場合

次のような場合、ユーザー・ロックを使用します。

  • 端末などの装置に対して排他的アクセスを可能にする場合

  • アプリケーション・レベルの読込みロックを適用する場合

  • ロックの解除を検出し、アプリケーションの終了後にクリーンアップする場合

  • アプリケーションを同期化して、順次処理を実行する場合

次のPro*COBOLプリコンパイラの例は、複数のユーザーが1つの装置にアクセスする必要がある場合に、競合が発生しないように保証するためのロックの使用方法を示しています。

例2-2 ユーザー・ロック

******************************************************************
* Print Check                                                    *
* Any cashier may issue a refund to a customer returning goods.  *
* Refunds under $50 are given in cash, more than $50 by check.   *
* This code prints the check. One printer is opened by all       *
* the cashiers to avoid the overhead of opening and closing it   *
* for every check, meaning that lines of output from multiple    *
* cashiers can become interleaved if you do not ensure exclusive *
* access to the printer. The DBMS_LOCK package is used to        *
* ensure exclusive access.                                       *
******************************************************************
CHECK-PRINT
*    Get the lock "handle" for the printer lock.
   MOVE "CHECKPRINT" TO LOCKNAME-ARR.
   MOVE 10 TO LOCKNAME-LEN.
   EXEC SQL EXECUTE
      BEGIN DBMS_LOCK.ALLOCATE_UNIQUE ( :LOCKNAME, :LOCKHANDLE );
      END; END-EXEC.
*   Lock the printer in exclusive mode (default mode).
   EXEC SQL EXECUTE
      BEGIN DBMS_LOCK.REQUEST ( :LOCKHANDLE );
      END; END-EXEC.
*   You now have exclusive use of the printer, print the check.
  ...
*   Unlock the printer so other people can use it
EXEC SQL EXECUTE
      BEGIN DBMS_LOCK.RELEASE ( :LOCKHANDLE );
      END; END-EXEC.

ロックの表示および監視

表2-3では、インスタンスで処理中のトランザクションのロック情報を表示する、Oracle Databaseの機能について説明しています。

表2-3 ロック情報の表示方法

ツール 説明

Oracle Enterprise Manager 10g Database Control


「データベース・パフォーマンス」ページの「その他の監視リンク」セクションで、「データベース・ロック」をクリックしてユーザー・ブロック、ブロッキング・ロック、またはすべてのデータベース・ロックの詳細なリストを表示します。詳細は、『Oracle Database 2日でデータベース管理者』を参照してください。

UTLLOCKT.SQL

UTLLOCKT.SQLスクリプトは、ツリー構造の簡単なロック待機グラフを表示します。スクリプトの実行にSQLツール(SQL*Plusなど)を使用し、システム内のロック待機中のセッション、およびそれに対応するブロッキング・ロックを出力します。このスクリプト・ファイルの位置は、オペレーティング・システムによって異なります。(UTLLOCKT.SQLを使用する前に、CATBLOCK.SQLスクリプトを実行する必要があります。)


シリアライズ可能トランザクションを使用した同時実行性の制御

Oracle Databaseは、デフォルトでは、同時に実行されるトランザクションの同じ表および同じデータ・ブロック内での行の修正、追加または削除を許可しています。あるトランザクションによって行われた変更は、その変更を行ったトランザクションがコミットされるまで、別の同時トランザクションでは参照できません。

トランザクションAが、(DML文またはSELECT FOR UPDATE文を使用して)別のトランザクションBによってロックされている行を更新または削除しようとすると、トランザクションAのDML文は、トランザクションBがコミットまたはロールバックされるまでブロックされます。トランザクションBがコミットされると、トランザクションAは、トランザクションBがそのデータベースに対して行った変更を参照できます。

この同時実行性モデルはほとんどのアプリケーションに適しています。これは、より高度な同時実行性が提供されて、パフォーマンスが改善されるためです。ただし、まれにシリアライズ可能なトランザクションが必要な場合もあります。シリアライズ可能トランザクションは、同時にではなく、一度に1トランザクションずつ(シリアルで)実行しているように見える方法で実行する必要があります。シリアル・モードで実行中の同時トランザクションでは、それらのトランザクションが1つずつ順次実行された場合に可能となるデータベースの変更のみが可能です。

図2-1は、シリアライズ可能トランザクション(B)と、別のトランザクション(A)との相互作用を示しています。

ANSI/ISO SQL規格SQL92は、考えられる3種類のトランザクションの相互作用、およびそれらの相互作用に対する保護を強化する4レベルの分離を定義しています。表2-4に、これらの相互作用および分離レベルの概要を示します。

表2-4 ANSI分離レベルの概要

分離レベル 内容を保証しない読込み脚注1 反復不能な読込み脚注2 仮読込み脚注3

READ UNCOMMITTED

可能性あり

可能性あり

可能性あり

READ COMMITTED

可能性なし

可能性あり

可能性あり

REPEATABLE READ

可能性なし

可能性なし

可能性あり

SERIALIZABLE

可能性なし

可能性なし

可能性なし


脚注1 トランザクションは、別のトランザクションで変更されたコミットされていないデータを読み込むことができます。

脚注2 トランザクションは、別のトランザクションでコミットされたデータを再度読み込み、その新しいデータを参照します。

脚注3 トランザクションは問合せを再実行し、コミットされた別のトランザクションによって挿入された新しい行を検出できます。

これらの分離レベルに関するOracle Databaseの動作について、表2-5に概要を示します。

表2-5 ANSI分離レベルとOracle Database

分離レベル 説明

READ UNCOMMITTED

Oracle Databaseでは「内容を保証しない読込み」は許可されません。他のデータベース製品の中には、スループットの改善のために、この方法を使用するものもありますが、スループットの高いOracle Databaseでは不要です。

READ COMMITTED

Oracle Databaseは、READ COMMITTED分離標準に準拠しています。これは、すべてのOracle Databaseアプリケーションのデフォルト・モードです。Oracle Databaseでは、問合せの始め(スナップショット時)にコミット済のデータのみを参照するため、READ COMMITTED分離について実際にANSI/ISO SQL92規格で要求される以上の整合性を提供します。

REPEATABLE READ

通常、Oracle Databaseは、シリアライズ可能トランザクションSERIALIZABLEによって提供されるもの以外は、この分離レベルをサポートしていません。

SERIALIZABLE

通常、Oracle Databaseは、シリアライズ可能トランザクションSERIALIZABLEによって提供されるもの以外は、この分離レベルをサポートしていません。


内容は次のとおりです。

シリアライズ可能トランザクションの相互作用

図2-1は、シリアライズ可能トランザクション(トランザクションB)と、別のトランザクション(トランザクションA、SERIALIZABLEまたはREAD COMMITTEDのいずれか)との相互作用を示しています。

シリアライズ可能トランザクションがORA-08177で失敗した場合、アプリケーションは次のいずれかで対処できます。

  • そのポイントまで実行された作業をコミットします。

  • その他の様々な文を、トランザクション内の直前のセーブポイントまでロールバックした後に実行します。

  • トランザクション全体をロールバックし再試行します。

Oracle Databaseでは、同時トランザクションによるアクセスを管理するために、各データ・ブロックに制御情報を格納します。SERIALIZABLE分離レベルを使用するには、CREATE TABLE文またはALTER TABLE文のINITRANS句を使用して、この制御情報の格納を取り消す必要があります。SERIALIZABLEモードを使用するには、INITRANSを3以上に設定する必要があります。

図2-1 2つのトランザクションの時系列的働き

2つのトランザクションの時系列的働き
「図2-1 2つのトランザクションの時系列的働き」の説明

シリアライズ可能トランザクションの分離レベルの設定

トランザクションの分離レベルは、SET TRANSACTION文のISOLATION LEVEL句を使用して変更できます。SET TRANSACTION文は、トランザクションで最初に発行される文である必要があります。

トランザクション分離レベルをセッション全体に設定するには、ALTER SESSION文を使用します。


関連項目:

  • ALTER SESSION文の構文は、『Oracle Database SQL言語リファレンス』を参照してください。

  • SET TRANSACTION文の構文は、『Oracle Database SQL言語リファレンス』を参照してください。


Oracle Databaseでは、同時トランザクションによるアクセスを管理するために、各データ・ブロックに制御情報を格納します。したがって、トランザクション分離レベルをSERIALIZABLEに設定する場合は、ALTER TABLE文を使用して、INITRANSを3以上に設定する必要があります。このパラメータを使用すると、Oracle Databaseは、ブロックにアクセスした最新トランザクションの履歴を記録するために十分な記憶域をそれぞれのブロック内に割り当てます。同じブロックを更新するトランザクションの数が多い表には、より大きい値を使用します。

参照整合性およびシリアライズ可能トランザクション

Oracle Databaseは、SERIALIZABLEトランザクション内であっても読込みロックを使用しないため、あるトランザクションによって読み込まれたデータは、別のトランザクションでオーバーライドできます。アプリケーション・レベルでデータベースの整合性チェックを実行するトランザクションでは、読み込んだデータはトランザクション実行中には変更されないと考えないでください(そのような変更がトランザクションからはわからない場合も)。SERIALIZABLEトランザクションを使用した場合でも、アプリケーション・レベルの整合性チェックのコードを十分注意して作成しないと、データベースの不整合が発生する可能性があります。


注意:

この項に示されている例は、READ COMMITTEDトランザクションとSERIALIZABLEトランザクションの両方に適用されます。

図2-2に、2つの表の間の参照整合性の親子関係を保持するために、アプリケーション・レベルでチェックを実行する異なる2つのトランザクションを示します。一方のトランザクションは、親表に特定の主キー値を持つ行が存在するかどうかをチェックした後、対応する子である行を挿入します。もう一方のトランザクションは、対応するディテール行が存在しないことをチェックした後、親である行を削除します。この場合、両方のトランザクションが読み込んだデータは、そのトランザクションが完了する前には変更されないものと想定しています(確認はしません)。

図2-2 参照整合性チェック

参照整合性チェック
「図2-2 参照整合性チェック」の説明

トランザクションAが実行した読込みのために、トランザクションBが親である行を削除できなくなることはありません。同様に、トランザクションBが子である行の問合せを行っても、トランザクションAが子である行を挿入できなくなることもありません。したがって、この使用例では、対応する親である行を持たない子である行がデータベースに残ります。どちらのトランザクションも、整合性チェックのために読み込んだデータに対する変更を他方が防げないため、両方のトランザクションがSERIALIZABLEトランザクションであっても、このような不整合が発生する可能性があります。

この例に示されているとおり、一方のトランザクションで読み込まれたデータがもう一方のトランザクションで同時に書き込まれないように、処置を行う必要がある場合があります。これには、SQL92 SERIALIZABLEモードで定義されているトランザクション分離レベルよりもかなり高いレベルが必要です。

Oracle Databaseでは、前述の矛盾を簡単に防ぐことができます。

  • トランザクションAは、SELECT FOR UPDATEを使用して、親である行を問い合せてロックし、トランザクションBがその行を削除しないように設定できます。

  • 逆に、トランザクションBは、トランザクションAが親である行へのアクセスを取得しないように設定できます。トランザクションBは、まず親である行を削除し、その後の問合せで、子表の中に対応する行が存在することが判明した場合、ロールバックします。

Oracle Databaseでは、トランザクションAの場合のような独立した問合せのかわりに、データベース・トリガーを使用して参照整合性を施行することもできます。たとえば、子表へのINSERTによって、BEFORE INSERT行レベル・トリガーを起動して、対応する親である行の有無をチェックできます。このトリガーは、SELECT FOR UPDATEを使用して親表を問い合せ、子である行を挿入するトランザクションの処理中に、親である行が(存在する場合)データベース内に残るようにします。対応する親である行が存在しない場合、トリガーは子である行の挿入を拒否します。

データベース・トリガーによって発行されたSQL文は、そのトリガーを起動したSQL文のコンテキスト内で実行されます。1つのトリガー内で実行されるすべてのSQL文は、トリガー起動文から参照する状態と同じデータベース状態を参照します。そのため、READ COMMITTEDトランザクションでは、トリガー内のSQL文は、トリガー文の実行開始時点のデータベースを参照し、SERIALIZABLEモードで実行するトランザクションでは、SQL文は、そのトランザクションの開始時点のデータベースを参照します。いずれの場合も、トリガーでSELECT FOR UPDATEを使用すると、参照整合性が正しく施行されます。

READ COMMITTED分離およびSERIALIZABLE分離

Oracle Databaseの場合、アプリケーション開発者は異なる特性を持つ2つのトランザクション分離レベルのうちの1つを選択できます。READ COMMITTEDおよびSERIALIZABLE分離レベルは、どちらも高度な一貫性および同時実行性を提供します。これら2つの分離レベルは競合を軽減し、実社会でのアプリケーションのデプロイ用に設計されています。この項の後半では、2つの分離レベルを比較し、その選択の際に有効な情報を示します。

内容は次のとおりです。

トランザクション集合の整合性

Oracle DatabaseのREAD COMMITTEDおよびSERIALIZABLE分離レベルについて説明するには、次のことを考慮すると効果的です。

  • データベース表(または任意の一連のデータ)の集合

  • それらの表内の行の特定の読込み順序

  • 特定の時刻(任意)にコミットされるトランザクションの集合

どの読込みにおいても、同じコミット済トランザクション集合によって書き込まれたデータが戻される操作(問合せまたはトランザクション)を、「トランザクション集合整合である」といいます。トランザクション集合整合でない操作では、ある集合のトランザクションの変更が反映される読込みと、他のトランザクションによって行われた変更が反映される読込みが存在します。そのような操作では、そのデータベースは、コミットされたトランザクション集合が反映されていない状態のデータベースのように見えます。

Oracle DatabaseのREAD COMMITTEDモードでは、問合せによって読み込まれたすべての行が、その問合せが始まる前にコミットされているため、文単位でのトランザクション集合整合です。

Oracle DatabaseのSERIALIZABLEモードでは、SERIALIZABLEトランザクション内のすべての文が、トランザクション開始時点のデータベースのイメージに対して実行されるため、トランザクション単位でのトランザクション集合整合です。

他のデータベース・システムでは、READ COMMITTEDモードで問合せを1回実行すると、トランザクション集合の整合性は失われます。この問合せでは、別のトランザクションによって行われた変更のサブセット以外見えないため、トランザクション集合は整合していません。たとえば、ディテール表とマスター表を結合すると、別のトランザクションによって挿入されたマスター・レコードを見ることはできますが、そのトランザクションによって挿入された対応するディテールは見えません(その逆も同じです)。READ COMMITTEDモードではこのような問題は回避されるため、読込みロック・システムより高い整合性が得られます。

読込みロック・システムでは、同時更新ができないようにするかわりに、SQL92 REPEATABLE READ分離によって、トランザクション・レベルではなく、文レベルでトランザクション集合の整合性が提供されます。仮(phantom)の保護がないということは、同一のトランザクションによって発行された2つの問合せが、他のトランザクションの別の集合によってコミットされたデータを参照できることを意味します。これらのシステムでは、スループットに制限がありデッドロックされやすいSERIALIZABLEモードの場合のみ、トランザクション・レベルでのトランザクション集合の整合性が提供されます。

コミット読込みトランザクションとシリアライズ可能トランザクションの比較

表2-6は、READ COMMITTEDトランザクションとSERIALIZABLEトランザクションの主な類似点および相違点の概要を示しています。

表2-6 コミット読込みトランザクションとシリアライズ可能トランザクション

操作 コミット読込み シリアライズ可能

内容を保証しない書込み

可能性なし

可能性なし

内容を保証しない読込み

可能性なし

可能性なし

反復不能な読込み

可能性あり

可能性なし

仮読込み

可能性あり

可能性なし

ANSI/ISO SQL 92への準拠

あり

あり

スナップショット読込み時間

トランザクション

トランザクション集合の整合性

文レベル

トランザクション・レベル

行レベル・ロック

あり

あり

読込みが書込みをブロック

なし

なし

書込みが読込みをブロック

なし

なし

異なる行の書込みが書込みをブロック

なし

なし

同じ行の書込みが書込みをブロック

あり

あり

阻止しているトランザクションの待機

あり

あり

エラー「このトランザクションのアクセスをシリアル化できません」の発生する可能性

なし

あり

阻止しているトランザクションの終了後のエラー

なし

なし

阻止しているトランザクションのコミット後のエラー

なし

あり


トランザクションの分離レベルの選択

それぞれのアプリケーションおよび作業負荷に適した分離レベルを選択する必要があります。また、異なるトランザクションにはそれぞれ個別の分離レベルを選択できます。分離レベルは、パフォーマンスと整合性のニーズ、およびアプリケーション・コーディング要件を考慮して選択します。

多数のユーザーが、トランザクションを同時に次々に送る環境の場合、予期されるトランザクション到着頻度および応答時間要件に対するトランザクション・パフォーマンスを評価して、十分なパフォーマンスを確保しながら必要な整合性を提供する分離レベルを選択する必要があります。多くの場合、高パフォーマンス環境では、整合性と同時実行性(トランザクションのスループット)を考慮して妥協点を見つける必要があります。

どちらのOracle Database分離モードも、行レベル・ロックとOracle Databaseの複数バージョン同時実行処理制御システムとを組み合せることによって、高レベルの整合性および同時実行性(およびパフォーマンス)を提供します。Oracle Databaseでは読込みと書込みの相互干渉がないため、問合せで整合性のあるデータが参照できる一方、READ COMMITTED分離およびSERIALIZABLE分離により、コミットされていない(内容が保証されない)データの読込みを防止し高レベルの同時実行性を提供することで、高いパフォーマンスを実現しています。

READ COMMITTED分離レベルでは、一部のトランザクションについては(仮読込みおよび反復不能な読込みのため)一貫性のない結果が生成される可能性は多少高くなりますが、かなり高い同時実行性を提供できます。SERIALIZABLE分離レベルの場合は、仮読込みおよび反復不能な読込みから保護されているため、より高い整合性が提供され、読込み/書込みトランザクションが問合せを2回以上実行する場合にはこの分離レベルは重要です。ただし、SERIALIZABLEモードでは、アプリケーションが「このトランザクションのアクセスをシリアル化できません」というエラーの有無を確認する必要があり、多数の同時トランザクションが更新のために同じデータにアクセスする環境では、スループットはかなり低下する可能性があります。データベースの整合性を確認するアプリケーション・ロジックでは、いずれのモードでも読込みが書込みをブロックしないように設定する必要があります。

トランザクションのためのアプリケーションのヒント

トランザクションをSERIALIZABLEモードで実行する場合、シリアライズ可能トランザクションの開始以降に、別のトランザクションにより変更されたデータを変更しようとすると、ORA-08177が発生します。

このエラーが発生した場合は、現行のトランザクションをロールバックし、操作を再実行します。トランザクションが新しいトランザクション・スナップショットを取得するため、操作が成功する可能性が高くなります。

トランザクションのロールバックおよび再実行によるパフォーマンスのオーバーヘッドを最小化するには、他の同時トランザクションと競合する可能性のあるDML文は、できるだけトランザクションの始めの方に置くようにしてください。

自律型トランザクション

この項では、自律型トランザクション(AT)の概要およびこのトランザクションの機能を簡単に説明します。


関連項目:

自律型トランザクションの詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。

プライマリ・トランザクションの最終結果とは関係なく、表に対して別の変更をコミットまたはロールバックすることが必要になる場合があります。たとえば、株式売買トランザクションでは、全体的な株式売買行為が実際に遂行されるかどうかに関係なく、顧客情報のコミットが必要な場合があります。またはそのトランザクションを実行中にトランザクション全体がロールバックされた場合でも、エラー・メッセージをデバッグ表にログする必要がある場合もあります。自律型トランザクションを使用すると、これらのタスクを実行できます。

自律型トランザクション(AT)は、別のトランザクション(メイン・トランザクション(MT))によって開始される独立したトランザクションです。自律型トランザクションを使用すると、メイン・トランザクションを停止して、SQL操作を実行し、そのSQL操作をコミットまたはロールバックした後でメイン・トランザクションを再開できます。

自律型(非依存)トランザクションは、自律型スコープ内で実行されます。自律型スコープとは、プラグマ(コンパイラ・ディレクティブ)AUTONOMOUS_TRANSACTIONでマークされたルーチンです。このプラグマは、PL/SQLコンパイラに対してルーチンを自律型としてマークするように指示します。

図2-3に、メイン・ルーチン(MT)と自律型ルーチン(AT)との間の制御フローを示します。図からわかるように、自律型ルーチンでは、制御がメイン・ルーチンに戻る前に複数のトランザクション(AT1およびAT2)をコミットできます。

図2-3 トランザクション制御フロー

トランザクション制御フロー
「図2-3 トランザクション制御フロー」の説明

自律型ルーチンの実行可能セクションに入ると、メイン・ルーチンが停止します。自律型ルーチンを終了すると、メイン・ルーチンが再開します。COMMITおよびROLLBACKによって、アクティブな自律型トランザクションは終了しますが、自律型ルーチンは終了しません。図2-3に示すとおり、1つのトランザクションが終了すると、次のSQL文が別のトランザクションを開始します。

自律型トランザクションの特長をさらにいくつか示します。

図2-4に、自律型トランザクションが従う実行順序の例を示します。

図2-4 自律型トランザクションの順序

自律型トランザクションの順序
「図2-4 自律型トランザクションの順序」の説明

自律型トランザクションの例

例に示すとおり、自律型トランザクションおよびメイン・トランザクションを使用する場合は、4種類の結果が考えられます(表2-7を参照)。自律型トランザクションの結果とメイン・トランザクションの結果の間に依存性はありません。

表2-7 トランザクションの結果

自律型トランザクション メイン・トランザクション

コミット

コミット

コミット

ロールバック

ロールバック

コミット

ロールバック

ロールバック


商品の注文

図2-5に示す例では、顧客がある商品を注文します。購買契約が成立しなくても、その顧客の情報(名前、住所、電話番号など)は顧客情報表にコミットされます。

図2-5 例: 購買指示

例: 購買指示
「図2-5 例: 購買指示」の説明

銀行口座からの出金処理

この例では、顧客は銀行口座から払戻しを実行しようとします。この処理で、メイン・トランザクションは2つの自律型トランザクション・スコープ(ATスコープ1またはATスコープ2)のいずれかを起動します。

このトランザクションで考えられる使用例を次に示します。

使用例1: 十分な預金残高がある場合

払戻しに十分な預金残高があり、銀行が払戻しに応じます。(図2-6を参照)。

図2-6 預金払戻し - 十分な預金残高がある場合

十分な預金残高がある場合
「図2-6 預金払戻し - 十分な預金残高がある場合」の説明

使用例2: 貸越し保護があり預金残高が不十分な場合

払戻しに十分な預金残高はありませんが、この顧客には貸越し保護があるため、銀行が払戻しに応じます(図2-7を参照)。

図2-7 預金払戻し - 貸越し保護があり預金残高が不十分な場合

貸越し保護があり預金残高が不十分な場合
「図2-7 預金払戻し - 貸越し保護があり預金残高が不十分な場合」の説明

使用例3: 貸越し保護がなく預金残高が不十分な場合

払戻しに十分な預金残高はなく、この顧客には貸越し保護もありません。したがって、銀行は払戻しを差し止めます(図2-8を参照)。

図2-8 預金払戻し - 貸越し保護がなく預金残高が不十分な場合

貸越し保護がなく預金残高が不十分な場合
「図2-8 預金払戻し - 貸越し保護がなく預金残高が不十分な場合」の説明

自律型トランザクションの定義

自律型トランザクションを定義するには、PRAGMA AUTONOMOUS_TRANSACTIONを使用します。これは、PL/SQLコンパイラに対して、ルーチンを自律型としてマークするように指示します。

例2-3では、ファンクションbalanceが自律型です。

例2-3 パッケージ・サブプログラムの自律型としてのマーク

SQL> -- Create table that package will use:
SQL>
SQL> DROP TABLE accounts;
DROP TABLE accounts
           *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> CREATE TABLE accounts (account INTEGER, balance REAL);

Table created.

SQL> -- Create package:
SQL>
SQL> CREATE OR REPLACE PACKAGE banking AS
  2    FUNCTION balance (acct_id INTEGER) RETURN REAL;
  3    -- Additional functions and packages
  4  END banking;
  5  /

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY banking AS
  2    FUNCTION balance (acct_id INTEGER) RETURN REAL IS
  3      PRAGMA AUTONOMOUS_TRANSACTION;
  4      my_bal  REAL;
  5    BEGIN
  6      SELECT balance INTO my_bal FROM accounts
  7        WHERE account=acct_id;
  8      RETURN my_bal;
  9    END;
 10    -- Additional functions and packages
 11  END banking;
 12  /

Package body created.

SQL>

関連項目:

自律型トランザクションの詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。

記憶域割当てエラー後の実行の再開

長時間にわたって実行されるトランザクションが領域不足エラー状態によって中断されたときに、アプリケーションによって問題が発生した文を一時停止し、領域問題が修正された後でその文を再開することが可能です。この機能を、再開可能記憶域割当てといいます。この機能によって、時間がかかるロールバックを回避できます。また、操作を小さく分割したり、処理過程を追跡するコードを作成する必要がなくなります。


関連項目:

  • 再開可能記憶域割当ての詳細は、『Oracle Database概要』を参照してください。

  • 再開可能記憶域割当ての詳細は、『Oracle Database管理者ガイド』を参照してください。


内容は次のとおりです。

エラー状態の後に再開可能な操作

問合せ、DML操作および特定のDDL操作は、領域不足エラーが発生した場合、すべて再開可能です。この機能は、操作がSQL文によって直接実行されているか、またはストアド・サブプログラム、無名PL/SQLブロック、SQL*Loader、OCIStmtExecuteなどのOCIコール内で実行されている場合に適用されます。

操作は、次のようなエラーの後で再開可能です。

  • ORA-01653などの領域不足エラー

  • ORA-01628などの領域制限エラー

  • ORA-01536などの領域割当てエラー

前述の方法では処理できない記憶域エラーもあります。ディクショナリ管理された表領域では、ロールバック・セグメントの上限に達するか、または索引または表の作成中にエクステントの最大数に達した場合、操作を再開できません。前述の方法にローカル管理表領域および自動UNDO管理を組み合せて使用してください。

一時停止された記憶域割当ての処理

文が一時停止された場合、アプリケーションは通常のエラー・コードを受信しません。このため、AFTER SUSPENDイベントを検出し、DBMS_RESUMABLEパッケージのファンクションを起動して問題についての情報を取得するトリガーをコーディングして、ロギングまたは通知を実行する必要があります。

トリガーの本体内では、通知を実行できます。たとえば、オペレータに電子メールを送り、領域問題について警告できます。

または、DBAは、DBA_RESUMABLE静的データ・ディクショナリ・ビューとV$_SESSION_WAIT動的パフォーマンス・ビューを使用して、一時停止された文を定期的に確認することもできます。


関連項目:

  • DBMS_RESUMABLEパッケージの詳細は、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください。

  • DBA_RESUMABLE静的データ・ディクショナリ・ビューの詳細は、『Oracle Databaseリファレンス』を参照してください。

  • V$_SESSION_WAIT動的パフォーマンス・ビューの詳細は、『Oracle Databaseリファレンス』を参照してください。


領域状態が(通常DBAによって)修正されると、一時停止された文は自動的に実行を再開します。タイムアウト周期が終了するまでに領域状態が修正されなかった場合は、その文によってSERVERERROR例外が発生します。

トリガー自体の中で領域不足エラーが発生する可能性を減らすには、トリガーを自律型トランザクションとして宣言し、トリガーがSYSTEM表領域内のロールバック・セグメントを使用するようにします。一時停止された文が保持するロックによってトリガーにデッドロック状態が発生した場合、そのトリガーは終了され、アプリケーションは文の一時停止が発生しなかった場合の本来のエラー状態を受信します。トリガーによって領域不足状態が発生した場合、そのトリガーおよび一時停止された文は両方ともロールバックされます。トリガー内の例外ハンドラを介してロールバックを回避し、文が再開されるまで待つこともできます。

例2-4のトリガーは、データベース内の記憶域エラーを処理します。いくつかのエラーでは、このトリガーは文を終了し、エラーに関する警告を電子メールによってDBAに通知します。他の一時的なエラーでは、8時間以内に記憶域の問題が解決していることを想定して、8時間後に文を再開するように指定しています。この例を実行するには、SYSDBAとしてログインする必要があります。

例2-4 再開可能記憶域割当て

SQL> -- Create table used by trigger body
SQL>
SQL> DROP TABLE rbs_error;
DROP TABLE rbs_error
           *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> CREATE TABLE rbs_error (
  2    SQL_TEXT VARCHAR2(64),
  3    ERROR_MSG VARCHAR2(64),
  4    SUSPEND_TIME VARCHAR2(64));

Table created.

SQL> -- Resumable Storage Allocation
SQL>
SQL> CREATE OR REPLACE TRIGGER suspend_example
  2    AFTER SUSPEND
  3    ON DATABASE
  4  DECLARE
  5    cur_sid           NUMBER;
  6    cur_inst          NUMBER;
  7    err_type          VARCHAR2(64);
  8    object_owner      VARCHAR2(64);
  9    object_type       VARCHAR2(64);
 10    table_space_name  VARCHAR2(64);
 11    object_name       VARCHAR2(64);
 12    sub_object_name   VARCHAR2(64);
 13    msg_body          VARCHAR2(64);
 14    ret_value         BOOLEAN;
 15    error_txt         VARCHAR2(64);
 16    mail_conn         UTL_SMTP.CONNECTION;
 17  BEGIN
 18    SELECT DISTINCT(SID) INTO cur_sid FROM V$MYSTAT;
 19    cur_inst := USERENV('instance');
 20    ret_value := DBMS_RESUMABLE.SPACE_ERROR_INFO
 21                 (err_type,
 22                  object_owner,
 23                  object_type,
 24                  table_space_name,
 25                  object_name,
 26                  sub_object_name);
 27    IF object_type = 'ROLLBACK SEGMENT' THEN
 28      INSERT INTO rbs_error
 29        (SELECT SQL_TEXT, ERROR_MSG, SUSPEND_TIME
 30           FROM DBA_RESUMABLE
 31             WHERE SESSION_ID = cur_sid
 32               AND INSTANCE_ID = cur_inst);
 33      SELECT ERROR_MSG INTO error_txt
 34        FROM DBA_RESUMABLE
 35          WHERE SESSION_ID = cur_sid
 36            AND INSTANCE_ID = cur_inst;
 37      msg_body :=
 38      'Space error occurred: Space limit reached for rollback segment '
 39      || object_name || ' on ' || to_char(SYSDATE, 'Month dd, YYYY, HH:MIam')
 40      || '. Error message was: ' || error_txt;
 41      mail_conn := UTL_SMTP.OPEN_CONNECTION('localhost', 25);
 42      UTL_SMTP.HELO(mail_conn, 'localhost');
 43      UTL_SMTP.MAIL(mail_conn, 'sender@localhost');
 44      UTL_SMTP.RCPT(mail_conn, 'recipient@localhost');
 45      UTL_SMTP.DATA(mail_conn, msg_body);
 46      UTL_SMTP.QUIT(mail_conn);
 47      DBMS_RESUMABLE.ABORT(cur_sid);
 48    ELSE
 49      DBMS_RESUMABLE.SET_TIMEOUT(3600*8);
 50    END IF;
 51    COMMIT;
 52  END;
 53  /

Trigger created.

SQL>