none
SQL Server 2005でのテーブル ヒント(NOWAIT)の動作について RRS feed

  • 質問


  • はじめまして、下記について情報をより問題点等がお気づきの点が
    ございましたら、ご教授宜しくお願いいたします。。


    <実行環境>
    Windows Server 2003 Enterprise Edition
    SQL Server 2005 Enterprise Edition

    <望む結果>
    ・SQL Server 2005で、READ COMITTED SNAPSHOTがオンの状態で、同一行に対する
     複数トランザクションが、ほぼ同じタイミングで
     発生した場合に、後から開始されたトランザクションは既に該当行にロックがかかっていたら
     ロック解除待ちをせずにエラーで終了させる。といったことが実現したいです。
     
    <検証手順>

    テーブルヒントのNOWAITを使用して実現しようと試みましたが
    下記手順では後発のクエリ(クエリ2)で、待ち時間が発生してしまいます。

    SQL Server Manegment Studioから下記の順でそれぞれ別ウィンドウから
    クエリを実施。WAITFORで指定した秒くらいの待ちが発生してしまう。
    手順に問題あり?

    1.クエリ1
    2.クエリ2



    ・トランザクション分離レベルは以下のように設定
    ALTER DATABASE TEST_DB
    SET READ_COMMITTED_SNAPSHOT ON;
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

    -- クエリ1 --------------------------------------------------

    USE TEST_DB;
    IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[test1]') AND type in (N'U'));
    DROP TABLE [dbo].[test1];

    CREATE TABLE test1(col1 int,col2 int)
    INSERT INTO test1 values(1,1)
    INSERT INTO test1 values(2,2)

    SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
    BEGIN

      SELECT * FROM TEST1  WITH(NOWAIT,ROWLOCK,UPDLOCK);

      UPDATE test1 SET col2 = 999 where col1 = 1;

      SELECT * FROM TEST1;

      WAITFOR delay '00:00:15';

    COMMIT;
    --------------------------------------------------------------

    -- クエリ2 --------------------------------------------------

    USE TEST_DB;

    BEGIN
      SELECT * FROM TEST1 WITH(NOWAIT,ROWLOCK,UPDLOCK);

      UPDATE test1 SET col2 = 888 where col1 = 1;

      SELECT * FROM TEST1;

    COMMIT;
    --------------------------------------------------------------



    2008年12月18日 11:32

回答

  • クエリ2がWaitしているのは、

    UPDATE test1 SET col2 = 888 where col1 = 1;

    でだと思います。

     

     

    USE TEST_DB;
    BEGIN
      SELECT * FROM TEST1 WITH(NOWAIT,ROWLOCK,UPDLOCK);

    UPDATE文なしですと、即時にタイムアウトしています。

     

    UPDATEでWaitしているのは、UPDATE文が更新ロックを取得しようとしてWaitになっていると思います。

    直前のSelect文は即時タイムアウトして更新ロックを取得できていないので・・・。

     

     

    つまり、UPDATE文にもNOWAITをつけてあげないといけないのです。

    UPDATE test1 with (nowait) SET col2 = 888 where col1 = 1;

     

    参考:http://msdn.microsoft.com/ja-jp/library/ms177523(SQL.90).aspx

    2008年12月18日 14:13

すべての返信

  • クエリ2がWaitしているのは、

    UPDATE test1 SET col2 = 888 where col1 = 1;

    でだと思います。

     

     

    USE TEST_DB;
    BEGIN
      SELECT * FROM TEST1 WITH(NOWAIT,ROWLOCK,UPDLOCK);

    UPDATE文なしですと、即時にタイムアウトしています。

     

    UPDATEでWaitしているのは、UPDATE文が更新ロックを取得しようとしてWaitになっていると思います。

    直前のSelect文は即時タイムアウトして更新ロックを取得できていないので・・・。

     

     

    つまり、UPDATE文にもNOWAITをつけてあげないといけないのです。

    UPDATE test1 with (nowait) SET col2 = 888 where col1 = 1;

     

    参考:http://msdn.microsoft.com/ja-jp/library/ms177523(SQL.90).aspx

    2008年12月18日 14:13
  •  てっくのこ さんからの引用

    ・SQL Server 2005で、READ COMITTED SNAPSHOTがオンの状態で、同一行に対する

     

    ちょっと気になったのですが、この場合READ COMITTED SNAPSHOTがオンであるかどうかは関係ない話ですよね。

    2008年12月18日 15:08
  • はじめまして、野良さん
    情報ありがとうございます。

    USE TEST_DB;

    BEGIN
    SELECT * FROM TEST1 WITH(NOWAIT,ROWLOCK,UPDLOCK);

    UPDATE test1 SET col2 = 888 where col1 = 1;


    SELECT * FROM TEST1 WITH(NOWAIT,ROWLOCK,UPDLOCK);
    でタイムアウトしても、後続の処理は続けられるんですね。
    タイムアウトが発生した時点で、処理が中止されるものと思っていました。

    教えて頂いたUPDATE等の後続処理にもWTIH(NOWAIT)を付ける方法で、
    やりたいことが実現できました。

    ロックを取得しているSELECTの後にエラー制御(@@ERROR、TRY・・・CATCH)を
    加えることでもやりたいことが実現できました。

    ありがとうございました。

    2008年12月19日 2:33
  • はじめまして、trapemiyaさん

    おっしゃる通り、今回のケースでは特にREAD COMMITED SNAPSHOTがオンであることは
    問題ではありませんでしたね。


    2008年12月19日 2:38