none
SQLServer起動後に更新されていないテーブルをselect with(xlock)で排他取得しても、排他されない? RRS feed

  • 質問

  • 手元の環境で、SQLServerを起動したあとにDML文が実行されていないテーブルでwith(xlock)しても、他のトランザクションがselectするのを排他しないという動作をしており、困惑しています(確認した環境は、SQLServer2012で、isolation level は read committedです。2008R2でも同じ動作を確認しました)。

    具体的には、SQLServer起動後にDML文が実行されていないテーブルで下記トランザクションをA→Bの順番で実行しても、トランザクションBは結果が返ってきてしまいます(この動作をケースXとします)。

    ・トランザクションA
      begin transaction
      select * from t_sample with(xlock)
    ・トランザクションB
      begin transaction
      select * from t_sample

    しかし、いったん下記トランザクションCを実行したあとに、A→Bと実行すると、BがAによってブロックされるようになります(この動作をケースZとします)。

    ・トランザクションC:
      begin transaction
      update t_sample set c1=100
      rollback transaction

    いったんケースZの動作となったあとに、ケースXが発生することはありません。ただ、SQLServerのサービスを再起動して試すと、ケースXが発生します。

    また、SQLServer起動直後でも、トランザクションAを with(xlock,tablock)やwith(xlock,paglock)とすると排他が正しく行われます。(with(xlock,rowlock)だとケースXとなります)

    動作としてはケースZが正しいと思うのですが、ケースXも間違いなく発生していて、その理由がわかりません。
    なぜこのような動作になるのでしょうか?


    補足:テストで使用したテーブルのクエリは次の通りです。
    create table t_sample(
      c1 int,
      c2 int,
      c3 char(8000)
    );

    declare @i int = 0;
    while(@i<100)
    begin
      insert into t_sample values(@i, @i+1000, 'hello');
      set @i = @i+1
    end



    • 編集済み tfumiaki 2013年12月16日 8:34
    2013年12月16日 8:32

回答

  • Lockの制御はSQL Server側で同時実行性を最大限高めるよう最適化されているので、詳細な動作は非常に複雑です。

    この場合、ユーザーデータベースでREAD COMMITTEDで変更が無いテーブルに対してSELECTが行われているため、トランザクションBにおいて共有ロックを取得しなくてもREAD COMMITTEDで要求される排他レベルが維持されることが分かっているため、共有ロックの取得をせずに読み取りを行うことで処理を高速化していたはずです。
    以下のBlogの末尾にあるCLAROFOCATIONにも関連する記述がありますので、合わせてご参照ください。
    http://blogs.msdn.com/b/craigfr/archive/2007/04/25/read-committed-isolation-level.aspx

    詳細な動作についてはLockの取得状況をProfiler等で時間軸に沿って追跡すると動作を確認できるかと思います。


    MCITP(Database Developer/Database Administrator)

    • 回答の候補に設定 星 睦美 2013年12月26日 5:07
    • 回答としてマーク tfumiaki 2013年12月26日 12:43
    2013年12月24日 23:58

すべての返信

  • Lockの制御はSQL Server側で同時実行性を最大限高めるよう最適化されているので、詳細な動作は非常に複雑です。

    この場合、ユーザーデータベースでREAD COMMITTEDで変更が無いテーブルに対してSELECTが行われているため、トランザクションBにおいて共有ロックを取得しなくてもREAD COMMITTEDで要求される排他レベルが維持されることが分かっているため、共有ロックの取得をせずに読み取りを行うことで処理を高速化していたはずです。
    以下のBlogの末尾にあるCLAROFOCATIONにも関連する記述がありますので、合わせてご参照ください。
    http://blogs.msdn.com/b/craigfr/archive/2007/04/25/read-committed-isolation-level.aspx

    詳細な動作についてはLockの取得状況をProfiler等で時間軸に沿って追跡すると動作を確認できるかと思います。


    MCITP(Database Developer/Database Administrator)

    • 回答の候補に設定 星 睦美 2013年12月26日 5:07
    • 回答としてマーク tfumiaki 2013年12月26日 12:43
    2013年12月24日 23:58
  • なるほど。確かに動作上はロックされなくても問題ないケースですね。
    ロックの動作テストをしていてこの動作に気づいたのですが、テストをするようなケースでは、更新していないテーブルを使用するようにしないとだめですね…

    プロファイラで動作を追ってみます!

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

    2013年12月26日 12:46