排他ロックしたレコードが、別トランザクションから参照されてしまい困っています。

SQL Server2000を使用し、
あるトランザクションで排他ロック(XLOCK)をかけたレコードが
他のトランザクションから共有ロックを使用し参照できてしまう状態は存在するのでしょうか？

排他ロックをかけたレコードが
他のトランザクションから参照できてしまい困っています。

現在、下記の環境で開発を行っております。

・サーバ側
Windows Server2003
SQL Server2000(sp4)
・クライアント側
Windows Xp(sp3)
jdk6.0
jdbc 3.0 Type4

以下が具体的な状況となります。

前提として、
・二つのトランザクション(以下Ａ、Ｂと表記します)が存在する。
・ＡとＢは別のユーザでコネクションを張っている。
・autoCommitはfalseに設定している。
・テーブルにプライマリキーやインデックスは張っていない。
・レコードは10件。
・分離レベルはREAD COMMITED

(1)Ａから、「SELECT * FROM TEST_TABLE WITH(XLOCK) 」を発行
(2)Ｂから、「SELECT * FROM TEST_TABLE」を発行

このような状況で、(1)、(2)の順で処理を行った場合に
私の認識では、(2)の検索時にタイムアウト等が発生するという認識です。
しかし、(2)のSQLは正常に終了し、(1)と同じデータが取得されてしまいます。

◆その他、確認したこと
・(1)の処理直後に処理を停止し、Enterprise Managerでロックが取得されているか確認したところ、トランザクションＡがすべてのレコードを排他ロック(X)していた。

・(2)の処理直後にロックの状態を確認しても、やはりトランザクションＡが、排他ロック(X)していた。

・CSEを使いODBC経由で同様の操作を行った場合も同じ動作がおこる。

・(1)WITH(XLOCK, TABLOCK)とすると(2)でデータが取得できなくなる。

・(2)のSQLを「SELECT * FROM TEST_TABLE WHERE COLUMN01 > 0」のように指定するとタイムアウトする(期待通りの動き)

・(2)のSQLを「SELECT COLUMN01 FROM TEST_TABLE WHERE COLUMN01 > 0」のように指定すると、今度は、なぜか取得出来てしまう。

・(1)と(2)の間にトランザクションＡでUPDATEなどを行うと(COMMITはしない)(2)のSQLのWHERE句や取得するカラムに関係なく、(2)のSQLはタイムアウトする(当り前か。。。)

いろいろ書きましたが、排他ロックされたレコードに
共有ロックはかけれないという認識なのですが、
そうではないのでしょうか？
また、そうではない場合どういった場合に、
共有ロックが可能となるのでしょうか？

SQL Serverの排他制御に詳しい方や
同じような現象に陥った方がいましたら、ご教授お願いします。

No.1 ベストアンサー 回答者： jamshid6 回答日時： 2010/02/21 05:45

私はロックヒントを殆ど使わないのですが、実際主キーのないテーブルで試してみるとそうなります。

プロファイラで見ても、ロックヒントのないSELECTがテーブルに対するインテント共有ロック以外取得していないように見えます
（ちなみにREPEATABLE READでは行共有ロックを取得し、SERIALIZABLEではテーブル共有ロックを取得しますし、主キーのあるテーブルではキーレベルの共有ロックを取得しいくのでXLOCKでブロックできます）。

MSDNのどこにもそのような記述はないので、いろいろ探した結果、あるMSDNのブログで１つだけそれらしい記述のあるものを見つけました。
> SQL Server avoids acquiring read committed locks when it knows that no data has changed on a page.
http://blogs.msdn.com/craigfr/archive/2007/04/25 …

この理解が正しいとすれば、「ユーザデータベースでは変更のマークのないページには共有ロックはかけない」となり、XLOCKだけではREAD COMMITTEDのSELECTは阻止できませんから、SERIALIZABLEで排他することが必要になります。
（つまりXLOCK,HOLDLOCK、または、TABLOCKXということになります）

この回答への補足

もう少し裏付けがあると助かりますので、
この現象に関連する記述を見かけた方いましたら、
ご回答お願いいたします。

補足日時：2010/02/21 19:09

この回答へのお礼

ご回答ありがとうございます。

やはり、MSDNの記述は見つかりませんよね。
MSDNに明確にかかれているのが理想なのですが。。。

恥ずかしながら、MSDNブログの存在を初めて知りました。
URLのブログエントリー読ませていただきましたが、
まさに、現状で起こっていることを物語っているように思えます。

jamshid6さんの回答にある通り、XLOCK,HOLDLOCK、または、TABLOCKXを検討することになりそうです。

お礼日時：2010/02/21 19:08