SQLServer2008 データロック異常について

Question

SQLServer2008に対してSELECT文によるデータ取得をしようとしたところ対象としていない部分まで行ロックされています。

SELECT文の文字数（最大文字数）によるロックの影響の変化はあるのでしょうか？

設定は以下の通りです。

SELECT部分：対象となる400列すべて

FROM部分：特定の１テーブル

WITH部分： (UPDLOCK, ROWLOCK, NOWAIT, READPAST)

WHERE部分：1行ずつ特定するためキー項目（6項目）をすべてANDでつなげ、それをロックデータ行数（65行）だけORでつなげている

ORDER BY部分：2列分設定

ロックのエスカレーション：無効

なお、WHERE部分の項目数を、４項目に減らしたり、ORDER BY部分を1列に減らすとORDER BY部分が1000行でも対象のデータのみをロックしました。

Answer 1

こんにちは。

>SELECT文の文字数（最大文字数）によるロックの影響の変化はあるのでしょうか？

文字数でロックの影響が変化するというのは聞いたことがありません。
もう少し情報が欲しいです。

>対象としていない部分まで行ロックされています。
65行以外にロックされる行がどの程度存在しますか？
もしくはTableロックにエスカレーションされているのでしょうか？
また、どうやって「対象としていない部分まで行ロックされている」と判断したか教えてください。

>WHERE部分：1行ずつ特定するためキー項目（6項目）
は、Clustered Indexと考えて良いですか？

>ORDER BY部分：2列分設定
この列にはなにかしらのIndexが設定されていますか？（または、主キーの一部ですか？）

>ORDER BY部分が1000行でも対象のデータのみをロックしました。
これの意味がわかりません。
ORDER BY句に1000行記述したということですか（1列ですが。。。）

以上、解決の近道となると思いますので、よろしくお願い致します。

Answer 2

Tetsuaki　Uchidaさん

返信ありがとうございます。

>もしくはTableロックにエスカレーションされているのでしょうか？
>また、どうやって「対象としていない部分まで行ロックされている」と判断したか教えてください。

ロックのエスカレーションは起こらないようにしている為起こっていません。

対象でない部分までの行ロックは[sp_lock]によって更新ロックされている行(KEY)を確認しました。

>>WHERE部分：1行ずつ特定するためキー項目（6項目）
>は、Clustered Indexと考えて良いですか？

単純にキー項目を「項目」＝項目値　AND ・・・と指定し、次の行の際に「OR」で呼び出しています。

>>ORDER BY部分：2列分設定
>この列にはなにかしらのIndexが設定されていますか？（または、主キーの一部ですか？）

主キーではない列を2つ指定しています。

>>ORDER BY部分が1000行でも対象のデータのみをロックしました。
>これの意味がわかりません。

ORDER BY部⇒WHERE部分のロックする行数の間違いでした。

また、さらに調べてみたところ

クエリ最適化で実行プランが

Index Seek ⇒Filterとなっていおり、

更新ロックされている行はIndex Seekの実際の行数と一致していました。

また、SQL　Server2000以降で対応できる方法を教えてください。

よろしくお願いします。

 

Answer 3

うーん、「キー項目（6項目）」について聞き方がちょっとまずかったかもしれません。

実際に聞きたかったのは
>WHERE部分：1行ずつ特定するためキー項目（6項目）
は主キーと等しいという認識でよいですか？
また、主キーはクラスター化インデックスですか？

ということでした。

なぜ主キーおよびクラスター化 、非クラスター化インデックスにこだわるかというと、
SQL Serverの行ロックの結果が変わるからです。

ですが、
>Index Seek ⇒Filterとなっていおり、

とあるので、おそらく（クラスター化に関係なく）主キーとは等しくないですよね。
主キーと等しければFilterが介在することは無いと思いますので。


さて、本件の原因ですが
SQL Serverの行ロックの仕様と思われます。
行ロックは抽出結果の行ではなく、Index Seek(もしくはScan)の対象となったIndex内のキーがロックされるようです。
なので、Filter前のIndex Scanの「実際の行数」分ロックがかかってしまっているのだと思います。

# 抽出結果から行識別子（RID）を特定して行識別子にロックをかけるということはしないということです。
# sp_lockのType列の「KEY」はインデックス内のロックであることを指しています。

# ■テーブル ヒント (Transact-SQL)
# http://msdn.microsoft.com/ja-jp/library/ms187373.aspx
# の「説明」あたりにちょっとだけ書いてあります。


回避策としては、
>WHERE部分：1行ずつ特定するためキー項目（6項目）
をクラスター化インデックスの主キーにするのが一番安全ではないでしょうか。
確実にロック対象が確定できると思いますし。

なお、非クラスター化インデックスにした場合、他にクラスター化インデックスが存在すればそちらもロック対象になります。
この場合、実際にロックされている行と、sp_lock、およびsys.dm_tran_locksの結果は全く異なります。（2種類のインデックスがロックされているので）

以上、参考になりましたら幸いです。


# 最初の質問文に書かれているSQL Server2008を前提としています。2000は使用経験が無いのでわかりませんが、おそらく原因・回避策は同じではないかと思います。
# 上記は実際に400列もあるテーブルで試していません。あくまで私の知識内でのことだとご理解ください。

Answer 4

Tetsuaki　Uchidaさん

返信ありがとうございます。

>>WHERE部分：1行ずつ特定するためキー項目（6項目）
>は主キーと等しいという認識でよいですか？
>また、主キーはクラスター化インデックスですか？

WHERE部分のキー項目は全て主キーでクラスター化インデックスです。（SQLのオブジェクトエクスプローラで対象のテーブルのインデックスのPK_・・・（クラスタ化）のインデックスキー列で確認）

さらに、ここで選択しているキー項目がそのテーブルの全ての主キーで他の主キーはありません。

それでも、上記のようなことが起こっています。

他に原因は考えられないでしょうか。

以上、よろしくお願いします。

Answer 5

ふむむむ・・・。
おかしいですね・・・。

クラスター化インデックスの主キーを抽出条件にしていれば
実行プランにFilterが出てくることはないと判断したのですが・・・。
しかしなぜFilterが出てくるのか不思議です。

解決できるかどうかわからないですが、
以下、確認してみて、教えていただけますか？

・WHERE句に記述ミスはありませんか？
・WHERE句に、キャストをしている部分はありませんか？
（すべての『「項目」＝項目値』　の左辺と右辺は同じデータ型ですか？）
・実際の実行プランは、
SELECT　←　Sort　←　Filter　←　Clusterd Index Seek(Clusterd)
になっていますか？
違う場合は教えてください。
・上記の実行プランになった場合の、
Clusterd Index Seek(Clusterd)　の実際の行数
Filter　の実際の行数
テーブル全体の行数
WHERE句に指定した行数（期待される行数）
を教えてください。

以上、解決できるかどうかわからないですが教えてください。
よろしくお願い致します。

Answer 6

Tetsuaki　Uchidaさん

返信ありがとうございます。

>・WHERE句に記述ミスはありませんか？
>・WHERE句に、キャストをしている部分はありませんか？
>（すべての『「項目」＝項目値』　の左辺と右辺は同じデータ型ですか？）

もともとC#で記述して作成されたSQL文をSQLServerで実行している為ミスはありません。
>・実際の実行プランは、
>SELECT　←　Sort　←　Filter　←　Clusterd Index Seek(Clusterd)
>になっていますか？
>違う場合は教えてください。

実行プランは

SELECT ←　Parallelism ←　Sort ←Nested Loops ←　Filter　← Index Seek (NonClustered)

                                                                 ←　キー参照　(Clusterd)

 となっています。（主キーしかないのにNonClusteredが使用されています）

>・上記の実行プランになった場合の、
>Clusterd Index Seek(Clusterd)　の実際の行数
>Filter　の実際の行数
>テーブル全体の行数
>WHERE句に指定した行数（期待される行数）
>を教えてください。
Index Seek (NonClustered)となっていますが実際の行数：74,361行

Filterの実際の行数：150行

テーブル全体の行数：156,056行

WHERE句に指定した行数：150行

となっています。

以上、よろしくお願いします。

 

Answer 7

>実行プランは

>SELECT ←　Parallelism ←　Sort ←Nested Loops ←　Filter　← Index Seek (NonClustered)

                                                                 ←　キー参照　(Clusterd)
                                                                 
これ、非クラスター化インデックスを使っていますね。
Filterの原因はこれだと思います。

SSMSのオブジェクトエクスプローラーから、
対象テーブルのインデックスノードを開くと非クラスター化インデックスはいくつありますか？

また、WHERE句に記述したカラムに関係する
非クラスター化インデックスはいくつありますか？

また、それらは必要なものですか？

# 込み入った質問で恐縮です。

何が起きているかというと、
主キーであるクラスター化インデックス以外に存在する非クラスター化インデックスが使用されているためFilterが発生しており、
期待値と異なる行ロックが発生しているようです。
非クラスター化インデックスを作成している理由がわからないので何とも言えませんが
必要ないインデックスならば削除した方が良いと思います。

# 主キーであるクラスター化インデックスとWHERE句の抽出条件が等しいならば
# 通常、そのクラスター化インデックスが使用されるはずなのですが、非クラスター化インデックスの作り方によっては変わってくるのかもしれません。

Answer 8

Tetsuaki　Uchidaさん

返信ありがとうございます。

質問内容の返答をさせて頂きます。

>SSMSのオブジェクトエクスプローラーから、
>対象テーブルのインデックスノードを開くと非クラスター化インデックスはいくつありますか？

非クラスター化インデックスは16個あります。

>WHERE句に記述したカラムに関係する
>非クラスター化インデックスはいくつありますか？

WHERE句に記述したカラムに関係する非クラスター化インデックスは2個あります。

>また、それらは必要なものですか？

必要なものと考えられます。

（すみません。最初にこれも作成した方がいない為このような返答になってしまいました。）

以上、よろしくお願いします。

 

Answer 9

>必要なものと考えられます。
>（すみません。最初にこれも作成した方がいない為このような返答になってしまいました。）

了解です。

アプリケーションの内容（要件・仕様など）をすべて把握することはフォーラムでは不可能なので、
これ以上ここで最適解を考えるのは難しいかなと思います。。。

でも、原因ははっきりわかったと思うので後は私が知っている範囲のアドバイスだけあげておきます。

1.行ロックをSQL Serverに任せるのでは無く、自前で管理する（排他管理テーブルを作成するなど）

行ロックは実際はインデックスのロックであったり、実行プランに左右されたりということがあるので
確実に行ロックを制御するには自前で管理するというケースも多いです。
工数はかかりますがテーブル定義、インデックス、条件の変更等の時に柔軟に対応できるので検討の余地があります。

2.必ず主キーであるクラスター化インデックスを使用するようにプランを強制する。

■プラン強制の使用によるクエリ プランの指定
http://msdn.microsoft.com/ja-jp/library/ms190727.aspx
に詳しく乗っていますので検討してみてください。
# ただし、SQL Server2000 で使用可能かは調査してください。

これくらいかなと思います。

以上、がんばってください。

Answer 10

Tetsuaki　Uchidaさん

返信ありがとうございます。

上記のことを参考に作成してみます。

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