【3分で流し読み】SQL Serverのトランザクション分離レベルについて理解する

こんにちは。今回は、いまさらながらSQL Serverのトランザクション分離レベルについて、整理しておきたいと思います。いつもさらっと読んでさらっと忘れてしまうので、備忘も兼ねて記事にまとめておこうという狙いです。

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

この概念は、SQL Serverに限らず、データベース共通のものになりますが、以下のQiitaの記事に分かりやすくまとめられています。トランザクションに必要なACID特性のうち、I (Isolation, 分離性)を保証するための概念です。

トランザクション分離レベルについてのまとめ – Qiita

ANSI/ISO標準で定められた分離レベルは、以下の4種類。いずれもトランザクションの整合性とパフォーマンスのトレードオフが存在します。

分離レベルパフォーマンスダーティリードノンリピータブルリードファントムリード
SERIALIZABLE発生しない発生しない発生しない
REPEATABLE READ発生しない発生しない発生する
READ COMMITTED発生しない発生する発生する
READ UNCOMMITTED発生する発生する発生する

SQL Serverにおけるトランザクション分離レベル

SQL Serverでは、READ COMMITTEDが規定値となっています。

SQL Server / SQL Database の行のバージョン管理の基本動作 at SE の雑記 (engineer-memo.com)

また、上記の4つの分離レベルに加えて、”SNAPSHOT ISOLATION”(スナップショット分離)というオプションが利用可能。

スナップショット分離は、SQL Server 2005に追加された分離レベルらしい。ある処理があるオブジェクトに対して排他ロックを取得している際、「ロック前のスナップショットを見せる」ことで、排他ロックにブロックされない読み取りを可能にする機能のようです。

この「ロック前のスナップショットを見せる」ことを実現するのが、行のバージョン管理と呼ばれる機能になります。行のバージョン管理では、トランザクション開始時に、その時点でのデータをTemp DBに保持するようになります。行のバージョン管理の動作詳細は以下。

SQL Server でのスナップショット分離 – ADO.NET | Microsoft Docs

Temp DBに書き込むオーバーヘッドがあるため、更新性能にはやや悪い影響を与えうるが、読み込み処理には良い影響を与えるトレードオフがあるとのこと。

なお、スナップショット分離は、以下の二つの方法で利用できます。

  • 分離レベルをSNAPSHOT ISOLATIONに変更し、セッション毎に明示的にSNAPSHOT ISOLATIONレベルを指定する
  • READ COMMITTED SNAPSHOT ISOLATIONオプションを有効化し、READ COMMITTED(SQL Serverのデフォルト)を使用しているすべてのセッションで規定で行のバージョン管理が行われるようにする

前者は以下コマンドで

ALTER DATABASE [TESTDB] SET ALLOW_SNAPSHOT_ISOLATION ON

後者は以下コマンドで有効化できる。

ALTER DATABASE [TESTDB] SET READ_COMMITTED_SNAPSHOT ON

後者の場合、トランザクションでスナップショット分離を利用するには、上記コマンドでの有効化に加えて、例えば以下のようにトランザクション開始時に明示的な指定が必要。

SQL Server でのスナップショット分離 – ADO.NET | Microsoft Docs

## ADO.NETの場合
SqlTransaction sqlTran =
  connection.BeginTransaction(IsolationLevel.Snapshot);

まとめると以下のような利用オプションがあることになるか。

分離レベルパフォーマンスダーティリードノンリピータブルリードファントムリード
SERIALIZABLE発生しない発生しない発生しない
SNAPSHOT発生しない発生しない発生しない
REPEATABLE READ発生しない発生しない発生する
READ COMMITTED (SNAPSHOT ISOLATION ON)発生しない発生する発生する
READ COMMITTED (SNAPSHOT ISOLATION OFF)発生しない発生する発生する
READ UNCOMMITTED発生する発生する発生する
The Read Uncommitted Isolation Level (sqlperformance.com)

なお、SQL ServerのREAD COMMITTED SNAPSHOT ISOLATIONの動きが、OracleのREAD COMMITTEDと同等の動作になるようですね?

SQL Server で最低限設定が必要な内容 – Qiita

スナップショット分離 vs READ COMMITTEDスナップショット分離?

これは以下の記事が参考になりそう。

SQL Server / SQL Database の行のバージョン管理の基本動作 at SE の雑記 (engineer-memo.com)

SQL Serverのスナップショット分離レベル導入によるデータ基盤連携の課題解決 – ZOZO TECH BLOG

双方の記事で言及されていたのは、READ COMMITTEDスナップショット分離は、既存のトランザクションの動きが自動的に変わってしまうので、場合によっては変更の影響が大きいが、スナップショット分離であれば有効化した上で明示的にスナップショット分離の利用を宣言したトランザクションのみ影響を受けるので、影響を限定できるという点。

以上、簡単ですがSQL Serverにおけるトランザクション分離レベルの概要メモでした。

ご参考になりましたら幸いです。

おしまい

この記事を気に入っていただけたらシェアをお願いします!

コメントを残す

メールアドレスが公開されることはありません。

ABOUT US
Yuu113
初めまして。ゆうたろうと申します。 兵庫県出身、東京でシステムエンジニアをしております。現在は主にデータ分析、機械学習を活用してビジネスモデリングに取り組んでいます。 日々学んだことや経験したことを整理していきたいと思い、ブログを始めました。旅行、カメラ、IT技術、江戸文化が大好きですので、これらについても記事にしていきたいと思っています。