SELECT FOR UPDATEについて

Question

お世話になります.

select for update文で、行単位のロックがかかるのかを検証しているのですがうまくいきません。

VS2008から以下のストアドに接続し、”FETCH NEXT from @cur”の行でブレークポイントをおき
デバック実行で停止させ、次にSQLサーバのクエリ―アナライザーで以下のSQL文を実行させたのですが、
更新されてしまいます。

なにか間違っているのでしょうか。
御教授ください。

--SQL文
update orders set EmployeeID=9 where orderid=10248

--ストアド
ALTER PROCEDURE dbo.HelloWorld
AS

Declare @cur  cursor
 
BEGIN TRAN

set @cur =  cursor for select * from orders where orderid=10248 for update
open @cur

FETCH NEXT from @cur

close @cur
COMMIT TRAN

Answer 1

TAKAKUN様
はじめまして。

確かに以下ではロックされませんでした。

set @cur =  cursor for select * from orders where orderid=10248 for update

が、以下では行ロックを確認できました。

set @cur =  cursor for select * from orders WITH (UPDLOCK) where orderid=10248

FOR UPDATEは使用したことがないのでなんとも説明できませんが。。

何かの参考になれば幸いです。

---

MCITP(Database Developer/Database Administrator) MCPD(Web Developer/Windows Developer)

Answer 2

ManagementStudioの利用状況モニタにてロックの種類を確認しましたが、以下の場合は、共有ロックでした。

set @cur =  cursor for select * from orders where orderid=10248 for update

何かの参考になれば幸いです。

---

MCITP(Database Developer/Database Administrator) MCPD(Web Developer/Windows Developer)

Answer 3

ノブヒデさま、ご返事ありがとうございます。

>set @cur =  cursor for select * from orders WITH (UPDLOCK) where orderid=10248

確かにロックされました。勉強になります。

私も利用状況モニタで、ロックの種類を確認しよう見てみたのですが、いま一つどこを確認したらよいのかわかりませんでした。教えていただけるとありがたいです。

押していただいたT-SQLで満足してるのですが、select for updateについてもう少し調べて結果報告させていただきます。わからない場合は、ノブヒデさんの内容を「回答としてマーク」として終了させてもらいます。

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

 

Answer 4

ManagementStudioから下記クエリを実行し、待機時間中に、利用状況モニタにて「プロセス情報」「プロセスによるロック」を確認しました。

－－－－－－－－記－－－－－－－－
Declare @cur  cursor
 
BEGIN TRAN

set @cur =  cursor for select * from orders where orderid=10248 for update
open @cur

FETCH NEXT from @cur

--処理30秒待機
WAITFOR DELAY '00:00:30'

close @cur
COMMIT TRAN

－－－－－－－－－－－－－－－－－

参考になれば幸いです。

---

MCITP(Database Developer/Database Administrator) MCPD(Web Developer/Windows Developer)

Answer 5

なにか間違っているのでしょうか。
御教授ください。

デフォルトの分離レベルはREAD COMMITTEDです。この状態でトランザクションを開始すれば、他のトランザクションからの更新を防ぐことはできません。状況に応じてREPEATABLE READ、もしくはSERIALIZABLEの分離レベルを用いれば良いと思います。

---

★良い回答には回答済みマークを付けよう！ わんくま同盟 MVP - Visual C# http://blogs.wankuma.com/trapemiya/

Answer 6

trapemiya様ご返事ありがとうございます。

トランザクションのロックより、ロックヒントのほうが優先度が高いのでは・・・と思うのですが、

トランザクションのロックが優先されるなら、for updateに意味はあるのでしょうか？

Answer 7

SQL Serverのカーソルはデフォルトで更新可能ですから、for updateを付けることに意味はありません。
私が先に書いたように分離レベルとしてREPEATABLE READやSERIALIZABLEを用いると変換デッドロックが発生しやすくなりますが、今回の例ではそのトランザクション内に更新処理が無いのでトランザクションが終了するまで共有ロックが保持されるだけです。変換デッドロックが心配な場合にはUPDLOCKヒント使えば良いと思います。
一方、更新する予定が無いのにUPDLOCKヒントを使うというのは、他のトランザクションがブロックされる時間が長くなる可能性がありますので、注意する必要があります。

---

★良い回答には回答済みマークを付けよう！ わんくま同盟 MVP - Visual C# http://blogs.wankuma.com/trapemiya/