SQL Server2005で変換デッドロックエラー

質問

データーベースに関して、知識があまりないもので、的外れなことや
情報不足があったらすみません。

SQLServer2005で同一のUPDATE文を2人で実行しようとしたところ、変換デッドロックが発生してしまいました。

該当するSQL文は
１ SELECT ～ FROM xxxx FOR UPDATE OF ～
２ UPDATE xxxx　
という２つのSQL文です。このSQLはアプリケーションが自動で発行生成しているSQLなので自分では変更は出来ないです。

エラーを確認するため、Profilerで確認したところ、以下のようになっていました。

<inputbuf>
FETCH API_CURSOR000000000000FA5C
</inputbuf>
</process>
</process-list>
<resource-list>
<ridlock fileid="1" pageid="435113" dbid="11"
objectname="xxx.dbo.xxxx(テーブル名)" id="lock4eccf240" mode="U"
associatedObjectId="54650898874368">
<owner-list>
<owner id="processb491f8" mode="S"/>
<owner id="processdf4898" mode="U"/>
<owner id="processdf4898" mode="U"/>
</owner-list>
<waiter-list>
<waiter id="processdf4898" mode="X" requestType="convert"/>
<waiter id="processb491f8" mode="U" requestType="wait"/>
</waiter-list>
</ridlock>
</resource-list>
</deadlock>
</deadlock-list>

共有ロック → 更新ロック → 排他ロックという順番でロックをかけようとしたところ、更新ロックから排他ロックに変換しようとしたところで
エラーになっているみたいです。

これらを解消するのに何か良い方法はありますでしょうか？

回答No.2

kero_mio

SQLやProfilerのトレースではなく、具体的に、これらの処理を実装している、ソースを見せて頂けませんか？
（まずい箇所は加工して頂いて結構なので）
そういう意味で、この補足をお願いしてたのですが、言い方が悪くてすいません。

ソースを見ない限り、トランザクションの単位もわかりませんし、
分離レベルの状態もどうなっているのか予測がつきません。

＞＞また、トランザクションは、１つ目のSELECT FROM UPDATE FORと
＞＞２つめのUPDATE文で違うトランザクションなのでしょうか？
＞＞（SQL Profilerからの結果では予測ができませんでした）
＞COMMITまでが一つのトランザクションならば、一つのトランザクションだと思います。

だと思いますだと、はっきりしたことがご回答できないので、やはりストアドやSQL、もしくは、他のプログラミング言語(VBやC#等)で記述されたものが公開されてないと、情報が少なくて、回答に困ってしまうと思いますので、情報をもう少しだけ精査して頂き、そのうえで対処方法をご回答いたします。

すべては、「ソースに始まり、ソースで終わる」　です。
宜しくお願いします。

補足

説明がうまく出来るか分からないですが、

使用しているのはパッケージソフトです。
GUI操作で、入力フィールドやプッシュボタン等、いくつかの部品を組み合わせて画面を作成していきます。
作った画面を動かす時に、どのようなSQLが発行されているかを知る手段は
プロファイラー等で見るしかないです。

ただ、別のアプローチでデッドロックを解消出来そうです（画面の構成を変える）。
なので、うまくいくようでしたら、この問題をクローズにしようと思います。

うまく説明できてないのと、情報をあまりだせない中で、お付き合いしていただいて、大変ありがとうございました。

回答No.1

kero_mio

下記、宜しかったら情報頂けますか？

・トランザクションの分離レベルがわかれば教えて下さい。
　（具体的に）

　READ UNCOMMITTED(非コミット読み取り)
　 READ COMMITTED(コミット済み読み取り)
　 REPEATABLE READ(再読み込み可能読み取り)
　 SERIALIZABLE(直列化)

・共有ロック → 更新ロック → 排他ロック
　のイメージがわかないので、具体的にどのようなコードで
　これらのロックを実現したのか教えて下さい。

・また、トランザクションは、１つ目のSELECT FROM UPDATE FORと
　２つめのUPDATE文で違うトランザクションなのでしょうか？
　（SQL Profilerからの結果では予測ができませんでした）

それから、推測になりますが、恐らく、SELECT FROM UPDATE FORで
対象テーブルに対し、XLOCKかUPDLOCKなどのロックヒントを与えるか
トランザクションの分離レベルを変更することでデッドロックを回避することができますので、問題のSQLやプログラムを可能な範囲で構いませんので、UPして頂けますか？

宜しくお願いします。

補足

御回答ありがとうございます。自分の分かる範囲で答えていきます。

前提として、このトランザクションはデータを保存するときに、アプリケーションが自動で発行しますが、アプリケーションはブラックボックスなので、発行するSQL文は変更等は出来ないです。

現状では、問題がアプリ、SQLServer、設定のいずれの問題なのか区別出来ていない状態です。

問題になってるSQLのトレースログは
「http://groups.google.co.jp/group/deadlock_SQLserver?hl=ja」
にExcel形式で上げておきました。
シートが２つあり、1つ目が問題のSQLを単独で実行したときのトレースログで、２つ目が１つ目のSQLを二人で同時に実行したときに発生するデッドロックのトレースログです。

・トランザクションの分離レベルがわかれば教えて下さい。
書いた時点では、READ COMMITTED(コミット済み読み取り)を使っていましたが、
現在はREAD COMMITTED SNAPSHOTを使っています。理由はアプリ側がSNAPSHOTを推薦しているようです。
ただ、READ COMMITTED SNAPSHOTを使っても、デッドロックは相変わらず発生しています。

・共有ロック → 更新ロック → 排他ロックのイメージがわかないので、具体的にどのようなコードで
これらのロックを実現したのか教えて下さい。

発行されているSQL文は２つのようです。
1 SELECT xxxxxx FROM TABLE_NAME WHERE xxxxxx=@P1 FOR UPDATE OF xxxxxx
2 UPDATE TABLE_NAME SET xxxxxx=@P1 WHERE xxxxxx=@P2
対象としているテーブルは同じテーブルです。

自分もログを見ただけで合っているか分からないのですが、
「共有ロック - 獲得」→「更新ロック - 獲得」→「共有ロック - 解除」
→「排他ロック - 獲得」→ 「更新ロック - 解除」→「排他ロック - 解除」というように推移しているように見えます。
詳しくはログを見ていただくと分かると思います。

・また、トランザクションは、１つ目のSELECT FROM UPDATE FORと
２つめのUPDATE文で違うトランザクションなのでしょうか？
（SQL Profilerからの結果では予測ができませんでした）

COMMITまでが一つのトランザクションならば、一つのトランザクションだと思います。

情報不足があるようでしたら出来るだけお伝えするつもりです。
何か分かることがあるようでしたら是非教えていただければと思います。