フリーランス チャレンジ!!

フリーランス チャレンジ!!

週休4日制に俺はなる!

【解決方法】SQL Serverデフォルト設定(READ COMMITTED SNAPSHOT OFF)だと同時接続でロック待ちが発生します

f:id:ksakae1216:20170716125112j:plain

 

どうもコウタロウです。

 

今日はタイトルについて。

 

※2017年7月18日更新

 すいません。ロック待ちが我々の環境では解決できませんでした。

 我々の環境だけなのかわからないので記事はとりあえずそのままとします。

 引き続き調査し、解決すればまた記事を更新します。

 

 

 

トランザクション分離レベル

まず今回の記事の前にトランザクション分離レベルの説明が必要ですね。

 

トランザクション分離レベルはWikipediaによると下記のとおりです。

  • SERIALIZABLE ( 直列化可能 )
複数の並行に動作するトランザクションそれぞれの結果が、いかなる場合でも、それらのトランザクションを時間的重なりなく逐次実行した場合と同じ結果となる.このような性質を直列化可能性(Serializability)と呼ぶ.SERIALIZABLEは最も強い分離レベルであり、最も安全にデータを操作できるが、相対的に性能は低い。ただし同じ結果とされる逐次実行の順はトランザクション処理のレベルでは保証されない。
  • REPEATABLE READ ( 読み取り対象のデータを常に読み取る )
ひとつのトランザクションが実行中の間、読み取り対象のデータが途中で他のトランザクションによって変更される心配はない。同じトランザクション中では同じデータは何度読み取りしても毎回同じ値を読むことができる。
ただし ファントム・リード(Phantom Read) と呼ばれる現象が発生する可能性がある。ファントム・リードでは、並行して動作する他のトランザクションが追加したり削除したデータが途中で見えてしまうため、処理の結果が変わってしまう。
  • READ COMMITTED ( 確定した最新データを常に読み取る )
他のトランザクションによる更新については、常にコミット済みのデータのみを読み取る。 MVCC はREAD COMMITTEDを実現する実装の一つである。
ファントム・リード に加え、非再現リード(Non-Repeatable Read)と呼ばれる、同じトランザクション中でも同じデータを読み込むたびに値が変わってしまう現象が発生する可能性がある。
  • READ UNCOMMITTED ( 確定していないデータまで読み取る )
他の処理によって行われている、書きかけのデータまで読み取る。
PHANTOM 、 NON-REPEATABLE READ 、さらに ダーティ・リード(Dirty Read) と呼ばれる現象(不完全なデータや、計算途中のデータを読み取ってしまう動作)が発生する。トランザクションの並行動作によってデータを破壊する可能性は高いが、その分性能は高い。

 

SQL Serverのデフォルト設定

SQL Serverのデフォルト設定ではトランザクション分離レベルは「READ COMMITTED」となってます。

 

これだけだと一見問題ないのですが、先のトランザクションの処理が長い場合、後のトランザクションがテーブルを参照できずにロック待ちとなってしまいます。

 

ロック待ちで困ること

実際、現在のプロジェクトで発生した事象です。

 

1時間毎に多くのテーブルを参照し、更新するJOBがあります。

このJOB実行時にトランザクションを開始し、終了まで20分程度かかる。

 

この20分の間に、JOBが参照しているテーブルをWebアプリケーションが参照しようとすると参照できずに待ってしまうんです。

 

困りますね。

 

解決方法

2つあります。

コマンドレベルと、SQL Management Studio使う方法です。

 

①コマンドレベル

下記の記事を参照して下さい。

SQLServerで読み取り一貫性 - Qiita

 

SQL Management Studio

下記ページのハードコピーにある通り、データベースを選択肢、右クリックでプロパティからオプションを表示して下さい。

そのオプションから「Is Read Committed Snapshot On」と「スナップショット分離を許可」をTrueに変更します。

※下記ページのハードコピーはtempdbになってますが、自分が使っているDB名に変えて下さい。

SQL Serverトラブルシューティング(22):TEMPDBを配置したディスク領域が枯渇した(Read Committed Snapshot分離レベル編)(ファイル管理トラブル) - @IT

 

※現在は、まだ事象が発生してないのでこれで解決だと思ってますが、もしロック待ちが再現すればまたここでお知らせします。