デットロックが発生原因について、もうひとつ付け加えたい。
■現象
2つのセッションがあり、1つのテーブルがあるとする。
①あるセッションは、テーブルにINSERTを実行
②あるセッションは、条件指定でSELECTを実行
ここで、デットロックが発生したとする。
①と②は、まったく条件が異なるため、デットロックしないはずでは。。
■原因
テーブルには、INDEXが設定されており、INSERTが
COMMITされるまでは、その
INDEX
を利用したSELECTがロック待ちになる。
(INDEXの木構造を再構築中のためではないか)
■対策
INDEXを参照しないでSELECTする。
利用頻度が低い
INDEX
は、削除した方が、INSERTも早くなる。
INDEXは、パフォーマンスを大幅に改善することがあるが、
デットロックを引き起こす原因になることも忘れてはならない。
2012年12月9日日曜日
SQLSERVER NOLOCKオプションの有効性
今回は、NOLOCKオプションについて、いくつか書いてみたいと思う。
■効果
あるテーブルがUPDATEされ、COMMIT待ちの状態であるが、
NOLOCKオプションをつけると、COMMIT前のデータがSELECTできてしまう。
つまり、複数のセッションで処理する場合、待ち時間がなくなるため
パフォーマンスアップにつながるわけである。
■弊害
COMMIT前のデータが読めるというのは、ダーティリードとも呼ばれる。
何が問題かというと、ROLLBACKされたときに、読み取ったデータが
存在しなくなるということである。または存在しても異なっている場合がある。
取得した値を利用して、処理をしてみたが、あるタイミングで、その値は
無効なものとなっている。前提が崩れているとすれば処理結果も信用できない。
また、NOLOCKオプションを付ける前だと、ロック待ちとして排他されていたが
付けた後だと、同時実行されるようになり、整合性に問題がでる場合がある。
■使用場所
①別記「SQLSERVER デットロックを防ぐ」にあるように、デットロック回避に使う。
→NOLOCKオプションでSELECT後、一意キーでアクセスすることにより、
ロック範囲を狭めるのと、整合性の確認、パフォーマンスアップにつながる。
②ROLLBACKがほぼなく、パフォーマンスアップしたい箇所につかう。
または、ROLLBACKされても整合性に問題が起きない箇所。
在庫データの参照など、ダーティリード時の影響が大きい箇所などには適用せず
リスクが比較的少ない箇所から実験するのもひとつの手である。
■効果
あるテーブルがUPDATEされ、COMMIT待ちの状態であるが、
NOLOCKオプションをつけると、COMMIT前のデータがSELECTできてしまう。
つまり、複数のセッションで処理する場合、待ち時間がなくなるため
パフォーマンスアップにつながるわけである。
■弊害
COMMIT前のデータが読めるというのは、ダーティリードとも呼ばれる。
何が問題かというと、ROLLBACKされたときに、読み取ったデータが
存在しなくなるということである。または存在しても異なっている場合がある。
取得した値を利用して、処理をしてみたが、あるタイミングで、その値は
無効なものとなっている。前提が崩れているとすれば処理結果も信用できない。
また、NOLOCKオプションを付ける前だと、ロック待ちとして排他されていたが
付けた後だと、同時実行されるようになり、整合性に問題がでる場合がある。
■使用場所
①別記「SQLSERVER デットロックを防ぐ」にあるように、デットロック回避に使う。
→NOLOCKオプションでSELECT後、一意キーでアクセスすることにより、
ロック範囲を狭めるのと、整合性の確認、パフォーマンスアップにつながる。
②ROLLBACKがほぼなく、パフォーマンスアップしたい箇所につかう。
または、ROLLBACKされても整合性に問題が起きない箇所。
在庫データの参照など、ダーティリード時の影響が大きい箇所などには適用せず
リスクが比較的少ない箇所から実験するのもひとつの手である。
SQLSERVER デットロックを防ぐ
2つのセッションが1つのテーブルにアクセスするとき、異なる抽出条件に
なっているにも関わらず、そこでデットロックが発生することはないだろうか?
■状況
たとえば、テーブルのカラムが
ID
GROUP
NAME
AGE
となっていて、
セッション①
WHERE ID = 1
セッション②
WHERE ID = 2
などとアクセスする。
このとき、別のデータを取得しようとしているので、ぶつからないはずである。
■原因
いくつかあるのだが、以下のような場合である。
①ID、GROUPのカラムで複合一意キーを指定していたが、どちらか片方で
アクセスしていた。
②一意キー以外の条件でアクセスすると、関係ないレコードまでロックされる
(SQLSERVERは、勝手に広範囲をロックしてしまうようだ)
■対応策
①複合一意キーの場合は、その定義順序で、定義されているカラムを必ず指定すること
② 一意キー以外の条件でアクセスする場合、WITH (NOLOCK) オプションでまず
データを取得する。その後、取得結果をカーソルでまわしながら、一意キーで
再度SELECTする。
※ WITH (NOLOCK) オプションは、ROLLBACK時の影響があることから乱用は避けたい
このような対応で、ロック範囲は意図した通りとなり、デットロックは軽減される。
■参考
ロック範囲が意図した範囲か確認する方法として、以下がある。
「Management Studio」を2つ起動させておく。
①片方で、範囲外と思われるレコードをUPDATE
②もう片方で、確認したいSELECT文を流す( 調べたいWHERE句を指定して )
以上を実行してみて、固まる(ロック待ち)となれば、ぶつかっているし、
無事にSELECTできれば、ロック範囲は重なっていない。
なっているにも関わらず、そこでデットロックが発生することはないだろうか?
■状況
たとえば、テーブルのカラムが
ID
GROUP
NAME
AGE
となっていて、
セッション①
WHERE ID = 1
セッション②
WHERE ID = 2
などとアクセスする。
このとき、別のデータを取得しようとしているので、ぶつからないはずである。
■原因
いくつかあるのだが、以下のような場合である。
①ID、GROUPのカラムで複合一意キーを指定していたが、どちらか片方で
アクセスしていた。
②一意キー以外の条件でアクセスすると、関係ないレコードまでロックされる
(SQLSERVERは、勝手に広範囲をロックしてしまうようだ)
■対応策
①複合一意キーの場合は、その定義順序で、定義されているカラムを必ず指定すること
② 一意キー以外の条件でアクセスする場合、WITH (NOLOCK) オプションでまず
データを取得する。その後、取得結果をカーソルでまわしながら、一意キーで
再度SELECTする。
※ WITH (NOLOCK) オプションは、ROLLBACK時の影響があることから乱用は避けたい
このような対応で、ロック範囲は意図した通りとなり、デットロックは軽減される。
■参考
ロック範囲が意図した範囲か確認する方法として、以下がある。
「Management Studio」を2つ起動させておく。
①片方で、範囲外と思われるレコードをUPDATE
②もう片方で、確認したいSELECT文を流す( 調べたいWHERE句を指定して )
以上を実行してみて、固まる(ロック待ち)となれば、ぶつかっているし、
無事にSELECTできれば、ロック範囲は重なっていない。
登録:
投稿 (Atom)