エキサイト株式会社 メディア事業部エンジニアの中尾です。
最近はSQL Serverを使うことが多いので、SQL Serverでよく使われているwith(NOLOCK)の挙動について説明できたらと思います。
with(NOLOCK)とは
簡単にいうと、トランザクションレベルを強制的にREAD UNCOMMITTEDな状態でselect文が使えます。
。
。。
。。。
難しいですね。
図で説明しましょう。
デフォルトのトランザクションレベル
SQL Server以外のMySQL,PostgresもデフォルトはREAD COMMITTEDと呼ばれる、トランザクションAの中でCUDを発行しても、別のトランザクションからはトランザクションAの変更内容が見えない状態です。
以下の図のように、ID=3のレコードの内容を取得しようとすると、トランザクション開始前の松本一郎が取得できます。
しかし、SQL ServerのREAD COMMITTEDはちょっと違います。
READ COMMITTEDにスナップショットという概念が
SQL ServerのREAD COMMITTEDは行のバージョン管理がされていません。 つまり、トランザクションA内でCUDを発行すると、ほかのトランザクションから変更内容は見えないのですが、変更内容をコミット(or ロールバック)するまで待機してしまいます(行ロック)
以下の図のように、ID=3のレコードの内容を取得しようとすると、トランザクション開始しているレコードを取得する場合、コミット(or ロールバック)するまで、名前が取得できません。
これを解消するためにはスナップショットを有効にしないといけません。
スナップショットとは、行ごとのバージョン管理をするという意味です。
以下の図のように、ID=3のレコードの更新前のデータをテンポラリに退避しており、ほかのトランザクションから問い合わせをされても、その退避内容を返します。
行ごとのバージョン管理をするということは、もちろんtempdbのサイズが大きくなるので、
これからスナップショットを有効にする場合は、パフォーマンスに気をつけてください。
以下のalter文のどちらかを発行することで有効になります。
ALTER DATABASE MyDatabase SET ALLOW_SNAPSHOT_ISOLATION ON
→オンライン中に実行可能で、select文を使う前に、SET TRANSACTION ISOLATION LEVEL SNAPSHOT を実行することで、スナップショットが使えるようになります。
ALTER DATABASE MyDatabase SET READ_COMMITTED_SNAPSHOT ON
→オンライン中に実行不可能ですが、一度実行すると、全てのクエリでスナップショットが有効になります。
もしかしたら、SQL Server以外のRDBはデフォルトでスナップショットという概念で行ごとのバージョン管理をしているかもしれません。
詳細は以下のURLを参考にしてください。
