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

Question

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


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

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

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

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

1.クエリ１
2.クエリ２



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

-- クエリ１ --------------------------------------------------

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;
--------------------------------------------------------------

-- クエリ２ --------------------------------------------------

USE TEST_DB;

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

  UPDATE test1 SET col2 = 888 where col1 = 1;

  SELECT * FROM TEST1;

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

Answer 1

クエリ２が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

Answer 2

てっくのこ さんからの引用

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

 

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

Answer 3

はじめまして、野良さん
情報ありがとうございます。

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)を
加えることでもやりたいことが実現できました。

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

Answer 4

はじめまして、trapemiyaさん

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