[Oracle] 自律型トランザクション

自律型トランザクションというのは、実行中のメイン・トランザクションによって開始される独立（自律）したトランザクションのことです。

この自律型トランザクションを使用すると実行中のメイン・トランザクションを一時停止し、自律型トランザクション内でSQL操作を実行し、コミット・ロールバックした後、メイン・トランザクションを再開することができます。

この時、メイン・トランザクションと自律型トランザクションのコミット・ロールバック操作は互いに影響を及ぼさず、完全に独立した別個のトランザクションとなります。

自律型トランザクションは、その特性を活かして、例えば以下のようなケースで利用することができます。

• 実行中のトランザクションをコミット・ロールバックせずに、その進行状況を他のユーザーが随時確認できるよう、ログ、ステータス、カウントなどを逐次書き出す場合
• 実行中のトランザクションをコミット・ロールバックせずにDDL文を実行する場合（通常、DDL文を実行すると自動的にコミットされてしまうため）

自律型トランザクションとして実行することができるのはプロシージャ、ファンクション、トリガー、（ネストしていない）PL/SQLブロックなどで、これらのプログラム単位を自律型トランザクションとして実行する場合、宣言部にPRAGMA AUTONOMOUS_TRANSACTIONを記述します。

具体的な利用例は以下の通り。

CREATE OR REPLACE PROCEDURE PROC1
(
  p_id  IN  NUMBER
)
IS
BEGIN
  FOR i IN 1..10000 LOOP
    INSERT INTO PRODUCT VALUES ( i, 'PRODUCT' || TO_CHAR(i) );
    PROC2( p_id );
  END LOOP;
  COMMIT;
END;

CREATE OR REPLACE PROCEDURE PROC2
(
  p_id  IN  NUMBER
)
IS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  UPDATE JOB_STATUS
    SET COUNT = COUNT + 1
    WHERE ID = p_id;
  COMMIT;
END;
/

この例では、PRODUCTテーブルに１万件のレコードを挿入する間、１レコード挿入するたびにJOB_STATUSテーブルのCOUNT列の値を１ずつインクリメントしてコミットしています。

このようにすることにより、バッチ処理実行中に、他のユーザーがどれだけ処理が進んでいるかを随時確認することができるというわけです。