はしくれSEめも

SEに必要情報をめも程度に残していこうと思っています。

【SQL Server】トランザクション分離レベルをOracleと合わせる

SQL Server

f:id:coogi-se:20210805104840p:plain


実は、SQL ServerとOracleとでトランザクション分離レベルが違うんですね。
SQL Serverのトランザクション分離レベルをOracleと同じように設定する方法をまとめました。

SQL Serverのトランザクション分離レベルについて

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と似たような処理になるかと思います。

Oracleと同じ設定方法

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

DBCC USEROPTIONS

設定を変更するSQLは以下の通りとなります。

-- 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

悲観的ロックと楽観的ロックについて

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

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

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

SQL ServerとOracleのデフォルト

SQL Serverのデフォルトは、悲観的ロックの設定となります。
Oracleのデフォルトは、楽観的ロックの設定となります。

SQL Serverにおいては更新時に排他ロックを取得しますので、参照するための共有ロックがブロックされ待たされるような形になります。
複数のトランザクションを順番に処理できますので、トランザクション時間が短く、頻繁に同時更新が発生するような処理（例えば金額計算処理）に向いているとされています。
一方、楽観的ロックについては更新頻度が少なく同時に更新されにくいようなデータに対する処理に向いています。

悲観的ロックのデメリット

  1. 更新完了までのロック解放待ちが発生しますのでパフォーマンスは悪くなります。
  2. ロックを保持する時間が長い分、デッドロックの危険も増えます。

楽観的ロックのデメリット

  1. 更新が競合してしまった場合のロールバック後処理も考慮する必要があります。

関連記事

coogi-memo.hatenablog.com