DataBase SQLServer

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」です。

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

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

トランザクションの分離レベルは高ければいいというものではありません。「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

Gooleアドセンス用336

Gooleアドセンス用336

-DataBase, SQLServer
-

執筆者:

関連記事

sqlserver

テーブル変更やインデックス追加時にSELECTできるのか

INDEX1 テーブルの変更は運用に影響するのか?2 テーブルの変更3 インデックスの追加 テーブルの変更は運用に影響するのか? 運用中のテーブルを変更しないといけない場合は多々あります。 運用中のテ …

sqlserver

SQLSERVER インストール後にDataをDドライブ(別ドライブへ)移動する方法

通常OSはCドライブ、データはDドライブなどの別ドライブを使用します。 SQLSERVERではデータの肥大化が起こりうる実データファイル(mdfやldf)をCドライブ以外に変更したい場面は多いはずです …

MySQLの実行速度テスト用PHP

1.testデータベースにtest_tableテーブルを作成。 2.1万件のデータを登録します。 3.1000件のフラグを更新 4.更新したデータを検索する時刻を測定して表示 するサンプルです。 &l …

mysql

MySQLのエラー、クエリ、スロークエリのログ

全て /etc/my.cnf の [mysqld] に追記してサーバーを再起動すれば出力されるようになります。 エラーログは開発時には警告も出力しておけばよいでしょう。 クエリログは全てのSELECT …

mysql

MySQL load dataでcsvをインサートする

かなり高速でinsertしてくれます。 INDEX1 使い方2 ERROR 10453 [補足]insertを高速にする 使い方 MySQLにログインして、データベースを指定して実行します。 mysq …