SQLSERVER デットロックを防ぐ②

デットロックが発生原因について、もうひとつ付け加えたい。

■現象

２つのセッションがあり、１つのテーブルがあるとする。

①あるセッションは、テーブルにINSERTを実行

②あるセッションは、条件指定でSELECTを実行

ここで、デットロックが発生したとする。

①と②は、まったく条件が異なるため、デットロックしないはずでは。。

■原因

テーブルには、INDEXが設定されており、INSERTが
COMMITされるまでは、その INDEX を利用したSELECTがロック待ちになる。
（INDEXの木構造を再構築中のためではないか）

■対策

INDEXを参照しないでSELECTする。
利用頻度が低い INDEX は、削除した方が、INSERTも早くなる。
INDEXは、パフォーマンスを大幅に改善することがあるが、
デットロックを引き起こす原因になることも忘れてはならない。

SQLSERVER NOLOCKオプションの有効性

今回は、NOLOCKオプションについて、いくつか書いてみたいと思う。

■効果
あるテーブルがUPDATEされ、COMMIT待ちの状態であるが、
NOLOCKオプションをつけると、COMMIT前のデータがSELECTできてしまう。

つまり、複数のセッションで処理する場合、待ち時間がなくなるため
パフォーマンスアップにつながるわけである。

■弊害
COMMIT前のデータが読めるというのは、ダーティリードとも呼ばれる。
何が問題かというと、ROLLBACKされたときに、読み取ったデータが
存在しなくなるということである。または存在しても異なっている場合がある。

取得した値を利用して、処理をしてみたが、あるタイミングで、その値は
無効なものとなっている。前提が崩れているとすれば処理結果も信用できない。

また、NOLOCKオプションを付ける前だと、ロック待ちとして排他されていたが
付けた後だと、同時実行されるようになり、整合性に問題がでる場合がある。

■使用場所
①別記「SQLSERVER　デットロックを防ぐ」にあるように、デットロック回避に使う。
　→NOLOCKオプションでSELECT後、一意キーでアクセスすることにより、
　　ロック範囲を狭めるのと、整合性の確認、パフォーマンスアップにつながる。

②ROLLBACKがほぼなく、パフォーマンスアップしたい箇所につかう。
　または、ROLLBACKされても整合性に問題が起きない箇所。

在庫データの参照など、ダーティリード時の影響が大きい箇所などには適用せず
リスクが比較的少ない箇所から実験するのもひとつの手である。



　

SQLSERVER デットロックを防ぐ

２つのセッションが１つのテーブルにアクセスするとき、異なる抽出条件に
なっているにも関わらず、そこでデットロックが発生することはないだろうか？

■状況
たとえば、テーブルのカラムが
ＩＤ
GROUP
NAME
AGE
となっていて、

セッション①
WHERE ID = 1

セッション②
WHERE ID = 2

などとアクセスする。
このとき、別のデータを取得しようとしているので、ぶつからないはずである。

■原因
いくつかあるのだが、以下のような場合である。

①ID、GROUPのカラムで複合一意キーを指定していたが、どちらか片方で
　アクセスしていた。

②一意キー以外の条件でアクセスすると、関係ないレコードまでロックされる
　（SQLSERVERは、勝手に広範囲をロックしてしまうようだ）

■対応策

①複合一意キーの場合は、その定義順序で、定義されているカラムを必ず指定すること

② 一意キー以外の条件でアクセスする場合、WITH (NOLOCK) オプションでまず
　データを取得する。その後、取得結果をカーソルでまわしながら、一意キーで
　再度SELECTする。
　※ WITH (NOLOCK) オプションは、ROLLBACK時の影響があることから乱用は避けたい

このような対応で、ロック範囲は意図した通りとなり、デットロックは軽減される。

■参考

ロック範囲が意図した範囲か確認する方法として、以下がある。

「Management Studio」を２つ起動させておく。

①片方で、範囲外と思われるレコードをUPDATE

②もう片方で、確認したいSELECT文を流す（ 調べたいWHERE句を指定して )

以上を実行してみて、固まる（ロック待ち）となれば、ぶつかっているし、
無事にSELECTできれば、ロック範囲は重なっていない。