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

お世話になった知人の方が、仕事の都合もあって実家のある愛媛県のほうに引っ越しされるということで、瀬戸大橋を電車で移動されているときに撮られた写真を頂きました。まだ瀬戸大橋を渡ったことはないのですが、是非一度行ってみたいですね。
そういえば、瀬戸大橋を渡る瀬戸大橋線に乗っているときに、瀬戸の花嫁の曲が流れるみたいですね。「瀬戸は日暮れて～！」瀬戸に来たという実感がわくみたいです。

さて、dbSheetClientに新しい事例がアップされました。
成約引合管理、予算収集、ワークフロー、文書管理、業績見える化まで、様々な業務効率化システムを4年間で８０以上構築稼働させたナラサキ産業様の事例です。詳しくは、こちらを参照してみてください。
http://www.newcom07.jp/dbsheetclient/usrvoice/narasakisangyo.html

＜本日の題材＞
デッドロック（SQL Server）

なかなか普段はそんなにはお目にかからないデッドロックの現象ですが、複数のクライアントが同時に同じテーブルにアクセスする環境では、アプリの作り方によっては簡単に起きてしまうこともあります。
一方のタスクがロックを試みているリソースに対して他方のタスクがロックを獲得し、これが相互に行われるとデッドロックが発生するというのが一般的な現象になると思います。本日は、複数のテーブルに対して更新する処理を、処理の順番が逆になるかたちで2つのプロセスが行うかたちでのデッドロック現象を確認してみたいと思います。ちなみに、同じテーブルの異なる行に対する処理でも同様に発生します。

 例）
2つのテーブル（tbl_A、tbl_B）があり、片方の処理は、トランザクション処理の中で、tbl_Aのレコード（CD_A=4）を更新した後に、tbl_Bのレコード（CD_B=4）を更新しますが、同時に、もう一つの処理が tbl_Bのレコード（CD_B=4）を更新した後に、tbl_A（CD_A=4）の値を更新します。
なお、タイミングが重なるように、処理の途中で以前ブログでも取り上げましたスリープ処理（WAITFOR DELAY）を入れてみます。

処理A）：
BEGIN TRANSACTION
 UPDATE dbo.tbl_A SET
   amount = 2100
  WHERE CD_A = 4;
 
 WAITFOR DELAY '00:00:10'
 
 UPDATE dbo.tbl_B SET
   amount = 3000
  WHERE CD_B = 4;
COMMIT TRANSACTION

処理B）：
BEGIN TRANSACTION
 UPDATE dbo.tbl_B SET
   amount = 1200
  WHERE CD_B = 4;

 WAITFOR DELAY '00:00:3'

 UPDATE dbo.tbl_A SET
   amount = 1500
  WHERE CD_A = 4;
COMMIT TRANSACTION

処理Aを実行した直後に、処理Bを実行します。すると、下記のようなかたちで処理が進み、デッドロックが発生します。

実際に、SQL Serverで実行してみます。
SQL Server Maangement Studioの1つのウィンドウで処理Aを実行し、すぐに別のウィンドウで処理Bを実行します。

処理A

処理B

 すると、処理Aでは、上記の結果のように、下記のようなデッドロックのエラーが表示されます。

「メッセージ 1205、レベル 13、状態 51、行 9
トランザクション (プロセス ID 52) が、ロック個のリソースで他のプロセスとデッドロックして、このトランザクションがそのデッドロックの対象となりました。トランザクションを再実行してください。」

それでは、次に、デッドロックが発生したときの対応として、エラーコード：1205を取得したら処理をリトライするという仕組みを、BEGIN CATCH～END CATCHの中に組み込んでみます。

処理A)
RETRY:
BEGIN TRANSACTION
BEGIN TRY
 UPDATE dbo.tbl_A SET
   amount = 2100
  WHERE CD_A = 4;
 WAITFOR DELAY '00:00:10'
 UPDATE dbo.tbl_B SET
   amount = 3000
  WHERE CD_B = 4;
 COMMIT TRANSACTION
END TRY

BEGIN CATCH
 PRINT 'ロールバックします'
 ROLLBACK TRANSACTION
 IF ERROR_NUMBER() = 1205 -- デッドロックエラーコード
 BEGIN
   WAITFOR DELAY '00:00:05'
   GOTO RETRY
 END
END CATCH

処理B)
RETRY:
BEGIN TRANSACTION
BEGIN TRY
 UPDATE dbo.tbl_B SET
   amount = 1200
  WHERE CD_B = 4;
 WAITFOR DELAY '00:00:3'
 UPDATE dbo.tbl_A SET
   amount = 1500
  WHERE CD_A = 4;
 COMMIT TRANSACTION
END TRY

BEGIN CATCH
 PRINT 'ロールバックします'
 ROLLBACK TRANSACTION
 IF ERROR_NUMBER() = 1205 -- デッドロックエラーコード
 BEGIN
   WAITFOR DELAY '00:00:05'
   GOTO RETRY
 END
END CATCH

 SQL Serverで実際に実行してみます。

処理A）

 処理B）

処理Aのほうが、一旦はデッドロックのエラーが発生しましたが、5秒後に再度リトライを実施して、最終的には更新が終了している様子が確認できます。

処理後の、tbl_A、tbl_B のデータを確認してみると

tbl_Aは

 tbl_Bは

 上記から、最後は処理Aの更新処理がされていることが確認できました。

さて、実際には、デッドロックは上記のようなケースだけではなく、ロックのエスカレーションによるブロッキングが原因で発生する場合などもあります。これについては、また機会があるときに取りあげてみたいと思います。

今日は以上まで

にほんブログ村

 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

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

ROLLBACKされて、レコードは増えていません。

ERROR_LOGテーブルの中身は、

SELECT * FROM dbo.ERROR_LOG;

エラー情報が記録されているのが確認できます。

今日は以上まで

にほんブログ村

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

 小学校の息子の卒業式に行ってきたときの写真です。
今まで、子供の卒業式には行ったことが無く、母親が行くものという認識でいたのですが、「今は父親も卒業式にはたくさん来てるのよ！」という妻の言葉もあり、参加してみたところ、確かに夫婦で卒業式に来てる方のほうが多いんですね。父親も子育てを楽しんでいる姿を感じました。もっと子育てに関心を持たなければと、いたく反省した次第です。

＜本日の題材＞
トランザクション処理

今回は、トランザクション処理について、見てみます。

トランザクション処理とは、データの一貫性を保つために、1つ以上のDML文(UPDATE、INSERT、DELETEなど)を1つの単位として管理し、実行結果が完全に成功か、完全に失敗かのどちらかになることを保証する機能です。
よく例として挙げられるのが、銀行のシステムです。例えば、AさんがB社より商品を購入して、代金として10万円をB社の口座に振り込むとした場合、処理としては、
①    Aさんの口座から10万円を減額
②    B社の口座に10万円を増額
という2つの処理を実施しなければなりません。ところが、①の処理を終えた段階で何らかのトラブルが発生して、②の処理を実行できなくなってしまった場合には、データに不整合が発生してしまいます。そのため、この2つの処理を1つのトランザクション処理としてまとめて、両方の処理が成功した場合のみこの変更処理を確定し、処理が一部でも失敗した場合には、全ての処理を取り消すということが必要になります。

なお、DBMS(データベース管理システム)には、どこからどこまでの処理が1つのトランザクションとしての区切りなのかについては、自動的にはわかりません。あくまでユーザが明確に指定して上げる必要があります。

ここで、SQL Serverの場合には、前回説明したエラー処理も含めると以下のようにすることができます。

BEGIN TRY
    BEGIN TRAN
        (DML文の処理)
    COMMIT
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK
END CATCH

@@TRANCOUNT関数は、現在の接続で実行された BEGIN TRANSACTION ステートメントの数を返す関数で、トランザクションが1つ以上開始されている場合、1以上の数字になります。トランザクションが開始されているかどうか確認したいときに使用します。上記では、トランザクションを設定していることは分かっているので、基本的に必要ありませんが、念のため記述しています。

下記の例は、Aさんの口座から100000をマイナスし、B社の口座に100000をプラスする処理をトランザクションで実施する内容ですが、Aさんの口座から減額した後で、RAISERROR関数にてエラーを起こします。そのためトランザクション開始後、エラーが起きるまでに行った更新処理がロールバックされて、元に戻ることを確認します。

BEGIN TRY
    BEGIN TRAN
        UPDATE accounts SET
            balance = balance - 100000
       WHERE account_number = 1234567;

      RAISERROR('RAISERRORにより強制終了',11,1);

      UPDATE accounts SET
          balance = balance + 100000
       WHERE account_number = 9876543; 
     COMMIT
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
         ROLLBACK

     SELECT ERROR_NUMBER() AS エラー番号
   , ERROR_SEVERITY() AS エラー重大度
   , ERROR_MESSAGE() AS エラーメッセージ
   , ERROR_LINE() AS エラー行
END CATCH

処理前後の口座の金額を確認すると、上記の処理前も、処理後も同じであることが確認できました。ですので、このトランザクション処理は確実にロールバックされたということです。

なお、OracleやDB2については、データベースへ接続して最初のSQLの実行から自動的にトランザクションが開始されるため、SQL Serverのような「BEGIN TRANSACTION」のようなコマンドは不要です。その後、COMMITか、ROLLBACKによりトランザクションが終了するかたちになります。

以下は、上記の例をOracleで同様に実施した場合の例。

DECLARE
  Trans_ERR EXCEPTION;             -- ユーザー定義例外の宣言
BEGIN
      UPDATE accounts SET
          balance = balance - 100000
       WHERE account_number = 1234567;

       RAISE Trans_ERR;                     -- ユーザー定義例外の呼出し

      UPDATE accounts SET
         balance = balance + 100000
       WHERE account_number = 9876543;

     COMMIT;
EXCEPTION
    WHEN Trans_ERR THEN
       ROLLBACK;
       DBMS_OUTPUT.PUT_LINE('強制的にエラーを発生、ロールバックしました');

END;
/

処理前後の口座の金額は、変わっていないことを確認しました。

ちなみに、MySQL、PostgreSQLでのトランザクションの開始コマンドは、それぞれ以下になるようです。
MySQL： START TRANSACTION
PostgreSQL： BEGIN TRANSACTION

今日は以上まで

にほんブログ村