7 トランザクションの定義および制御
この章では、トランザクション処理の実行方法について説明します。Oracleデータに対する変更の確定または取消しを制御する方法を含めて、データベースの整合性を保つための基本的な技術を学習します。次の内容について説明します。
7.1 基本用語
トランザクションの説明に入る前に、この項で定義されている用語に慣れる必要があります。
Oracleが管理するジョブまたはタスクは、セッションと呼ばれます。アプリケーション・プログラムまたはOracle Formsなどのツールを実行してOracleに接続すると、ユーザー・セッションが開始されます。Oracleでは、ユーザー・セッションを同時に機能させ、コンピュータ・リソースを共有できます。そのために、Oracleでは同時実行性、つまり多数のユーザーによる同じデータベースへのアクセスを制御する必要があります。同時実行性の制御が十分でないと、データの整合性が失われる可能性があります。つまり、データや構造への変更が誤って行われる可能性があります。
Oracleでは、ロックを使用して、データへの同時アクセスを制御します。ロックにより、データの表や行などのデータベース・リソースのユーザーに一時的な所有権が与えられます。つまり、このユーザーがデータの変更を終了するまで他のユーザーは同じデータを変更できません。デフォルトのロック機能がOracleのデータおよび構造を保護するため、明示的にリソースをロックする必要はありません。ただし、デフォルトのロックをオーバーライドするときは、表または行単位でデータ・ロックを要求できます。行の共有や排他など、数種類のロック・モードから選択できます。
複数のユーザーが同じデータベース・オブジェクトへのアクセスを試みると、デッドロックが発生する可能性があります。たとえば、同じ表を更新するユーザーが2人いる場合、それぞれ相手が現在ロックしている行の更新を試みると待機状態になります。それぞれのユーザーが、相手が使用中のリソースを待つことになるため、Oracleによりデッドロックが解除されるまで、どちらも処理を続行できません。Oracleからは、最小量の作業を完了した関連トランザクションに対してエラー信号が送られ、「リソース待機の間にデッドロックが検出されました」というOracleエラー・コードがSQLCAのSQLCODEに戻されます。
1人のユーザーによって問合せが行われている表を、同時に別のユーザーが更新すると、Oracleでは問合せ用の表データの読取り一貫性ビューが生成されます。つまり、1つの問合せが開始され、処理が進む間、問合せによって読み取られるデータは変わりません。更新アクティビティが続行している間、Oracleでは、表データのスナップショットを取り、変更内容をロールバック・セグメントに記録します。Oracleは、ロールバック・セグメント内の情報を使用して、読取り一貫性のある問合せ結果を作成し、必要に応じて変更を元に戻します。
7.2 トランザクションによるデータベースの保護
Oracleはトランザクション指向です。つまり、トランザクションを使用してデータの整合性を保証します。トランザクションとは、あるタスクを完了するために定義する1つ以上の論理的に関連付けられたSQL文です。Oracleでは、SQL文によるすべての変更がコミットされるか(確定)、ロールバックされるか(取消し)のどちらかになるように、一連の文が1単位とみなされます。トランザクションの途中でアプリケーション・プログラムに障害が発生すると、データベースは自動的にトランザクション前の状態にリストアされます。
以降の項では、トランザクションの定義および制御方法について説明します。特に、次の方法を学習します。
-
トランザクションの開始および終了
-
COMMIT文を使用したトランザクションの確定 -
SAVEPOINT文をROLLBACKTO文と併用したトランザクションの部分的な取消し -
ROLLBACK文を使用したトランザクション全体の取消し -
RELEASEオプションの指定によるリソースの解放とデータベース接続の切断 -
SETTRANSACTION文を使用した読取り専用トランザクションの設定 -
FORUPDATE句またはLOCKTABLE文を使用したデフォルト・ロックのオーバーライド
この章で説明するSQL文の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
7.3 トランザクションの開始および終了方法
トランザクションは、プログラムの最初の実行SQL文(CONNECT以外)により開始します。1つのトランザクションが終了すると、次の実行SQL文により別のトランザクションが自動的に開始します。このように、すべての実行文はトランザクションの一部です。宣言SQL文は、ロールバックできず、コミットも必要ないため、トランザクションの一部とはみなされません。
-
COMMIT文またはROLLBACK文を記述し、RELEASEオプションは付けても付けなくてもかまいません。これにより、データベースへの変更を明示的に確定または取り消します。 -
実行の前と後に自動コミットを発行するデータ定義文(
ALTER、CREATEまたはGRANTなど)を記述します。これにより、データベースへの変更を暗黙的に確定します。
システム障害が発生した場合や、ソフトウェアの問題、ハードウェアの問題または強制割込みなどが原因で、予期しないユーザー・セッション停止が発生した場合にも、トランザクションは終了します。そのトランザクションはOracleによりロールバックされます。
トランザクションの途中でプログラムに障害が発生すると、Oracleによりエラーが検出され、トランザクションはロールバックされます。オペレーティング・システムに障害が発生すると、データベースがトランザクション前の状態にリストアされます。
7.4 COMMIT文の使用について
データベースへの変更を確定するには、COMMIT文を使用します。変更がコミットされるまで、他のユーザーは変更されたデータにアクセスできず、表示されるのはトランザクション開始前の状態のデータです。COMMIT文は、ホスト変数の値にも、プログラムの制御フローにも影響はありません。具体的には、COMMIT文により次の処理が実行されます。
-
これらの変更を他のユーザーが参照できるようにします。
-
すべてのセーブポイントを消去します(ROLLBACK文の使用についてを参照)。
-
解析ロック以外の行および表のロックをすべて解除します。
-
CURRENT OF句で参照されるカーソルをクローズします。
MODE={ANSI13|ORACLE}の場合は、すべての明示カーソルをクローズします。 -
トランザクションを終了します。
MODE={ANSI13|ORACLE}の場合、CURRENT OF句で参照されていない明示カーソルは、複数のコミットにわたりオープン状態のままです。これによってパフォーマンスが向上します。たとえば、複数のコミットにわたるフェッチについてを参照してください。
これらは通常の処理の一部であるため、COMMIT文は、プログラムのメイン・パスにインラインで設定する必要があります。プログラムを終了する前に、保留中の変更を明示的にコミットしてください。コミットしない場合、保留中の変更はロールバックされます。次の例では、トランザクションをコミットして、Oracleとの接続を切断します。
EXEC SQL COMMIT WORK RELEASE;
オプションのキーワードWORKには、ANSI互換性があります。RELEASEオプションを指定すると、プログラムで使用されているOracleリソース(ロックとカーソル)がすべて解放され、データベースとの接続が切断されます。
データ定義文では、実行の前後に自動コミットが発行されるため、データ定義文の後にCOMMIT文を記述する必要はありません。したがってデータ定義文が正常終了しても異常終了しても、その前のトランザクションがコミットされます。
7.5 ROLLBACK文の使用について
データベースに加えられ保留中の変更を取り消すには、ROLLBACK文を使用します。たとえば、表から間違った行を削除するなどのミスを犯した場合、ROLLBACKを使用すれば、元のデータをリストアできます。ROLLBACK文は、ホスト変数の値にも、プログラムのフロー制御にも影響がありません。具体的には、ROLLBACK文により次の処理が実行されます。
-
カレント・トランザクションで実行されたデータベースの変更を取り消します。
-
すべてのセーブポイントを消去します。
-
トランザクションを終了します。
-
解析ロックを除き、すべての行と表のロックを解除します。
-
CURRENT OF句で参照されるカーソルをクローズします。
MODE={ANSI|ANSI14}の場合は、すべての明示カーソルをクローズします。
MODE={ANSI13|ORACLE}の場合、CURRENT OF句で参照されない明示カーソルは、複数のロールバックにわたりオープン状態のままです。
ROLLBACK文は例外処理の一部であるため、プログラムのメイン・パスではなく、エラー処理ルーチン内に指定する必要があります。次の例では、トランザクションをロールバックして、Oracleとの接続を切断します。
EXEC SQL ROLLBACK WORK RELEASE;
オプションのキーワードWORKを指定すると、ANSI互換になります。RELEASEオプションを指定すると、プログラムで使用されているすべてのリソースが解放され、データベースとの接続が切断されます。
WHENEVER SQLERROR GOTO文からエラー処理ルーチンに分岐するとき、そのルーチンにROLLBACK文が含まれている場合は、ロールバックがエラーで失敗すると、プログラムが無限ループに入るおそれがあります。このような無限ループは、ROLLBACK文の前にWHENEVER SQLERROR CONTINUEを記述することで回避できます。
EXEC SQL WHENEVER SQLERROR GOTO sql_error; FOR EACH new employee display 'Employee number? '; read emp_number; display 'Employee name? '; read emp_name; EXEC SQL INSERT INTO EMP (EMPNO, ENAME) VALUES (:emp_number, :emp_name); ENDFOR; ... sql_error: EXEC SQL WHENEVER SQLERROR CONTINUE; EXEC SQL ROLLBACK WORK RELEASE; display 'Processing error'; exit program with an error;
7.5.1 文レベルのロールバック
Oracleは、SQL文を実行する前に、暗黙的なセーブポイント(ユーザーは操作できません)を設定します。SQL文でエラーが発生すると、Oracleは自動的にその文をロールバックし、該当するエラー・コードをSQLCA内のSQLCODEに戻します。たとえば、INSERT文で一意の索引に重複する値を挿入しようとしてエラーが発生すると、その文はロールバックされます。
失敗したSQL文によって開始された作業のみが失われます。現在のトランザクションでその文の前に行われた作業は保持されます。このため、データ定義文が失敗した場合、それに先行する自動コミットは取り消されません。
SQL文を実行する前に、Oracleではその文を解析する、つまり、その文が構文規則に従っているか、あるいは有効なデータベース・オブジェクトを参照しているかを確認する要があります。SQL文の実行中にエラーが検出されると、ロールバックが発生しますが、解析中にエラーが検出されても、ロールバックは発生しません。
Oracleでは、デッドロックを解消するために、1つのSQL文のみをロールバックすることもできます。関連しているトランザクションの1つにエラーが通知され、そのトランザクション内の現在の文がロールバックされます。
7.6 SAVEPOINT文の使用について
トランザクションの処理中に現在のポイントにマークを付け、名前を指定するには、SAVEPOINT文を使用します。マークを設定したそれぞれの点をセーブポイントと呼びます。たとえば、次の文では、start_deleteというセーブポイントを設定しています。
EXEC SQL SAVEPOINT start_delete;
セーブポイントを設定すると、長いトランザクションを分割できるため、複雑なプロシージャでもうまく制御できます。たとえば、単一のトランザクションが複数のファンクションを実行しているときに、それぞれのファンクションの前にセーブポイントを設定できます。その結果、1つのファンクションが失敗しても、簡単にOracleデータを前の状態にリストアし、リカバリしてから、ファンクションを再実行できます。
トランザクションの一部を取り消すには、セーブポイントをROLLBACK文とそのTO SAVEPOINT句とともに使用します。TO SAVEPOINT句を使用すると、現行のトランザクションの途中の文までロールバックできるため、変更をすべて取り消す必要はありません。具体的には、ROLLBACK TO SAVEPOINT文により次の処理が実行されます。
-
指定したセーブポイントがマークされた以降のデータベースへの変更を取り消します。
-
指定したセーブポイント以降のセーブポイントをすべて消去します。
-
指定したセーブポイントがマークされた以降に取得された行および表のロックをすべて解除します。
次の例では、MAIL_LIST表にアクセスして新しいリストを挿入し、古いリストを更新して、アクティブでない(少数の)リストを削除しています。削除後、SQLCAのSQLERRD(3)をチェックして、削除された行数を調べます。行数が予想以上に多い場合は、セーブポイントstart_deleteまでロールバックして、その削除のみを取り消します。
FOR EACH new customer display 'Customer number? '; read cust_number; display 'Customer name? '; read cust_name; EXEC SQL INSERT INTO MAIL_LIST (CUSTNO, CNAME, STAT) VALUES (:cust_number, :cust_name, 'ACTIVE'); ENDFOR; FOR EACH revised status display 'Customer number? '; read cust_number; display 'New status? '; read new_status; EXEC SQL UPDATE MAIL_LIST SET STAT = :new_status WHERE CUSTNO = :cust_number; ENDFOR; -- mark savepoint EXEC SQL SAVEPOINT start_delete; EXEC SQL DELETE FROM MAIL_LIST WHERE STAT = 'INACTIVE'; IF sqlca.sqlerrd(3) < 25 THEN -- check number of rows deleted display 'Number of rows deleted is ', sqlca.sqlerrd(3); ELSE display 'Undoing deletion of ', sqlca.sqlerrd(3), ' rows'; EXEC SQL WHENEVER SQLERROR GOTO sql_error; EXEC SQL ROLLBACK TO SAVEPOINT start_delete; ENDIF; EXEC SQL WHENEVER SQLERROR CONTINUE; EXEC SQL COMMIT WORK RELEASE; exit program; sql_error: EXEC SQL WHENEVER SQLERROR CONTINUE; EXEC SQL ROLLBACK WORK RELEASE; display 'Processing error'; exit program with an error;
ROLLBACK TO SAVEPOINT文では、RELEASEオプションを指定できないことに注意してください。
あるセーブポイントまでロールバックすると、そのセーブポイント以降に設定されたセーブポイントはすべて消去されます。ただし、ロールバックしたセーブポイントはそのまま残ります。たとえば、5つのセーブポイントを設定しているときに3番目のセーブポイントまでロールバックすると、4番目と5番目のセーブポイントのみ消去されます。COMMIT文またはROLLBACK文では、すべてのセーブポイントが消去されます。
デフォルトでは、ユーザー・セッションごとのアクティブなセーブポイントの数は、5つに制限されています。アクティブなセーブポイントとは、最後のコミットまたはロールバック以降に設定されたセーブポイントです。データベース管理者(DBA)は、Oracleの初期化パラメータSAVEPOINTSの値を増やして、この制限を引き上げることができます。2つのセーブポイントに同じ名前を付けると、最初のセーブポイントが消去されます。
7.7 RELEASEオプションの使用について
プログラムが異常終了すると、Oracleは自動的に変更をロールバックします。異常終了が発生するのは、プログラムが作業を明示的にコミットもロールバックもせずに、RELEASEオプションを使用してOracleから切断する場合です。
プログラムが正常に終了するのは、プログラムが正常に実行され、オープン状態のカーソルがクローズされ、作業が明示的にコミットまたはロールバックされて、Oracleとの接続が切断され、制御がユーザーに戻された場合です。最後に実行されるSQL文が次のどちらかの場合、プログラムは正常終了します。
EXEC SQL COMMIT RELEASE;
または
EXEC SQL ROLLBACK RELEASE;
それ以外の場合は、ユーザー・セッションで取得したロックおよびカーソルは、そのユーザー・セッションがアクティブでなくなったことがOracleで認識されるまで、プログラムの終了後も保持されます。このため、マルチユーザー環境では、他のユーザーがロックされたリソースが解除されるまで必要以上に長く待たされることになりかねません。
7.8 SET TRANSACTION文の使用について
SET TRANSACTION文を使用すると、読取り専用または読取り/書込みトランザクションを開始したり、現行のトランザクションを指定したロールバック・セグメントに割り当てたりできます。読取り専用トランザクションは、COMMIT文、ROLLBACK文またはデータ定義文で終了します。
読取り専用トランザクションでは反復可能読取りが行えるため、他のユーザーが更新中の1つ以上の表に対して、複数の問合せを実行する場合に便利です。読取り専用トランザクション中、複数の表と複数の問合せで構成された読取り一貫性ビューが作成され、すべての問合せがデータベースの同じスナップショットを参照します。他のユーザーは、通常の方法でデータの問合せや更新ができます。次にSET TRANSACTION文の例を示します。
EXEC SQL SET TRANSACTION READ ONLY;
SET TRANSACTION文は、読取り専用トランザクションの最初のSQL文であることが必要で、1つのトランザクションで1回しか使用できません。READ ONLYパラメータは必須です。これを使用しても、他のトランザクションには影響がありません。読取り専用トランザクションで使用できるのは、SELECT文(FOR UPDATEなし)、LOCK TABLE文、 SET ROLE文、ALTER SESSION文、ALTER SYSTEM文、COMMIT文およびROLLBACK文のみです。
次の例では、店の管理者として、読取り専用トランザクションを使用して要約レポートを生成することで、当日、先週および先月の売り上げを調べます。トランザクションの途中で、他のユーザーがデータベースを更新しても、レポートには影響はありません。
EXEC SQL SET TRANSACTION READ ONLY; EXEC SQL SELECT SUM(SALEAMT) INTO :daily FROM SALES WHERE SALEDATE = SYSDATE; EXEC SQL SELECT SUM(SALEAMT) INTO :weekly FROM SALES WHERE SALEDATE > SYSDATE - 7; EXEC SQL SELECT SUM(SALEAMT) INTO :monthly FROM SALES WHERE SALEDATE > SYSDATE - 30; EXEC SQL COMMIT WORK; -- simply ends the transaction since there are no changes -- to make permanent -- format and print report
7.9 デフォルト・ロックのオーバーライドについて
デフォルトでは、多数のデータ構造がOracleにより暗黙的(自動的)にロックされます。ただし、デフォルトのロックをオーバーライドして、別のロックを有効にする場合は、行または表を特定して、そこにデータ・ロックを要求できます。明示的なロックにより、トランザクション中に表に対するアクセスを共有または制限したり、複数の表および複数の問合せの読取り一貫性を確保できます。
SELECT FOR UPDATE OF文を使用すると、表の特定行を明示的にロックすることで、更新または削除が実行されるまで、その行が変更されないようにできます。ただし、Oracleでは、更新時または削除時には自動的に行レベルのロックが行われます。したがって、更新または削除の前にロックする場合にのみ、FOR UPDATE OF句を使用してください。
LOCK TABLE文を使用すると、表全体をロックできます。
7.9.1 FOR UPDATE OF句の使用について
UPDATE文またはDELETE文のCURRENT OF句で参照されるカーソルをDECLAREする場合は、 FOR UPDATE OF句を使用すると行の排他的ロックを取得できます。SELECT FOR UPDATE OF文では、更新または削除の対象となる行が識別され、アクティブ・セット内の各行がロックされます。(行はすべて、オープン時にロックされ、フェッチ時にはロックされません。)これは、ある行内の既存の値に基づいて更新処理を行う場合に便利です。更新前に、その行が他のユーザーにより変更されないようにする必要があります。
FOR UPDATE OF句はオプションです。たとえば、次のようなコードがあるとします。
EXEC SQL DECLARE emp_cursor CURSOR FOR SELECT ENAME, JOB, SAL FROM EMP WHERE DEPTNO = 20 FOR UPDATE OF SAL;
ここでFOR UPDATE OF句を削除すると、次のようにコードが単純になります。
EXEC SQL DECLARE emp_cursor CURSOR FOR SELECT ENAME, JOB, SAL FROM EMP WHERE DEPTNO = 20;
CURRENT OF句では、必要に応じてFOR UPDATE句を追加するようにプリコンパイラに指示します。CURRENT OF句を使用して、カーソルから最後にフェッチされた行を参照します。
7.9.3 LOCK TABLE文の使用について
LOCK TABLE文を使用すると、指定したロック・モードで1つ以上の表をロックできます。たとえば、文はEMP表を行共有モードにロックします。行共有ロックでは、表への同時アクセスが可能です。他のユーザーが表全体をロックして排他使用することはできません。
EXEC SQL LOCK TABLE EMP IN ROW SHARE MODE NOWAIT;
ロック・モードによって、表に対して他にどのようなロックを使用できるかが決まります。たとえば、多くのユーザーが同時に1つの表に対して行共有ロックを取得できる一方、排他ロックを取得できるのは一度に1ユーザーのみです。あるユーザーが表を排他ロックしている間は、他のユーザーはその表内の行の挿入、更新または削除を行えません。ロック・モードの詳細は、「アプリケーション開発者用のSQL処理」を参照してください。
オプションのキーワードNOWAITを指定すると、他のユーザーが表をロックしている場合は、その表の解放を待機しないようにOracleに対して指示できます。制御はただちにプログラムに戻されるため、プログラムではロックの取得を再度試みるまでの間に別の作業ができます。(SQLCA内のSQLCODEをチェックすると、表ロックが失敗したか確認できます。)NOWAITを省略すると、表が利用可能になるまで、Oracleは待機します。待機の時間制限は設定されていません。
表をロックしても、他のユーザーは表に対して問合せができますが、問合せを実行しても表ロックを取得できません。したがって、問合せが他の問合せや更新を妨げることはなく、更新が問合せを妨げることもありません。2つの異なるトランザクションで同じ行の更新が試みられる場合にのみ、一方のトランザクションが他方のトランザクションの完了まで待機の状態になります。表のロックは、トランザクションがコミットまたはロールバックを発行すると解除されます。
7.10 複数のコミットにわたるフェッチについて
複数のコミットとフェッチを併用する場合は、CURRENT OF句を使用しないでください。かわりに、各行のROWIDを選択してから、その値を使用して、更新または削除中の現在の行を識別します。次の例を検討してください:
EXEC SQL DECLARE emp_cursor CURSOR FOR SELECT ENAME, SAL, ROWID FROM EMP WHERE JOB = 'CLERK'; ... EXEC SQL OPEN emp_cursor; EXEC SQL WHENEVER NOT FOUND GOTO ... LOOP EXEC SQL FETCH emp_cursor INTO :emp_name, :salary, :row_id; ... EXEC SQL UPDATE EMP SET SAL = :new_salary WHERE ROWID = :row_id; EXEC SQL COMMIT; ENDLOOP;
ノート:
フェッチされた行はロックされません。つまり、ある行を読み取っても、その行を更新または削除する前に別のユーザーがその行を変更すると、結果が一貫性のないものになる可能性があります。
7.11 分散トランザクションの処理について
分散データベースとは、異なるノード上の複数の物理データベースで構成される単一の論理データベースです。分散型の文とは、データベース・リンクによってリモート・ノードにアクセスする任意のSQL文です。分散トランザクションには、分散データベースの複数のノードでデータを更新するための分散型の文が、1つ以上設定されています。その更新が1つのノードのみに影響するときは、そのトランザクションは分散型ではありません。
コミットを発行すると、分散トランザクションによる影響を受ける各データベースの変更が確定されます。コミットのかわりにロールバックを発行すると、すべての変更が取り消されます。ただし、コミットまたはロールバック中にネットワークやコンピュータで障害が発生すると、分散トランザクションの状態は不明またはインダウトになることがあります。そのような場合、FORCE TRANSACTIONシステム権限があれば、FORCE句を使用して、ローカル・データベースでトランザクションを手動でコミットまたはロールバックできます。このトランザクションは、データ・ディクショナリ・ビューDBA_2PC_PENDINGにあるトランザクションIDを引用符で囲んだリテラルで指定する必要があります。次に例を示します。
EXEC SQL COMMIT FORCE '22.31.83'; ... EXEC SQL ROLLBACK FORCE '25.33.86';
FORCEでは、指定したトランザクションのみがコミットまたはロールバックされるため、現行のトランザクションには影響がありません。インダウトのトランザクションは、手動ではセーブポイントにロールバックできないことに注意してください。
COMMIT文中でCOMMENT句を使用すると、分散トランザクションに関連付けるコメントを指定できます。トランザクションがインダウトになると、COMMENTで指定したテキストがトランザクションIDとともに、データ・ディクショナリ・ビューDBA_2PC_PENDINGに格納されます。テキストには、長さ50文字以内の引用符付きリテラルを指定する必要があります。次に例を示します。
EXEC SQL COMMIT COMMENT 'In-doubt trans; notify Order Entry';
分散トランザクションの詳細は、トランザクションを参照してください。
7.12 ガイドライン
7.12.1 アプリケーションの設計について
アプリケーションを設計するときは、論理的に関連する処理を1つのトランザクション内にグループ化してください。適切に設計されたトランザクションには、特定のタスクを完了するために必要なすべてのステップが過不足なく含まれています。
表を参照するデータは一貫している必要があります。したがって、トランザクション内のSQL文は、一貫した方法でデータを変更する必要があります。たとえば2種類の銀行預金口座間の資金の送金取引の場合は、一方の口座に対する借方記帳および他方の口座に対する貸方記帳の処理がトランザクションに含まれている必要があります。どちらの処理も、正常終了または失敗が同時であることが必要です。一方の口座への新規預金など、関連のない更新はトランザクションに含めないでください。