none
ロックについての質問 RRS feed

  • 質問

  • ストアドであるテーブルにロックをかけて4ステップのステートメント実行のあいだ
    TABLEロックをかけたいのですが やり方を教えてください。

    たとえば
    ==========================================
    ALTER PROCEDURE XXXXtestproc
    AS

    step01:

      UPDATE XXXX~

    STEP02:

         SELECT @カウントKEY = KEYCNT from AA403

         UPDATE BB801TBL
                SET 主キー = @カウントKEY + 1

         UPDATE AA403
             SET @カウントKEY = @カウントKEY + 1

    STEP03:
          SELECT ~
    ========================================

     

    の STEP02 を実行中の間だけ AA403 に テーブルロックをかけたいのですが
    のところで SELECT ~ with(TABLOCKX,HOLDLOCK) とすれば以降のステップがすべて終了するまで有効になるのでしょうか?
    よろしくお願いします。

     

    2006年8月11日 3:35

回答

  •  こんにちは。

     ストアドプロシージャの始めでBEGIN TRAN、STEP02の終わりでCOMMIT TRANするように変更すれば、後はおっしゃるようにSELECT ~ with(TABLOCKX,HOLDLOCK) のようにして、STEP02の終わりまでテーブルロックを掛けることができます。

     もっとも本当は、

    1. BEGIN TRANとCOMMIT TRANはストアドプロシージャ開始時に既にトランザクションが開始されていない場合(@@TRANCOUNT = 0)のみ行う

    2.UPDATEで(できればSELECTでも)エラーが起こらなかったかを調べて(SQL Server 2005ならTry~Catchを使う)、もしエラーだったときは、COMMIT TRANせずに、かつ、ストアドプロシージャ側でBEGIN TRANしていたときに限りストアドプロシージャ側でROLLBACK TRANしてから、呼び出し元にエラーを返す

    ようにしなければいけません。このようなストアドプロシージャ内でのトランザクションの扱いはSQL Serverの設計の理解に苦しむ部分で、なぜこんな面倒な処理を毎回開発者に書かせるのかわかりません。ストアドプロシージャ開始時にトランザクションが開始されていたときは、BEGIN TRAN・COMMIT TRAN・ROLLBACK TRANのいずれも無視されるという設計ならいくらか便利だったのですが、実際にはいずれも中途半端に実行されるか、過剰に実行されます。

     なお、SAVEPOINTの機能は、分散トランザクションが使えなくなるためお勧めしません(呼び出し元が分散トランザクションを使うかどうかはストアドプロシージャの実装者の側では決められない)。

    2006年8月11日 4:32
  •  おおむねそんな感じになるかと思います。BEGIN TRANはストアドプロシージャの最初に置いた方が、STEP01のUPDATEもトランザクションに入るので好ましいかとは思いますが、そうしなくとも当初の目的は達成できるかと思います。

     TRY~CATCHは、IF文が並んで見苦しいのをスッキリさせるための書式に過ぎないので、使いたくなければ使わなくとも構いません。ただし、呼び出し側でTRY~CATCHを使っていると挙動が変わってしまうという難点はあります(この辺りもSQL Serverの設計の問題点です)。

    2006年8月13日 1:17

すべての返信

  •  こんにちは。

     ストアドプロシージャの始めでBEGIN TRAN、STEP02の終わりでCOMMIT TRANするように変更すれば、後はおっしゃるようにSELECT ~ with(TABLOCKX,HOLDLOCK) のようにして、STEP02の終わりまでテーブルロックを掛けることができます。

     もっとも本当は、

    1. BEGIN TRANとCOMMIT TRANはストアドプロシージャ開始時に既にトランザクションが開始されていない場合(@@TRANCOUNT = 0)のみ行う

    2.UPDATEで(できればSELECTでも)エラーが起こらなかったかを調べて(SQL Server 2005ならTry~Catchを使う)、もしエラーだったときは、COMMIT TRANせずに、かつ、ストアドプロシージャ側でBEGIN TRANしていたときに限りストアドプロシージャ側でROLLBACK TRANしてから、呼び出し元にエラーを返す

    ようにしなければいけません。このようなストアドプロシージャ内でのトランザクションの扱いはSQL Serverの設計の理解に苦しむ部分で、なぜこんな面倒な処理を毎回開発者に書かせるのかわかりません。ストアドプロシージャ開始時にトランザクションが開始されていたときは、BEGIN TRAN・COMMIT TRAN・ROLLBACK TRANのいずれも無視されるという設計ならいくらか便利だったのですが、実際にはいずれも中途半端に実行されるか、過剰に実行されます。

     なお、SAVEPOINTの機能は、分散トランザクションが使えなくなるためお勧めしません(呼び出し元が分散トランザクションを使うかどうかはストアドプロシージャの実装者の側では決められない)。

    2006年8月11日 4:32
  •  gnaka さま、返信ありがとうございます。

    ということは

     ==========================================
    ALTER PROCEDURE XXXXtestproc
    AS

    step01:

      UPDATE XXXX~

    STEP02:

      Begin Transaction

         SELECT @カウントKEY = KEYCNT from AA403 with(TABLOCKX,HOLDLOCK) 

        if @@Error <> 0 GOTO STEP02失敗

         UPDATE BB801TBL
                SET 主キー = @カウントKEY + 1

        if @@Error <> 0 GOTO STEP02失敗

         UPDATE AA403
             SET @カウントKEY = @カウントKEY + 1

       if @@Error <> 0 GOTO STEP02失敗

    STEP02成功:
            Commit Transaction
            Goto STEP03

    STEP02失敗:
            Rollback Transaction
            Return

    STEP03:
          SELECT ~
    ========================================

     

    ということですか??、 ちなみに 「Try CATCH ~ END」は あまり使いたくないのでですが使わないと
    だめですか?

    SQL SERVER 2005 DEVELOPER EDITION でテストしております。

    たびたびすみませんがよろしくお願いします。

     

    2006年8月11日 5:57
  •  おおむねそんな感じになるかと思います。BEGIN TRANはストアドプロシージャの最初に置いた方が、STEP01のUPDATEもトランザクションに入るので好ましいかとは思いますが、そうしなくとも当初の目的は達成できるかと思います。

     TRY~CATCHは、IF文が並んで見苦しいのをスッキリさせるための書式に過ぎないので、使いたくなければ使わなくとも構いません。ただし、呼び出し側でTRY~CATCHを使っていると挙動が変わってしまうという難点はあります(この辺りもSQL Serverの設計の問題点です)。

    2006年8月13日 1:17
  •   gnakaさま、ご回答いただき、まことにありがとうございます。

    この件はSSISのトランザクションでのストアドの実行のため上記のような途中でのBegin Transaction となりました。
    全体的にはSSISがトランザクションのコントロールを行っているためです。

    SSISを使う場合はBegin Transaction を書かなくてもいいかもしれませんね。

     


     

     

    2006年8月17日 13:34
  • Nobnnn さん、

     

    こんにちは!

    フォーラム オペレーターの服部 清次です。

    今回、gnaka さんの回答が参考になったようですので、こちらの情報を他の方々に共有していただくためにも、私の方で回答チェックを付けさせていただきました。

     

    また何か質問などがありましたら、ぜひ TechNet Forum にご投稿ください。

    どうぞよろしくお願いします。

    それでは、また!

     

    _____________________________________________

    マイクロソフト株式会社 フォーラム オペレータ 服部 清次

    2008年10月23日 4:38