SQLServerのトランザクション分離レベルとスナップショット分離

SQLServerでは他のDBと同じようなトランザクション分離レベルに加えて、ロック動作が変わるオプションがあり少し特殊になっています。理解も兼ねて整理してみました。

トランザクション分離レベル
ロックの種類
スナップショット分離オプション
トランザクション分離レベルの確認方法

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

まずはトランザクション分離レベルです。５つの種類がありそれぞれの動作は以下になります。この動作自体は他のDBと同じです。
デフォルトは他のDBと同様に「READ COMMITTED」になりますが、トランザクション制御方式はロック方式となっておりOracleとは異なる点が注意点になります。（後述）

分離レベル	ダーティリード	ノンリピータブルリード	ファントムリード
READ UNCOMMITTED	発生	発生	発生
READ COMMITTED	ー	発生	発生
REPEATABLE READ	ー	ー	発生
SNAPSHOT ISOLATION	ー	ー	ー
SERIALIZABLE	ー	ー	ー

分離レベル自体については一般的なお話のためここでは割愛します。詳細は以下の記事などを参照ください。
トランザクション分離レベルについてのまとめ
 SET TRANSACTION ISOLATION LEVEL

ロックの種類

SQLServerのトランザクション制御はロック方式で、主に以下の3種類のロックがあります。

種類	説明
共有ロック（Share Lock, S-Lock）	データを読み出していることを、他のユーザに示するフラグ
更新ロック（Update Lock, U-Lock）	データの更新予定を、他のユーザに示すフラグ
排他ロック（eXclusive Lock, X-Lock）	データを更新していることを、他のユーザに示すフラグ

詳細は以下表の組み合わせがあり、かなり複雑ですがここでのポイントは以下です。
・共有ロック同士は同時に取得可能
・共有ロックと排他ロックは排他的。（例えば排他ロックを取得しているものに対して共有ロックは取得できない）

詳細は下記の公式情報を参照ください。
トランザクションのロックおよび行のバージョン管理ガイド

スナップショット分離オプション

トランザクション分離レベル「READ COMMITTED」は規定値であり、実際の運用でもよく利用される設定だと思いますが、SQLServerではデフォルトで共有ロックを取得するので注意が必要です。

この動作はランザクション分離レベルとは別の概念で「READ_COMMITTED_SNAPSHOT」オプションによって変更する事ができます。規定ではこのオプションはOFFです。（Azure SQL Databaseの既定値はON)

「READ_COMMITTED_SNAPSHOT」オプションをONにするとトランザクション制御が「ロック方式」からスナップショットを利用した「行のバージョン管理」に変わります。
「行のバージョン管理」になった場合、トランザクション開始時にデータのスナップショットがtempdbに書き込みされ、トランザクション内からはtempdbのスナップショットを参照するためロック待ちを軽減できます。

これにより同時実行性能は上がりますが、スナップショットをtempdbに書き込むため、CPU負荷は高くなり、tempdbのサイズが大きくなる点には注意が必要です。