【SQL server】トランザクション分離レベルについて

SQL serverで設定できるトランザクション分離レベルについて整理しました。
※デフォルトはREAD COMMITTEDになります。

トランザクション分離レベル	ロックの種類	ダーティリード	ノンリピータブルリード	ファントムリード
READ UNCOMMITTED	悲観的ロック	○	○	○
READ COMMITTED	悲観的ロック	×	○	○
REPEATABLE READ	悲観的ロック	×	×	×
SERIALIZABLE	悲観的ロック	×	×	×
SNAPSHOT	楽観的ロック	×	×	×
READ COMMITTED SNAPSHOT	楽観的ロック	×	○	○

SQL serverのREAD COMMITTED SNAPSHOTについては、更新時の断面をTempdbにSNAPSHOTをとってから処理する動きになりますのでOracleのREAD COMMITTEDに似たような処理になるかと思います。

尚、悲観的ロックと楽観的ロックについては一般的には以下のような理解でよいかと思います。

悲観的ロック：
更新処理時に更新対象のデータを参照してから更新が完了するまでの間、他のトランザクションからの参照をブロックします。

楽観的ロック：
更新対象のデータを参照した時点ではロックをかけず、更新直前に他のトランザクションによって更新されていないことを確認してから対象をロックします。すでに更新されてしまっていた場合は、エラーとなります。

悲観的ロックについては上記の通りデフォルトの設定となります。SQL serverにおいては更新時に排他ロックを取得しますので、参照するための共有ロックがブロックされ待たされるような形になります。複数のトランザクションを順番に処理できますので、トランザクション時間が短く、頻繁に同時更新が発生するような処理（例えば金額計算処理）に向いているとされています。ただし、更新完了までのロック解放待ちが発生しますのでパフォーマンスは悪くなりますし、ロックを保持する時間が長い分デッドロックの危険も増えます。

一方、楽観的ロックについては更新頻度が少なく同時に更新されにくいようなデータに対する処理に向いています。ただし更新が競合してしまった場合のロールバック後処理も考慮する必要があります。

現状の設定を確認・変更するSQLは以下の通りとなります。

-- 設定確認
DBCC USEROPTIONS

-- Sessionレベルの設定の変更
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

-- DatabaseレベルのREAD COMMITTED SNAPSHOT 設定の有効化

ALTER DATABASE Database_name SET READ_COMMITTED_SNAPSHOT ON