SQL Server の「読み取り一貫性とロック」について触れてみたいと思います。

経緯

もともとは Oracle に触れる機会が多かったのですが、費用面からか SQL Server を望むお客様も多くなってきました。
SQL Server に触れる機会も多くなりましたので違いについて記載します。

概要

Oracle では、別トランザクションの更新中データに対して、変更前のデータを読み取ることができます。
「ダーティーリード」ではなく、「マルチバージョン コンカレンシー コントロール」(MVCC)と呼ばれる同時実行性向上の仕組みによるものです。

SQL Server でも MVCCを有効にすることができますが、デフォルトでは OFF になっています。SQL Server で MVCC を有効にする方法と、注意点を記載します。
(SQL Server 2005 からサポートされている機能です。)

この記事の内容は、SQL Server 2014 で確認した結果について記載しています。

MVCC を有効にする方法と注意点

MVCC を有効にする方法

SQL Server で MVCCを有効にする方法は簡単です。
MVCC を ON にしたいデータベースに対して以下のコマンドを発行します。

ALTER DATABASE [Database Name]
SET READ_COMMITTED_SNAPSHOT ON;

トランザクション分離レベルとACID特性

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

少しトランザクション分離レベルについて記載します。

この表は、ANSI/ISO SQL標準で定められている「トランザクション分離レベル」と「データに矛盾が生じる3つの現象」をあらわしたものです。

SQL Server(他の多くのDBMSも)はデフォルトの分離レベルが「READ COMMITED」です。
SQL Server と Oracle は同じ分離レベル「READ COMMITED」ですが、ロックの違いにより挙動が異なってきます。

SQL Server は READ_COMMITTED_SNAPSHOT が OFF（SQL Server の規定値）の場合、共有ロックを取得します。
排他ロックされたデータに対して共有ロックは取得できない（逆もできない）ため、排他ロックが解除されまるでデータを読めません。（待たされます。）

両方のDBMSで開発をしたことがある人から「SQL Server はデッドロックが発生しやすい」と聞くことがありますが、SQL Server はデフォルトで共有ロックを取得するためです。
共有ロック自体がデッドロックの原因全てではありませんが、デッドロックが発生しやすくなります。

SQL Server でも READ_COMMITTED_SNAPSHOT が ON になると共有ロックではなくMVCCになります。（Oracle と似た挙動になります。）
なお、SQL Server の分離レベルには上記の表とは別に SNAPSHOT があります。

分離レベル SNAPSHOT は SERIALIZABLE と同じで「データに矛盾が生じる3つの現象」が発生しません。
SNAPSHOT は同時実行性がありますが、SERIALIZABLE は同時実行性がありません。
SNAPSHOT は複数のトランザクションが同時に実行された結果、同じデータを更新した場合、後のトランザクションはエラーが生じロールバックします。

ACID特性について

「データに矛盾が生じる3つの現象」には先の表で記載した通りダーティーリード、ノンリピータブルリード、ファントムリードがあります。 （複数のトランザクションを同時に実行すると隔離性が保たれなくなる現象）。

ACID特性の隔離性（Isolation）によって発生しないように分離レベルが定義されています。

ACID特性はトランザクション処理に求められる要素で以下の4つがあります。
・原始性(Atomicity)
・一貫性(Consistency)
・隔離性(Isolation)
・耐久性(Dirability)

MVCC を有効にした場合に注意をすること。

MVCC(READ_COMMITTED_SNAPSHOTをON)にすると同時実行性が高まるため、OFF の時には起きなかった問題が生じる場合があります。
下の図をもとに説明をします。
この図は、「TABLE1」テーブルの「A」フィールドに「100加算する処理」を同時に実行した場合について記述しています。

READ_COMMITTED_SNAPSHOT が OFF の場合では問題が起きなかった処理です。
MVCC で動作するこのケースでは、読み取るデータを明示的に排他ロックする必要があります。

実は、上記の①、②、③（④、⑤、⑥）の処理は、以下の1つの Update 文で記述できます。 この場合、特に問題は生じません。今回は、問題を説明するために問題が生じる記述の仕方にしています。

update TABLE1
set A = A + 100
where KEY1 = ‘001’

まとめ

MVCC(READ_COMMITTED_SNAPSHOTをON)にした場合のメリットとデメリット

メリット

・READ_COMMITTED_SNAPSHOT をONにすることにより、データが共有ロックされなくなり、同時実行性が向上します。
・デッドロックが発生しにくくなります。

デメリット

・ロックの方法が変わることによりアプリケーション側でロックの設計が必要になる場合があります。
・データをバージョン管理するため TEMPDB にその情報が格納されます。処理のオーバヘッドも大きくなります。

SQL Server では READ_COMMITTED_SNAPSHOT がデフォルトで OFF です。
オーバーヘッドが大きくなることなどがその原因かもしれません。
※ SQL Azure では、READ_COMMITTED_SNAPSHOT がデフォルトで ON です。

MVCC(READ_COMMITTED_SNAPSHOTがON）の場合、アプリケーション設計、負荷テストなど十分に実施してください。

注意事項

「行のバージョン管理を伴う READ COMMITTED 分離」と「スナップショット分離」の２つがあります。 この違いについて解りやすく記載します。

行のバージョン管理を伴う READ COMMITTED 分離

データベースオプション

READ_COMMITTED_SNAPSHOT を ON にすることで、一貫性を保った状態でステートメントレベルの読み取り操作を実行できます。
他のトランザクションがコミットしたレコードは、更新後の値で見えます。このレコードは更新できます。（ノンリピータブルリードが発生します。）

分離レベル

分離レベルは、READ COMMITED です。

スナップショット分離

データベースオプション

ALLOW_SNAPSHOT_ISOLATION を ON にすることで、一貫性を保った状態でトランザクションレベルの読み取り操作を実行できるように「許可」します。
スナップショット分離は、以下のステートメント実行して初めて有効になります。

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

ALLOW_SNAPSHOT_ISOLATION を ON にしても上記ステートメント発行しないと、スナップショット分離になりません。
ALLOW_SNAPSHOT_ISOLATION が OFF でも、分離レベルに SNAPSHOT を指定できトランザクションも開始できますが、DML の実行でエラー3952が発生して処理が失敗します。

スナップショット分離では、他のトランザクションがコミットしたレコードは、自トランザクション開始時の値で見えます。このレコードを更新するとエラーが発生しトランザクションはロールバックされます。（ノンリピータブルリードは発生しません。）

分離レベル

SQL Server 独自の分離レベル SNAPSHOT です。

Microsoft の推奨

以下は、マイクロソフトのサイトにある記述の引用です。

次の理由により、ほとんどのアプリケーションでは、スナップショット分離よりも、行のバージョン管理を使用する READ COMMITTED 分離が推奨されます。

・READ COMMITTED 分離では、スナップショット分離ほど tempdb の領域が使用されません。
・READ COMMITTED 分離は分散トランザクションで使用できます。一方、スナップショット分離は分散トランザクションでは使用できません。
・READ COMMITTED 分離はほとんどの既存のアプリケーションでアプリケーションを変更することなく使用できます。 既定の分離レベルである READ COMMITTED を使用して記述されたアプリケーションは、動的にチューニングすることができます。 READ COMMITTED の動作は、行のバージョン管理を使用するかどうかに関係なくデータベース オプションの設定によって決まります。また、アプリケーションに影響を与えることなく動作を変更できます。
・スナップショット分離は更新の競合が発生しやすいのですが、行のバージョン管理を使用する READ COMMITTED 分離は違います。スナップショット分離レベルで実行されているトランザクションで別のトランザクションによって後で変更されるデータを読み取る場合、そのスナップショット トランザクションで同じデータを変更しようとすると更新の競合が発生し、そのトランザクションは終了してロールバックされます。この問題は、行のバージョン管理を使用する READ COMMITTED 分離には関係ありません。

よくある記載の間違い

READ_COMMITTED_SNAPSHOT と ALLOW_SNAPSHOT_ISOLATION を両方 ON にすると記載したサイトも見受けられます。

ALLOW_SNAPSHOT_ISOLATION データベースオプションを ON にしても、分離レベルに SNAPSHOT を指定しないと意味がありません。

以下の何れかの運用を選択することになります。

・データベースオプション READ_COMMITTED_SNAPSHOT を ON にする。(SQL Server の分離レベルは初期値が READ COMMITED なので READ_COMMITTED_SNAPSHOT の値がすぐ作用する。)
　⇒ 「行のバージョン管理を伴う READ COMMITTED 分離」
・データベースオプション ALLOW_SNAPSHOT_ISOLATION を ON にし、分離レベルに SNAPSHOT を指定する。
　⇒ 「スナップショット分離」