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

今日は以上まで

にほんブログ村

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

先日、東京駅のすぐそばのJPタワー・KITTE 4FのHall&Conferenceで行われたSecurity Days 2015のセミナーに、今年も参加してきました。目的はITコーディネータのポイント取得を兼ねてセキュリティに関する最新の情報収集。相変わらず大きな問題である標的型攻撃対策、内部犯行を含めての情報漏洩対策など、様々な内容が盛りだくさん。その中で、2015年10月から始まるマイナンバー制度や、個人情報保護法改正に向けた内容など、いろいろと気になる点もありました。「個人情報の保護レベルを世界水準に合わせよう」ということを指摘されている、新潟大学の鈴木正朝先生の講演が、私としては結構インパクトがありましたね。日本よりも規制の厳しい欧米の水準に合わせないと、ゲノムなど含めて世界の情報を日本に集めることができなり、産業的に大きな問題になるということを言っておられ、説得力がありました。

写真は、そのビルの屋上庭園「KITTEガーデン」から撮った東京駅の写真です。まだ風がちょっと寒かった！

＜本日の題材＞
例外処理(エラー処理) (SQL Server編)

前回、前々回は、ORACLEの例外処理について取り上げました。
今回は、SQL Serverの例外処理について、見てみます。

SQLServerのTransact-SQLでは、SQLServer2005以降において例外処理がサポートされています。記述の仕方は MicrosoftのVisual C#、Visual C++ 言語での例外処理に似ていて、Transact-SQL ステートメントのグループを TRY ブロックで囲み、TRY ブロック内でエラーが発生すると、CATCH ブロックで囲まれた別のステートメントのグループに制御が渡されるというものです。

構文は以下：

BEGIN TRY
  (処理ロジック)
END TRY
BEGIN CATCH
  (ERROR 処理ロジック)
END CATCH

下記の例は、「STEP 1」表示の後、INSERT文でデータを登録するときに、桁あふれのエラーにより処理が中断され、「STEP 2」を表示することなく、エラーがCATCHされてエラー処理（エラーメッセージの表示）が実行されることを示します。

BEGIN TRY
  SELECT 'STEP 1'
  INSERT INTO dbo.社員マスタ(社員番号,社員名,拠点)
  VALUES(123456, 'TEST_MENBER', 'シカゴ');

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

ここで、エラーに関する情報は、”ERROR_” で始まるシステム関数で取得することができます。
ERROR_NUMBER()         --            エラー番号
ERROR_MESSAGE()       --            エラーメッセージ
ERROR_SEVERITY()       --            エラーの重大度レベル
ERROR_STATE()               --            エラーの状態番号
ERROR_LINE()                   --            エラーが発生した行番号
ERROR_PROCEDURE()  --            エラーが発生したストアドプロシージャ                                                                         またはトリガー名

※エラーの重大度レベルについては、以下のMSのHPを参照
  https://msdn.microsoft.com/ja-jp/library/ms164086.aspx

ただし、この TRY　～ CATCH ステートメントは、以下の種類のエラーはCATCHできないということです。
・構文エラーなどのコンパイルエラー
・再コンパイルで発生するエラー（コンパイル後の名前の遅延解決により発生するオブジェクト名の解決エラーなど）

以下の例は、実際には存在しないテーブルを抽出するSQLを実行してコンパイルエラーが発生するケースで、このときにはTRY　～ CATCHではキャッチされず、実行したアプリ（例えばSQLServer Management Studio）にエラーが返されます。

BEGIN TRY
    PRINT N'処理の実行開始';
    SELECT * FROM 存在しないテーブル;
END TRY
BEGIN CATCH
    SELECT
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
GO

この種のエラーは、エラーの発生場所と同一の実行レベルの TRY...CATCH 構造では処理されません。上記のような場合でもエラー処理をするためには、エラーが発生するコードを TRY ブロック内部の独立したバッチで実行、すなわち、ストアドプロシージャに記述するか、sp_executesql を使用して動的な Transact-SQL ステートメントを実行するかたちにすることで、低いレベルで発生したエラーをTRY...CATCH 構造でCATCHできるということです。以下が動的SQLを使ったその例になります。

BEGIN TRY
    PRINT N'処理の実行開始';
        DECLARE @sql VARCHAR(4000)
        SET @sql = 'SELECT * FROM 存在しないテーブル'
        EXEC(@sql)
END TRY
BEGIN CATCH
    SELECT
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
GO

実行結果は、上記のようにエラーをCATCHして、BEGIN CATCHのエラー処理が実行されています。

今日は以上まで

にほんブログ村