UPDATE文で発生するデッドロックについて、教えてください。

はじめまして。
SQL　SERVER初心者の為、ご指導の程お願いいたします。

UPDATE文で発生するデッドロックについて教えてください。

現在、ストアドプロシージャ内で、
以下のUPDATE文を使用しています。

UPDATEテーブルＡ
SET 更新済フラグ='1'
WHEREロック時間 =パラメータ．ロック時間
ANDロックユーザー=パラメータ．ロックユーザー
AND ＩＤ= パラメータ．ＩＤ

※Where句に使用する、ロック時間・ロックユーザー・ＩＤは、
　キー項目では無く、インデックスも使用しておりません。
　ただし、対象データを一意に識別できる項目ではあります。
　
このストアドを含む処理を、ほぼ同時実行すると
このUPDATE文で「LOCK　TIMEOUT」が発生し、
デッドロックとなってしまいます。

UPDATEの対象となるデータ件数は、ともに５００～６００件です

また、対策としましてロックのヒント文を下記のように設定し、
WHERE句に設定している、ロック時間・ロックユーザー・ＩＤに
対して、インデックスを作成してみましたが、
同様に、デッドロックが発生してしまいます。

UPDATEテーブルＡ （with　rowlock）
SET 更新済フラグ='1'
WHEREロック時間 =パラメータ．ロック時間
ANDロックユーザー=パラメータ．ロックユーザー
AND ＩＤ= パラメータ．ＩＤ


SQL　Serverに詳しい方、お力になっていただければ、光栄です。
よろしくお願いいたします。

No.7 回答者： siddhaartha 回答日時： 2007/01/18 18:07

回答が遅れました。

色々なアドバイスが出てきていますので、一度切り分けて
考えてみてはいかがでしょうか。

・SPから当該UPDATE文のみを抜き出して実行してもデッドロックになるか？
・TABLOCKにした場合でも同じか？
・ロックの待ち時間を長くしてみてもTIMEOUTが発生するか？

をまずは試してみてください。
その結果によって原因がわかるかもしれませんし、
別のアドバイスが出来るかもしれません。

No.6 回答者： y_y_co 回答日時： 2007/01/18 17:59

SQLServerは、

重複していなく、インデックスになっているキー（主キー含む）、
に対して、WHERE句を設定すると行ロックとなり、
重複している OR インデックスになっていない、フィールドに対して、
WHERE句を設定するとページロックまたは、全件検索となる仕組となっています。
デッドロックは、複数のＳＱＬ文が発行された時におきる現象ですが、
今回、この「UPDATE」以外の、ＳＱＬ文はどうなのでしょうか？
基本的にSQLServerは、適切なテーブル設計、チューニングができていれば目だった、デッドロック、遅延等ほとんどおきないように、なってはいますが・・・
基本中基本が原因であれば、ＲＤＢのスキル習得からオススメします。

No.5 回答者： chukenkenkou 回答日時： 2007/01/18 02:53

>Where句に使用する、ロック時間・ロックユーザー・ＩＤは、

>キー項目では無く、インデックスも使用しておりません。
>ただし、対象データを一意に識別できる項目ではあります

あなたには一意と判断できても、RDBMS側には一意と判断できず、
全件検索することになります。

>このUPDATE文で「LOCK　TIMEOUT」が発生し、
>デッドロックとなってしまいます

SQL Serverのデッドロックには詳しくないですが、RDBMSによっては
「たすき掛け」と「排他待ちが一定時間を越えた」を切り分けて
メッセージを出してくれるものも少なくありません。
今回の場合は、後者ではないのでしょうか？

>UPDATEの対象となるデータ件数は、ともに５００～６００件です

UPDATEを500～600件発行するとき、COMMITは発行していないのですよね？
インデクスがなければ、物理順で同じ経路を繰り返し探すことになるので
タイムアウト等が発生して当然だと思いますが？

>ロック時間・ロックユーザー・ＩＤに
>対して、インデックスを作成してみました

どういうインデクスを定義したのでしょうか？

(ロック時間,ロックユーザー,ＩＤ)で1本のインデクスを定義したの
でしょうか？

この回答へのお礼

chukenkenkouさん、ご返答ありがとうございます。

いくつかご回答につきまして、いくつか質問をさせてください。

＞あなたには一意と判断できても、RDBMS側には一意と判断できず、
　全件検索することになります。
これについてですが、ロック時間・ロックユーザー・ＩＤが、
テーブルのキー項目となっている場合は、全件検索は行われない
という認識でよろしいでしょうか？
初歩的な質問で申し訳ありません。

>どういうインデクスを定義したのでしょうか？
＞(ロック時間,ロックユーザー,ＩＤ)で1本のインデクスを定義したの
　でしょうか？

なにぶん初心者なもので、インデックスの作成は手探り状態で
行いました。
エンタープライズマネージャーより、該当テーブルのデザインより、
（ロック時間,ロックユーザー,ＩＤ）のインデックスを作成しました。

１本のインデックスも作成することが可能なのですか？？？
また、インデックスのよい作成方法をご存知であれば、
教えてください。

現在、下記のようなテーブルレイアウトです。

識別コード，年度，月度，○○番号　・・・・・　ロック時間，ロックユーザー，ＩＤ

※識別コード，年度，月度，○○番号までが、キー項目となります。

よろしくお願いします。

お礼日時：2007/01/18 14:04

No.4 回答者： E-HR33 回答日時： 2007/01/17 17:05

自分も初心者ではありますが同じようなことがあったので参考までに書かせていただきます。

確かＬＯＣＫＴＩＭＥＯＵＴはデッドロックではなかったような気がします。
ＬＯＣＫＴＩＭＥＯＵＴということは順序的に先になった更新処理が遅すぎて後続が待ちきれずにＴＩＭＥＯＵＴしたのではないでしょうか？
トランザクション開始処理時のオプションで処理待ち時間が変えられるはずなので、試してみてはいかがでしょうか？

以上、参考になれば幸いです。

No.3 回答者： siddhaartha 回答日時： 2007/01/17 14:26

再度＃１です。

SQL Serverの環境が手元に無いので試していませんが、
With句の指定って
×：UPDATE テーブルＡ （with　rowlock）
↓
○：UPDATE テーブルＡ with　（rowlock）

じゃないかなぁ・・・

ちゃんと行ロックになっているか、EnterPriseManagerなり
sp_lockで見るなりして確認してみてください。

この回答への補足

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

すみません。記述を間違えていました。
プログラム上は、with　（rowlock）となっています。

補足日時：2007/01/17 15:02

No.2 回答者： siddhaartha 回答日時： 2007/01/17 14:10

＃１です。

追加質問にお答えします。

SQL Serverは通常、ページ単位のロックを行います。
(確か7.0からはオプティマイザが動的に最適なロック単位を
選択してくれるようになったかも・・・)

ですので、行Ａ・Ｘが違っても同一ページ(確かデフォルトは8KB)に
入っていれば、Aを更新ロックする際にXもロックされます。

しかし、行ロックオプションを指定しているとのことですので
それは起こらないはず。なのでTransactionを開放してないからとか
ストアド内で他のテーブルも更新していて順序がたすきがけに
なったとか、の原因を疑った方が良いとおもいます。

この回答へのお礼

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

度々の質問で大変恐縮ですが、処理内容を確認したところ、、、
ＳＰ内の処理では、同一テーブルに更新しにいく処理は存在しますが、
同一行への更新は行われないものとなっています。

ヒント文、ROWLOCKを使用しておりますが、
同一テーブルへの更新が行われる場合、デッドロックの可能性が
考えられるということでしょうか？？？

よろしくお願いいたします。

お礼日時：2007/01/17 15:11

No.1 回答者： siddhaartha 回答日時： 2007/01/17 12:02

Updateの行の順序は保証されていません。

(特にPキー以外の列を含む条件の場合)
なので、処理A・処理Bで同一行が複数対象になった場合
更新行がたすきがけになればデッドロックが発生します。

処理A・Bで絶対に同一行が更新対象になりませんか？
ロック・ユーザーIDが重なることはなさそうだけど・・・

どうしてもダメなら行ロックではなくテーブルロックにして
試してみてください。

この回答へのお礼

ご回答ありがとうございます。
テーブルロックを試してみます。

ご回答に関しまして、１つ質問させていただいても
よろしいでしょうか？

＞処理A・処理Bで同一行が複数対象になった場合
＞更新行がたすきがけになればデッドロックが発生します。

処理A・処理Bとも、同一行への更新は無いのですが、
やはり、たすきがけ状態になり、
デッドロックが発生してしまうのでしょうか？

例）処理Ａ実行時
　　行Ａ・Ｂ・Ｃが更新対象行
　　
　　処理Ｂ実行時
　　行Ｘ・Ｙ・Ｚが更新対象行

よろしくお願いいたします。

お礼日時：2007/01/17 12:33