トランザクションの処理および制御
トランザクション処理はOracle Databaseの機能で、複数のユーザーがデータベース上で同時に作業することができ、各ユーザーが参照するデータのバージョンに一貫性があり、すべての変更が正しい順序で適用されることが保証されます。
トランザクションは、Oracle Databaseが1つの単位として処理する連続した1つ以上のSQL文です(これらの文は、すべて実行されるか、1つも実行されないかのいずれかです)。
Oracle Databaseによってデータ構造が自動的にロックされるため、異なるユーザーが、相互のデータに悪影響を与えたりデータを同調させることなく、同じデータ構造に書込みできます。データの可用性を最大にするため、Oracle Databaseでは必要最小限のデータが最小限の時間のみロックされます。
複数のユーザーがデータに同時にアクセスすることによって発生する問題を防ぐために、追加のコードを記述する必要はほとんどありません。ただし、このレベルの制御が必要な場合は、Oracle Databaseのデフォルトのロック・メカニズムを手動で上書きできます。
ここでのトピック
関連項目:
-
トランザクションの詳細は、『Oracle Database概要』を参照してください。
-
トランザクション処理の詳細は、『Oracle Database概要』を参照してください
-
手動データ・ロックの詳細は、『Oracle Database概要』を参照してください
COMMIT文
COMMIT
文は、カレント・トランザクションを終了し、その変更を永続的なものにして、他のユーザーから参照できるようにします。
ノート:
トランザクションは複数のブロックにまたがることができ、ブロックは複数のトランザクションを含むことができます。
COMMIT
文のWRITE
句では、コミット操作によって生成される情報をOracle DatabaseがREDOログに書き込む場合の優先順位を指定します。
ノート:
データベース初期化パラメータCOMMIT_LOGGING
およびCOMMIT_WAIT
が設定されていない場合、非分散トランザクションに対するPL/SQLのデフォルトのコミット動作はBATCH
NOWAIT
です。
関連項目:
-
トランザクションのコミットの詳細は、『Oracle Database概要』を参照してください。
-
分散トランザクションの詳細は、『Oracle Database概要』を参照してください。
-
COMMIT
文の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。 -
スタンバイ・データベースへのフェイルオーバー中にデータを消失させないようにする方法については、『Oracle Data Guard概要および管理』を参照してください。
例7-36 COMMENT句およびWRITE句を使用するCOMMIT文
この例では、トランザクションによって銀行口座の間で振替えを実行しています。一方の口座から現金が出金され、同時に他方の口座に現金が入金されることが重要であるため、COMMIT
WRITE
IMMEDIATE
NOWAIT
文を使用しています。
DROP TABLE accounts; CREATE TABLE accounts ( account_id NUMBER(6), balance NUMBER (10,2) ); INSERT INTO accounts (account_id, balance) VALUES (7715, 6350.00); INSERT INTO accounts (account_id, balance) VALUES (7720, 5100.50); CREATE OR REPLACE PROCEDURE transfer ( from_acct NUMBER, to_acct NUMBER, amount NUMBER ) AUTHID CURRENT_USER AS BEGIN UPDATE accounts SET balance = balance - amount WHERE account_id = from_acct; UPDATE accounts SET balance = balance + amount WHERE account_id = to_acct; COMMIT WRITE IMMEDIATE NOWAIT; END; /
振替え前の問合せ:
SELECT * FROM accounts;
結果:
ACCOUNT_ID BALANCE ---------- ---------- 7715 6350 7720 5100.5 BEGIN transfer(7715, 7720, 250); END; /
振替え後の問合せ:
SELECT * FROM accounts;
結果:
ACCOUNT_ID BALANCE ---------- ---------- 7715 6100 7720 5350.5
ROLLBACK文
ROLLBACK
文は、カレント・トランザクションを終了し、トランザクション中に加えられたすべての変更を取り消します。
表からの間違った行の削除などの誤操作を行った場合に、ロールバックは元のデータをリストアできます。SQL文が失敗したりPL/SQLが例外を呼び出したためにトランザクションを終了できない場合は、ロールバックを使用すると、対処措置を行い、実行しなおすことができます。
関連項目:
ROLLBACK
文の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
例7-37 ROLLBACK文
この例では、3つの異なる表に従業員に関する情報を挿入しています。INSERT
文で重複する従業員番号を格納すると、PL/SQLによって事前定義の例外DUP_VAL_ON_INDEX
が呼び出されます。3つの表すべてに対する変更が確実に取り消されるようにするため、例外ハンドラがROLLBACK
を実行します。
DROP TABLE emp_name; CREATE TABLE emp_name AS SELECT employee_id, last_name FROM employees; CREATE UNIQUE INDEX empname_ix ON emp_name (employee_id); DROP TABLE emp_sal; CREATE TABLE emp_sal AS SELECT employee_id, salary FROM employees; CREATE UNIQUE INDEX empsal_ix ON emp_sal (employee_id); DROP TABLE emp_job; CREATE TABLE emp_job AS SELECT employee_id, job_id FROM employees; CREATE UNIQUE INDEX empjobid_ix ON emp_job (employee_id); DECLARE emp_id NUMBER(6); emp_lastname VARCHAR2(25); emp_salary NUMBER(8,2); emp_jobid VARCHAR2(10); BEGIN SELECT employee_id, last_name, salary, job_id INTO emp_id, emp_lastname, emp_salary, emp_jobid FROM employees WHERE employee_id = 120; INSERT INTO emp_name (employee_id, last_name) VALUES (emp_id, emp_lastname); INSERT INTO emp_sal (employee_id, salary) VALUES (emp_id, emp_salary); INSERT INTO emp_job (employee_id, job_id) VALUES (emp_id, emp_jobid); EXCEPTION WHEN DUP_VAL_ON_INDEX THEN ROLLBACK; DBMS_OUTPUT.PUT_LINE('Inserts were rolled back'); END; /
SAVEPOINT文
SAVEPOINT
文は、トランザクション処理内の現在位置に名前とマークを付けます。
セーブポイントを使用すると、トランザクション全体をロールバックするのではなく、トランザクションの一部をロールバックできます。セッションごとのアクティブなセーブポイントの数には、制限がありません。
あるセーブポイントまでロールバックすると、そのセーブポイント以降にマークされたセーブポイントはすべて消去されます。ロールバック先のセーブポイントは消去されません。単純なロールバックまたはコミットではすべてのセーブポイントが消去されます。
再帰的サブプログラムの中でセーブポイントをマークすると、再帰しながら進む過程で、各レベルでSAVEPOINT
文の新しいインスタンスが実行されます。ただし、ロールバックできるのは直前にマークされたセーブポイントまでです。
セーブポイント名は未宣言の識別子です。トランザクションでセーブポイント名を再利用すると、セーブポイントはトランザクションの中の古い位置から現在の位置に移動します(つまり、セーブポイントへのロールバックは、トランザクションの現在の部分のみに影響を与えます)。
関連項目:
SQLのSET
TRANSACTION
文の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
例7-38 SAVEPOINT文およびROLLBACK文
この例では、挿入する前にセーブポイントをマークしています。INSERT
文でemployee_id
列に重複した値を格納しようとすると、PL/SQLによって事前定義の例外DUP_VAL_ON_INDEX
が呼び出され、トランザクションがセーブポイントまでロールバックして、INSERT
文のみを取り消します。
DROP TABLE emp_name; CREATE TABLE emp_name AS SELECT employee_id, last_name, salary FROM employees; CREATE UNIQUE INDEX empname_ix ON emp_name (employee_id); DECLARE emp_id employees.employee_id%TYPE; emp_lastname employees.last_name%TYPE; emp_salary employees.salary%TYPE; BEGIN SELECT employee_id, last_name, salary INTO emp_id, emp_lastname, emp_salary FROM employees WHERE employee_id = 120; UPDATE emp_name SET salary = salary * 1.1 WHERE employee_id = emp_id; DELETE FROM emp_name WHERE employee_id = 130; SAVEPOINT do_insert; INSERT INTO emp_name (employee_id, last_name, salary) VALUES (emp_id, emp_lastname, emp_salary); EXCEPTION WHEN DUP_VAL_ON_INDEX THEN ROLLBACK TO do_insert; DBMS_OUTPUT.PUT_LINE('Insert was rolled back'); END; /
例7-39 ROLLBACKでのSAVEPOINTの再使用
DROP TABLE emp_name; CREATE TABLE emp_name AS SELECT employee_id, last_name, salary FROM employees; CREATE UNIQUE INDEX empname_ix ON emp_name (employee_id); DECLARE emp_id employees.employee_id%TYPE; emp_lastname employees.last_name%TYPE; emp_salary employees.salary%TYPE; BEGIN SELECT employee_id, last_name, salary INTO emp_id, emp_lastname, emp_salary FROM employees WHERE employee_id = 120; SAVEPOINT my_savepoint; UPDATE emp_name SET salary = salary * 1.1 WHERE employee_id = emp_id; DELETE FROM emp_name WHERE employee_id = 130; SAVEPOINT my_savepoint; INSERT INTO emp_name (employee_id, last_name, salary) VALUES (emp_id, emp_lastname, emp_salary); EXCEPTION WHEN DUP_VAL_ON_INDEX THEN ROLLBACK TO my_savepoint; DBMS_OUTPUT.PUT_LINE('Transaction rolled back.'); END; /
暗黙的なロールバック
INSERT
文、UPDATE
文、DELETE
文またはMERGE
文の実行前に、データベースによって(ユーザーが利用できない)暗黙的なセーブポイントがマークされます。文が正常に実行されなかった場合は、データベースによってこのセーブポイントまでロールバックが行われます。
通常は、トランザクション全体ではなく、失敗したSQL文のみがロールバックされます。その文が原因で未処理例外が呼び出された場合は、ホスト環境によってロールバックの対象が決まります。
データベースでは、デッドロックを解消するためにSQL文を1文のみロールバックすることもできます。データベースによって、関係しているトランザクションにエラーが戻され、そのトランザクション中の現在の文がロールバックされます。
SQL文を実行する前に、データベースはその文を解析する必要があります(すなわち、その文が構文規則に従っているかどうかや、有効なスキーマ・オブジェクトを参照しているかどうかを確認する必要があります)。SQL文の実行中にエラーが検出されると、ロールバックが発生しますが、解析中にエラーが検出されても、ロールバックは発生しません。
ストアド・サブプログラムを未処理例外で終了すると、PL/SQLは値をOUT
パラメータに代入せず、ロールバックを実行しません。
例外処理の詳細は、「PL/SQLのエラー処理」を参照してください
SET TRANSACTION文
SET
TRANSACTION
文を使用すると、読取り専用または読取り/書込みトランザクションの開始、分離レベルの確立、指定したロールバック・セグメントへのカレント・トランザクションの代入を実行できます。
読取り専用トランザクションは、他のユーザーが更新している表に対して複数の問合せを実行する場合に便利です。
読取り専用トランザクションでは、複数の表と複数の問合せで構成された読取り一貫性のあるビューが作成され、すべての問合せがデータベースの同一のスナップショットを参照します。他のユーザーは、通常の方法でデータの問合せや更新ができます。コミットまたはロールバックするとトランザクションが終了します。
SET
TRANSACTION
文は、読取り専用トランザクションの最初のSQL文である必要があり、1つのトランザクションで1回しか使用できません。トランザクションをREAD
ONLY
に設定すると、それ以降の問合せからはトランザクションの開始前にコミットされた変更内容しか見えません。READ
ONLY
を使用しても、他のユーザーや他のトランザクションには影響がありません。
読取り専用トランザクションに使用できるのは、SELECT
、OPEN
、FETCH
、CLOSE
、LOCK
TABLE
、COMMIT
およびROLLBACK
文のみです。問合せはFOR
UPDATE
にはできません。
関連項目:
SQLのSET
TRANSACTION
文の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
例7-40 読取り専用トランザクションでのSET TRANSACTION文
この例では、読取り専用トランザクションによって、当日、先週および先月の注文総数を収集しています。トランザクションの途中で他のユーザーがデータベースを更新しても、注文総数には影響がありません。orders
表は、サンプル・スキーマOEにあります。
DECLARE daily_order_total NUMBER(12,2); weekly_order_total NUMBER(12,2); monthly_order_total NUMBER(12,2); BEGIN COMMIT; -- end previous transaction SET TRANSACTION READ ONLY NAME 'Calculate Order Totals'; SELECT SUM (order_total) INTO daily_order_total FROM orders WHERE order_date = SYSDATE; SELECT SUM (order_total) INTO weekly_order_total FROM orders WHERE order_date = SYSDATE - 7; SELECT SUM (order_total) INTO monthly_order_total FROM orders WHERE order_date = SYSDATE - 30; COMMIT; -- ends read-only transaction END; /
デフォルトのロックの上書き
デフォルトでは、Oracle Databaseによってデータ構造が自動的にロックされるため、異なるアプリケーションが、相互のデータに悪影響を与えたりデータを同調させることなく、同じデータ構造に書込みできます。
トランザクションの間にデータへの排他アクセスが必要な場合、次のSQL文を使用してデフォルトのロックを上書きできます。
-
LOCK
TABLE
は、表全体を明示的にロックします。 -
SELECT
をFOR
UPDATE
句とともに使用すると(SELECT
FOR
UPDATE
)、表の特定の行を明示的にロックできます。
ここでのトピック
LOCK TABLE文
LOCK
TABLE
文は、1つ以上の表を指定されたロック・モードで明示的にロックするため、それらの表へのアクセスを共有または拒否できます。
ロック・モードによって、その表に設定できる他のロックが決定されます。たとえば、1つの表に対して多くのユーザーが同時に行共用ロックを取得できますが、排他ロックを取得できるのは一度に1人のユーザーのみです。あるユーザーが表に対して排他ロックをかけていると、他のユーザーはその表に対して行の挿入、更新、削除を実行できません。
表がロックされていても、他のユーザーは表に対して問合せできますが、問合せを実行しても表のロックを取得できません。2つの異なるトランザクションが同じ行を変更した場合のみ、一方のトランザクションがもう一方のトランザクションの終了を待ちます。LOCK
TABLE
文を使用すると、別のトランザクションが完了するまでの待機時間を指定できます。
表ロックは、それらを取得したトランザクションがコミットまたはロールバックされる際に解除されます。
関連項目:
-
表の明示的なロックの詳細は、『Oracle Database開発ガイド』を参照してください。
-
LOCK
TABLE
文の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
SELECT FOR UPDATEカーソルとFOR UPDATEカーソル
FOR
UPDATE
句を伴うSELECT
文(SELECT
FOR
UPDATE
文)では、結果セットの行が選択され、それらがロックされます。SELECT
FOR
UPDATE
を使用すると、行の中の既存の値に基づいて更新できます(これは、更新前に、他のユーザーがそれらの値を変更できないことが保証されるためです)。また、SELECT
FOR
UPDATE
を使用すると、例10-6のように、更新する必要のない行をロックできます。
ノート:
ハイブリッド列圧縮(HCC)で圧縮された表では、DML文によって行ではなく圧縮単位がロックされます。特定のOracleストレージ・システムの機能であるHCCの詳細は、『Oracle Database概要』を参照してください。
デフォルトでは、要求された行ロックが取得されるまでSELECT
FOR
UPDATE
文は待機します。この動作を変更するには、SELECT
FOR
UPDATE
文でNOWAIT
、WAIT
、またはSKIP
LOCKED
句を使用します。このような句の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
SELECT
FOR
UPDATE
が明示カーソルと関連付けられる場合、そのカーソルはFOR
UPDATE
カーソルと呼ばれます。UPDATE
文またはDELETE
文のCURRENT
OF
句には、FOR
UPDATE
カーソルのみを使用できます。(CURRENT
OF
句は、SQL文UPDATE
およびDELETE
のWHERE
句に対するPL/SQLの拡張機能であり、文をカーソルの現在の行に制限します。)
SELECT
FOR
UPDATE
で複数の表の問合せを実行する場合、列がFOR
UPDATE
句に現れる行のみがロックされます。
ROWID擬似列を使用したCURRENT OF句のシミュレート
結果セットの行は、FOR
UPDATE
カーソルをオープンするときにロックされるのであり、フェッチされるときにロックされるのではありません。また、トランザクションをコミットまたはロールバックすると、行のロックは解除されます。例7-41に示すように、行のロックが解除された後は、FOR
UPDATE
カーソルからはフェッチできません(ROLLBACK
とCOMMIT
を入れ替えても結果は同じです)。
回避策は、ROWID
擬似列を使用してCURRENT OF
句をシミュレートすることです(『Oracle Database SQL言語リファレンス』を参照)。各行のROWIDを選択して、UROWID
変数に入れ、更新や削除のときに、ROWIDを使用して現在行を識別します(例7-42を参照)。(UROWID
変数の値を出力するには、『Oracle Database SQL言語リファレンス』で説明されているROWIDTOCHAR
ファンクションを使用してその値をVARCHAR2
に変換します。)
ノート:
ハイブリッド列圧縮(HCC)で圧縮された表の行を更新すると、行のROWID
が変更されます。特定のOracleストレージ・システムの機能であるHCCの詳細は、『Oracle Database概要』を参照してください。
注意:
フェッチされた行は、FOR
UPDATE
句によってロックされないため、他のユーザーによって意識せずに変更内容が上書きされる可能性があります。
ノート:
読取り一貫性のために必要な追加領域は、カーソルがクローズされるまで解放されません。そのため、大規模な更新では処理速度が低下する場合があります。
例7-41 COMMIT文の後でのFOR UPDATEカーソルによるフェッチ
DROP TABLE emp; CREATE TABLE emp AS SELECT * FROM employees; DECLARE CURSOR c1 IS SELECT * FROM emp FOR UPDATE OF salary ORDER BY employee_id; emp_rec emp%ROWTYPE; BEGIN OPEN c1; LOOP FETCH c1 INTO emp_rec; -- fails on second iteration EXIT WHEN c1%NOTFOUND; DBMS_OUTPUT.PUT_LINE ( 'emp_rec.employee_id = ' || TO_CHAR(emp_rec.employee_id) ); UPDATE emp SET salary = salary * 1.05 WHERE employee_id = 105; COMMIT; -- releases locks END LOOP; END; /
結果:
emp_rec.employee_id = 100 DECLARE * ERROR at line 1: ORA-01002: fetch out of sequence ORA-06512: at line 11
例7-42 ROWID擬似列を使用したCURRENT OF句のシミュレート
DROP TABLE emp; CREATE TABLE emp AS SELECT * FROM employees; DECLARE CURSOR c1 IS SELECT last_name, job_id, rowid FROM emp; -- no FOR UPDATE clause my_lastname employees.last_name%TYPE; my_jobid employees.job_id%TYPE; my_rowid UROWID; BEGIN OPEN c1; LOOP FETCH c1 INTO my_lastname, my_jobid, my_rowid; EXIT WHEN c1%NOTFOUND; UPDATE emp SET salary = salary * 1.02 WHERE rowid = my_rowid; -- simulates WHERE CURRENT OF c1 COMMIT; END LOOP; CLOSE c1; END; /