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

トランザクションの分離レベルを知るための3つの概念

トランザクションの分離性(isolatration)とは、あるトランザクションの処理は、別のトランザクションに影響を与えないことです。どの程度影響を及ぼすかの基準として、3つの基準を知る必要があります。単純に分離レベルが高いからいいというわけではありません。使っているシステムがどの分離レベルかを把握してトランザクションを利用することが重要です。

ダーティリード (Dirty Read)

トランザクションBが更新したがまだコミットしていない情報を、トランザクションAが読み込んでしまうこと。コミットしていない不確かな情報を読込んでしまう、一番分離レベルが低い状態。

ノンリピータブルリード(Non-Repeatable Read)

同一トランザクションの中で別の結果を読取ること。データの再現性がないこと。

ファントムリード (Phantom Read)

同一トランザクション内で、レコードが消えたり増えたりすること。ファントム (=幻)リード。
ノンリピータブルリードはデータの内容の話。ファントムリードはレコードの話。

トランザクション分離レベルのまとめ

別トランザクションのコミット前の情報が見える状態は、最も分離レベルが低いといえます。別トランザクションのコミット後の情報がどこまで見えるかにより更にレベルが別れます。別トランザクションの更新や追加・削除がまったく参照できなければ分離レベルが高いといえます。

また、ダーティーリード、ノンリピータブルリード、ファントムリードという3つの基準堺に、「READ UNCOMMITTED」「READ COMMITTED」「REPEATABLE READ」「シリアル化可能」の4つの分離レベリに別れます。

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

ここからが本題のSQLserverのトランザクションレベルです。下記コマンドで、現状の設定が確認できます。

DBCC USEROPTIONS

デフォルト値は「read committed」です。

SQLserverのデフォルト値の設定

SQLServerの分離レベルのデフォルト値が「read committd」です。

別のトランザクションで行った更新は、コミット前なら参照できない、コミットすれば参照できるようになると思うはずです。

しかし実際にはそうはなりません。別トランザクションで行ったコミット前の更新はたしかに参照できません。しかし、更新を行ったトランザクションのコミット前は、更新前の情報も参照できなくなります。つまり待ちが発生するのです。

追記)下記例ではwhere id=1では待ちが発生しますが、where id=2ならコミット前にもレスポンすが返ってきます。この場合はupdateで確認していますが、insertでも同様です。selectする時に影響のあったレコードが含まれているかどうかで待ちが発生するかどうかが変わります。

トランザクションの分離レベルは高ければいいというものではありません。「read committd」を採用しているシステムは多くあります。しかし、どのような動作をしているかを把握しておくことはとても重要です。

私が思う対策は2つあります。

対策1)ダーティーリード(with nolock)

必要に応じて、ダーティーリードを使用することです。select ~ from table with(nolock) のように、with(nolock)を利用することでダーティーリードができるようになります。あくまでコミット前の情報なので注意しましょう。

対策2)READ COMMITTED SNAPSHOT 設定の有効化

もう一つは、データベースの設定を変更することです。下記コマンドでREAD COMMITTED SNAPSHOT 設定の有効化をすることで、待ちがなくなります。

ALTER DATABASE [データベース名] SET READ_COMMITTED_SNAPSHOT ON

タイトルとURLをコピーしました