自律型トランザクション

ITコーディネータのシュウです。

きれいだった桜も散ってしまいました。なかなか忙しくて、ゆっくり花見をすることができませんでしたが、ほんの1週間ほどではありますが、力いっぱい咲いて私たちを感動させ、喜ばせてくれる自然とは、本当にすごいと、改めて感じるこの季節ですね。
ちなみに、桜の中で日本で圧倒的に多いのがソメイヨシノ（染井吉野）ですが、もとは江戸の染井村の造園師や植木職人によって育成されたものが、各地に接木されて増やされたもので、すべてのソメイヨシノは元をたどればかなり限られた数の原木につながり、それらのクローンだということです。（会社の同僚から教えてもらいました）

＜本日の題材＞
自律型トランザクション

今回は、Oracleにおける自律型トランザクション処理について、見てみます。

これは、ある一連のトランザクション処理を行う中で、それとは独立したトランザクションを宣言することができる機能で、メインのトランザクション処理はロールバックされても、そこだけCOMMITさせることが可能になります。
私も、あるデータ変換を行うメインのトランザクション処理を実行する中で、何らかのエラーが起きた場合にそのエラーがどういうものなのかをエラー用のログテーブルに書き出すという機能で使用したことがあります。当然、メインのトランザクションはエラーが発生したのでロールバックして何も更新されない状態にしますが、エラーの内容はログに書き出したいので、メインのトランザクションとは独立したトランザクションとして、例外処理の中でログに登録するというものです。
（ログの登録をメインのトランザクションの中に入れてしまうと、エラー時にトランザクションをロールバックすれば、ログへの登録もロールバックされてしまうため）
ほとんどオラクル社の提示していたサンプルをそのまま利用したかたちではありましたが。。。

例）
①まず、エラーログ用のテーブルを作成

CREATE TABLE ERROR_LOG (
ERROR_CODE NUMBER
, ERROR_MESSAGE VARCHAR2(4000)
, BACKTRACE CLOB
, CALLSTACK CLOB
, CREATED_ON DATE
, CREATED_BY VARCHAR2(30)
);

②エラー記録用のプロシージャの作成

ここで、自律型トランザクションの宣言を、PRAGMA AUTONOMOUS_TRANSACTION;で行います。

CREATE OR REPLACE PROCEDURE record_error
IS
PRAGMA AUTONOMOUS_TRANSACTION; -- 自律型トランザクションの宣言
l_code PLS_INTEGER := SQLCODE;
l_mesg VARCHAR2(32767) := SQLERRM;
BEGIN
INSERT INTO error_log (error_code
, error_message
, backtrace
, callstack
, created_on
, created_by)
VALUES (l_code
, l_mesg
, sys.DBMS_UTILITY.format_error_backtrace
, sys.DBMS_UTILITY.format_call_stack
, SYSDATE
, USER);

COMMIT;
END;
/

※DBMS_UTILITY.format_error_backtrace ファンクションはエラーが発生した行を取得し、DBMS_UTILITY.format_call_stack ファンクションは、エラー発生箇所までの実行コールスタック、どのように処理が呼び出されてきたかを示します。

③上記のエラー記録用のプロシージャを、メインのプロシージャの例外処理で呼び出します。今回は簡単な例として、部門表(DEPT)にデータを登録するという処理を作成し、エラーが発生したら処理は全てロールバックしますが、エラーログにエラーの記録を残すようにします。

BEGIN
INSERT INTO DEPT VALUES (50, 'PLANNING', 'LOS ANGELES');
INSERT INTO DEPT VALUES (60, 'DEVELOPMENT', 'SEATTLE');
INSERT INTO DEPT VALUES (50, 'GENERAL AFFAIRS', 'SAN FRANCISCO');

COMMIT;

--例外処理
EXCEPTION
WHEN others THEN
record_error();
RAISE;
END;
/

上記は、DEPT表へのデータの登録を行って、最後にCOMMIT;を実行する処理ですが、途中でDEPTNOのキー重複によりエラーが発生します。そのエラーの内容を「error_log」テーブルには記録を残すというものになります。

処理を実行すると、

DEPT表の中身は、
SELECT * FROM DEPT;

処理はロールバックされています。

ERROR_LOG テーブルの中身は、
SELECT * FROM error_log;

エラーの内容がログとして記録されています。

また、SQL Serverにおいては、自律型トランザクションの機能はサポートしていないようです。同様な処理を行いたい場合には、TRY ～CATCHのCATCHブロックの中で、新たにトランザクションを開始してログの登録を行うなどの処理が必要です。

上記と同様の処理を行うとすると、

BEGIN TRY
BEGIN TRAN
INSERT INTO DEPT VALUES (50, 'PLANNING', 'LOS ANGELES');
INSERT INTO DEPT VALUES (60, 'DEVELOPMENT', 'SEATTLE');
INSERT INTO DEPT VALUES (50, 'GENERAL AFFAIRS', 'SAN FRANCISCO');
COMMIT
END TRY

BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK

BEGIN TRAN
SELECT ERROR_NUMBER()
, ERROR_SEVERITY() AS エラー重大度
, ERROR_MESSAGE() AS エラーメッセージ
, ERROR_LINE()

INSERT INTO error_log(
ERROR_NUMBER
, ERROR_SEVERITY
, ERROR_MESSAGE
, ERROR_LINE
)
SELECT
ERROR_NUMBER()
, ERROR_SEVERITY() AS エラー重大度
, ERROR_MESSAGE() AS エラーメッセージ
, ERROR_LINE()
COMMIT;
END CATCH