6.6 トランザクションの処理および制御

トランザクション処理はOracle Databaseの機能で、複数のユーザーがデータベース上で同時に作業することができ、各ユーザーが参照するデータのバージョンに一貫性があり、すべての変更が正しい順序で適用されることが保証されます。

トランザクションは、Oracle Databaseが1つの単位として処理する連続した1つ以上のSQL文です(これらの文は、すべて実行されるか、1つも実行されないかのいずれかです)。

Oracle Databaseによってデータ構造が自動的にロックされるため、異なるユーザーが、相互のデータに悪影響を与えたりデータを同調させることなく、同じデータ構造に書込みできます。データの可用性を最大にするため、Oracle Databaseでは必要最小限のデータが最小限の時間のみロックされます。

複数のユーザーがデータに同時にアクセスすることによって発生する問題を防ぐために、追加のコードを記述する必要はほとんどありません。ただし、このレベルの制御が必要な場合は、Oracle Databaseのデフォルトのロック・メカニズムを手動で上書きできます。

ここでのトピック

• COMMIT文

• ROLLBACK文

• SAVEPOINT文

• 暗黙的なロールバック

• SET TRANSACTION文

• デフォルトのロックの上書き

関連項目:

• トランザクションの詳細は、『Oracle Database概要』を参照してください。

• トランザクション処理の詳細は、『Oracle Database概要』を参照してください

• Oracle Databaseのロック・メカニズムの詳細は、『Oracle Database概要』を参照してください

• 手動データ・ロックの詳細は、『Oracle Database概要』を参照してください

6.6.1 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概要および管理』を参照してください。

例6-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

6.6.2 ROLLBACK文

ROLLBACK文は、カレント・トランザクションを終了し、トランザクション中に加えられたすべての変更を取り消します。

表からの間違った行の削除などの誤操作を行った場合に、ロールバックは元のデータをリストアできます。SQL文が失敗したりPL/SQLが例外を呼び出したためにトランザクションを終了できない場合は、ロールバックを使用すると、対処措置を行い、実行しなおすことができます。

関連項目:

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

例6-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;
/

6.6.3 SAVEPOINT文

SAVEPOINT文は、トランザクション処理内の現在位置に名前とマークを付けます。

セーブポイントを使用すると、トランザクション全体をロールバックするのではなく、トランザクションの一部をロールバックできます。セッションごとのアクティブなセーブポイントの数には、制限がありません。

あるセーブポイントまでロールバックすると、そのセーブポイント以降にマークされたセーブポイントはすべて消去されます。ロールバック先のセーブポイントは消去されません。単純なロールバックまたはコミットではすべてのセーブポイントが消去されます。

再帰的サブプログラムの中でセーブポイントをマークすると、再帰しながら進む過程で、各レベルでSAVEPOINT文の新しいインスタンスが実行されます。ただし、ロールバックできるのは直前にマークされたセーブポイントまでです。

セーブポイント名は未宣言の識別子です。トランザクションでセーブポイント名を再利用すると、セーブポイントはトランザクションの中の古い位置から現在の位置に移動します(つまり、セーブポイントへのロールバックは、トランザクションの現在の部分のみに影響を与えます)。

関連項目:

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

例6-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;
/

例6-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;
/

6.6.4 暗黙的なロールバック

INSERT文、UPDATE文、DELETE文またはMERGE文の実行前に、データベースによって(ユーザーが利用できない)暗黙的なセーブポイントがマークされます。文が正常に実行されなかった場合は、データベースによってこのセーブポイントまでロールバックが行われます。

通常は、トランザクション全体ではなく、失敗したSQL文のみがロールバックされます。その文が原因で未処理例外が呼び出された場合は、ホスト環境によってロールバックの対象が決まります。

データベースでは、デッドロックを解消するためにSQL文を1文のみロールバックすることもできます。データベースによって、関係しているトランザクションにエラーが戻され、そのトランザクション中の現在の文がロールバックされます。

SQL文を実行する前に、データベースはその文を解析する必要があります(すなわち、その文が構文規則に従っているかどうかや、有効なスキーマ・オブジェクトを参照しているかどうかを確認する必要があります)。SQL文の実行中にエラーが検出されると、ロールバックが発生しますが、解析中にエラーが検出されても、ロールバックは発生しません。

ストアド・サブプログラムを未処理例外で終了すると、PL/SQLは値をOUTパラメータに代入せず、ロールバックを実行しません。

例外処理の詳細は、「PL/SQLのエラー処理」を参照してください

6.6.5 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言語リファレンス』を参照してください。

例6-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;
/

6.6.6 デフォルトのロックの上書き

デフォルトでは、Oracle Databaseによってデータ構造が自動的にロックされるため、異なるアプリケーションが、相互のデータに悪影響を与えたりデータを同調させることなく、同じデータ構造に書込みできます。

トランザクションの間にデータへの排他アクセスが必要な場合、次のSQL文を使用してデフォルトのロックを上書きできます。

• LOCK TABLEは、表全体を明示的にロックします。

• SELECTをFOR UPDATE句とともに使用すると(SELECT FOR UPDATE)、表の特定の行を明示的にロックできます。

ここでのトピック

• LOCK TABLE文

• SELECT FOR UPDATEカーソルとFOR UPDATEカーソル

• ROWID擬似列を使用したCURRENT OF句のシミュレート

6.6.6.1 LOCK TABLE文

LOCK TABLE文は、1つ以上の表を指定されたロック・モードで明示的にロックするため、それらの表へのアクセスを共有または拒否できます。

ロック・モードによって、その表に設定できる他のロックが決定されます。たとえば、1つの表に対して多くのユーザーが同時に行共用ロックを取得できますが、排他ロックを取得できるのは一度に1人のユーザーのみです。あるユーザーが表に対して排他ロックをかけていると、他のユーザーはその表に対して行の挿入、更新、削除を実行できません。

表がロックされていても、他のユーザーは表に対して問合せできますが、問合せを実行しても表のロックを取得できません。2つの異なるトランザクションが同じ行を変更した場合のみ、一方のトランザクションがもう一方のトランザクションの終了を待ちます。LOCK TABLE文を使用すると、別のトランザクションが完了するまでの待機時間を指定できます。

表ロックは、それらを取得したトランザクションがコミットまたはロールバックされる際に解除されます。

関連項目:

• 表の明示的なロックの詳細は、『Oracle Database開発ガイド』を参照してください。

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

6.6.6.2 SELECT FOR UPDATEカーソルとFOR UPDATEカーソル

FOR UPDATE句を伴うSELECT文(SELECT FOR UPDATE文)では、結果セットの行が選択され、それらがロックされます。SELECT FOR UPDATEを使用すると、行の中の既存の値に基づいて更新できます(これは、更新前に、他のユーザーがそれらの値を変更できないことが保証されるためです)。また、SELECT FOR UPDATEを使用すると、例9-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句に現れる行のみがロックされます。

6.6.6.3 ROWID擬似列を使用したCURRENT OF句のシミュレート

結果セットの行はFOR UPDATEカーソルをオープンするときにロックされるのであり、フェッチされるときにロックされるのではありません。ただし、FOR UPDATE句でSKIP LOCKED句が指定された場合は、行はカーソルのFETCHフェーズでロックされます。また、トランザクションをコミットまたはロールバックすると、行のロックは解除されます。例6-41に示すように、行のロックが解除された後は、FOR UPDATEカーソルからはフェッチできません(ROLLBACKとCOMMITを入れ替えても結果は同じです)。

回避策は、ROWID擬似列を使用してCURRENT OF句をシミュレートすることです(『Oracle Database SQL言語リファレンス』を参照)。各行のROWIDを選択して、UROWID変数に入れ、更新や削除のときに、ROWIDを使用して現在行を識別します(例6-42を参照)。(UROWID変数の値を出力するには、『Oracle Database SQL言語リファレンス』で説明されているROWIDTOCHARファンクションを使用してその値をVARCHAR2に変換します。)

ノート:

ハイブリッド列圧縮(HCC)で圧縮された表の行を更新すると、行のROWIDが変更されます。特定のOracleストレージ・システムの機能であるHCCの詳細は、『Oracle Database概要』を参照してください。

注意:

フェッチされた行は、FOR UPDATE句によってロックされないため、他のユーザーによって意識せずに変更内容が上書きされる可能性があります。

ノート:

読取り一貫性のために必要な追加領域は、カーソルがクローズされるまで解放されません。そのため、大規模な更新では処理速度が低下する場合があります。

例6-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

例6-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;
/