行ロックを行うとテーブルのインテント排他も出来てしまう
-
2012年5月31日 2:50
行ロックに関する質問です。
SQLServer2005＋VisualStudio2005(vb.net)で開発を行っています。処理の流れは下記の通りです。
①プログラムの起動 → ②トランザクション開始 → ③条件設定を行いテーブルAのレコードを抽出し、ワークテーブルにInsertしてワークテーブルをグリッドにバインド → ④グリッドに対する追加、更新、削除 → ⑤登録、コミット
１テーブルに対する更新作業を複数の端末で同時に行いたい為に行ロックしたいのですが、③のタイミングで利用状況モニタを参照すると、下記の様なロック状態になっており、その状態では他のプログラムで操作しようとしてもロック要求がタイムアウトになってしまいます。
ロックの種類=DB／ロックモード=S／ロック状態=GRANT／要求の所有者＝Sess／インデックス＝テーブルA
ロックの種類＝TBL／ロックモード＝IX／ロック状態＝GRANT／要求の所有者＝Xact／インデックス＝テーブルA
ロックの種類＝PAG／ロックモード＝IU／ロック状態＝GRANT／要求の所有者＝Xact／インデックス＝テーブルA
ロックの種類＝RID／ロックモード＝U／ロック状態＝GRANT／要求の所有者＝Xact／インデックス＝テーブルA
RIDで抽出件数分のロックが出来ているので行ロックは行われていると思うのですが、テーブルのインテント排他でタイムアウトが発生するのを回避する方法は無いのでいしょうか？
プログラムではDataSetクラスのSqlClientを使用してデータベースの操作を行っており、③の処理はSqlDataAdapterを使用して下記のSQLを処理しています。
SELECT * FROM テーブルA WITH(UPDLOCK,NOWAIT)
WHERE 条件WITHには(HOLDLOCK,ROWLOCK)なども指定したのですが、行ロックの状態は作れなかったので、(UPDLOCK,NOWAIT)に指定しています。
すべての返信
-
2012年6月1日 7:47
RIDで抽出件数分のロックが出来ているので行ロックは行われていると思うのですが、テーブルのインテント排他でタイムアウトが発生するのを回避する方法は無いのでいしょうか？
行ロックが確実に行えている確認は取られましたか？
WHERE条件によってはエスカレーションを起こしていたり・・・
あと、WITH(UPDLOCK,ROWLOCK)ではなくWITH(UPDLOCK,NOWAIT)を選んだのは何故でしょう。
見当違いだったらすいませんが、、、
-
2012年6月2日 6:23
回答ありがとうございます。誰にも回答してもらえないのかなぁとガッカリしていたところです。本当にありがとうございます。
>行ロックが確実に行えている確認は取られましたか？
確認は、利用状況モニタで下記ロックが抽出件数分存在する事で、行えていると判断しました。方法として、それ以外思いつかなかったのですが、問題あるでしょうか？
ロックの種類＝RID／ロックモード＝U／ロック状態＝GRANT／要求の所有者＝Xact／インデックス＝テーブルA
>WHERE条件によってはエスカレーションを起こしていたり・・・
エスカレーションについては行ロックがエスカレーションすればテーブルロックに切り替わると思っているのですが、行ロックからテーブルロックに切り替わった場合、
行ロックが無くなってテーブルロックだけになるわけではないのでしょうか？両方存在するのでエスカレーションではないのかと思っていますが、念のためにトレース
フラグでの禁止は行いました。ただ、本当に禁止が有効になっているのかは判りません。
>WITH(UPDLOCK,ROWLOCK)ではなくWITH(UPDLOCK,NOWAIT)を選んだのは何故でしょう。
WITH(HOLDLOCK,ROWLOCK)や、WITH(ROWLOCK)を実行して行ロックが得られませんでした。UPDLOCKは特に明示しなくても行ロックになるとの事だ
ったので、試したところ行ロックされたので、WITH(UPDLOCK,NOWAIT)の様な形で使用しています。しかし、WITH(UPDLOCK,ROWLOCK)の組み合わせ
は行っていないので試してみます。
-
2012年6月2日 7:58
WITH(UPDLOCK,ROWLOCK)を試してみました。
WITH(UPDLOCK,NOWAIT)と同様に下記の様な状態でした。
ロックの種類=DB／ロックモード=S／ロック状態=GRANT／要求の所有者＝Sess／インデックス＝テーブルA １件
ロックの種類＝TBL／ロックモード＝IX／ロック状態＝GRANT／要求の所有者＝Xact／インデックス＝テーブルA １件
ロックの種類＝PAG／ロックモード＝IU／ロック状態＝GRANT／要求の所有者＝Xact／インデックス＝テーブルA 4件
ロックの種類＝RID／ロックモード＝U／ロック状態＝GRANT／要求の所有者＝Xact／インデックス＝テーブルA レコード件数
-
2012年6月2日 18:23モデレータ
UPDLOCK を設定した場合は、IX/IU が取得されてしまいますので、これを外すということはできないかと思います。
ロックのエスカレーションに関しましては熊野さんが書かれておられるようにエスカレーションした場合はテーブルロックがとられ、行ロックはない状態となります。
# ロックのエスカレーションの状態に関しましては、SQL Server プロファイラで Locks の Lock:Escalation のイベントをキャプチャすることで確認できると思います。今回書かれているテーブルAですが、クラスター化インデックスを持たないヒープ構造のテーブルをお使いでしょうか?
私も試してみたのですが、クラスター化インデックスを持たないテーブルですと ROWLOCK と HOLDLOCK を指定してしても行 (RID) 単位でロックはとられませんでした。
ヒープテーブルの状態で RID の共有ロックのみをとる方法があるかは時間があるときに少し調べてみようと思いますが、テーブルAにクラスター化インデックスを設定することはできないでしょうか?
# いつ調べられるかという確約ができないのですが…。クラスター化インデックスを設定した状態でROWLOCK , HOLDLOCK を指定すると熊野さんが実施されたい状態になるかと思います。
現状の状態のままからの解決策のご提示でなく申し訳ありませんが、調べてみたことを少し投稿させていただきます。
-
2012年6月4日 7:59
IX は IX 同士や IS とは互換性があるので、テーブルレベルでの IX が競合しているとすれば、競合相手はテーブルレベルで X や S を取ろうとしているということになります。また、行レベルのロックを獲得しているのであれば、その上位のテーブルレベルには IX や IS が取られることは避けられません。
どちらが問題かと言われれば、今問題にしている行ロックやテーブル IX ロックを取っている側よりも、それと競合するようなテーブルロック(広範囲なロック)を取ろうとしている方が問題ではないでしょうか？
今テーブルロックを取ろうとしているクエリを、インデックスを使ったデータアクセスができるようにチューニングすることで、テーブルロックではなく行やキーロックで処理できるようにすれば、問題のロック待ちも解消すると思います。
-
2012年6月4日 23:47
回答ありがとうございます。
遅くなって申し訳ありません。
>ロックのエスカレーションに関しましては熊野さんが書かれておられるようにエスカレーションした場合はテーブルロックがとられ、行ロックはない状態となります。
そうなんですね。勉強不足でした。ありがとうございます。
>クラスター化インデックスを設定した状態でROWLOCK , HOLDLOCK を指定すると熊野さんが実施されたい状態になるかと思います。
>現状の状態のままからの解決策のご提示でなく申し訳ありませんが、調べてみたことを少し投稿させていただきます。
早速、試してみます。ありがとうございます。
-
2012年6月5日 0:45
回答ありがとうございます。
遅くなって申し訳ありません。
>今テーブルロックを取ろうとしているクエリを、インデックスを使ったデータアクセスができるようにチューニングすることで、テーブルロックではなく行やキーロックで処理できるよう
>にすれば、問題のロック待ちも解消すると思います。
インデックスでデータアクセスできる様にチューニングしてみます。ありがとうございます。
-
2012年6月5日 1:56
皆さん色々なご指導ありがとうございました。
行ロックできる様になりました。
ただ、ロックされた部分とロックされていない部分を画面上に判別可能な様に表示し、且つロックされた部分を編集不可にするなどの処理を
埋め込む必要からデータベースの機能だけでは無理だったのでプログラミングで対応する事にしました。
勉強になりました。ありがとうございました。
.png)
