「マイクロソフト系技術情報 Wiki」は、「Open棟梁Project」,「OSSコンソーシアム .NET開発基盤部会」によって運営されています。
本ドキュメントでは、データベース アプリケーションを
開発する際に知っておく必要がある、DBMS毎の
を説明する。
例えば、OracleとSQL Serverのデータの
以下のような違いがある。
このような実装の違いを把握しないまま、Oracleと同じ感覚でSQL Serverアプリケーションを開発した場合、
意図しないロック待ちが発生することになり、パフォーマンスの劣化やデッドロックの発生などの問題に悩まされることになる。
1つ以上のSQL文を含む作業単位で、トランザクションが有効であるには、
ACIDと呼ばれる4つのトランザクションの基本特性を備えている必要がある。
原子性 | Atomicity |
一貫性 | Consistency |
分離(独立)性 | Isolation |
持続(永続)性 | Durability |
これを実現するための、ロック・分離戦略と、同時実行制御の仕組みは、
以下のようにOracleとSQL Serverで大きな相違がある。
次節では、
の概要について説明する。
※ なお、本ドキュメントでは「多バージョン法」と「ロック法」の優劣については言及しない。
多バージョン法(MultiVersion? Concurrency Control:MVCC)とは、
新旧の、複数のバージョンのデータを保持してデータの読み取り一貫性を保証するための仕組である。
このため、Oracleは、『マルチバージョン データベース』、『多バージョン データベース』などと呼ばれる。
SQL Server(の既定の設定)では、データの読み取り一貫性の保証の仕組みとしてロッキング・メカニズムを使用している。
一般的な同時実行制御方式は「ロック法」と言われていたが、 近年は、「多バージョン法」の同時実行制御に対応したDBMSも増えてきている。
多バージョン法と、ロック法の最も大きな違いは、更新中のデータに対して検索を行った場合である。
Oracleでは、多バージョン法により、一時領域に保持された、旧バージョンのデータを読み込む。
つまり、Oracleでは、読み取るデータが更新中であっても検索処理が待たされることはない。
これに対し、SQL Serveでは、ロック法による同時実行制御のため、検索時に共有ロックをかける。
読み取るデータが更新中の場合は、すでにそのデータに排他ロックがかかっているため、
共有ロックをかけることができない。
つまり、SQL Serverでは、更新中のデータに読み手はアクセスできず、検索処理が待たされる。
このような、実装の特徴を理解していないと、アプリケーション開発者は、
パフォーマンス劣化やデッドロックなどのトラブルに見舞われることになる。
の2つをサポートしている。
以下に、OracleとSQL Serverがサポートする
ANSI/ISO標準のトランザクション分離レベルの対応表を示す。
ANSI・ISO標準のトランザクション分離レベル | Oracle | SQL Server |
未コミット読み取り(Uncommitted Read) | 未サポート | ○ |
コミット読み取り(Read Committed) | ○(default) | ○(default) |
繰り返し可能読み取り(Repeatable Read) | 未サポート | ○ |
直列可能(Serializable) | ○ | ○ |
ANSI/ISO SQL規格は、 同時に実行されるトランザクションの分離レベルと同時実行における3つの問題点を定義している。
次に、この3つの問題点について説明する(同時実行における最も基本的な問題点である『更新データの消失』は、次節で説明する)。
同時に実行されている、
まだコミットされていないトランザクションが
書き込んだデータを読み込んでしまう。
.png [詳細] .png [詳細] file
トランザクションが過去に読み込んだデータをもう一度読み込もうとしたとき、
他のトランザクションによって書き換えられ、コミットされたデータを得てしまう。
トランザクションが、ある行の集合を返す検索条件で問い合わせを再実行したとき、
別のトランザクションがその問い合わせ条件を満たす行を追加し、読み込んでしまう。
ANSI・ISO標準のトランザクション分離レベル | ダーティ・リード | 反復不可能読み取り | ファントム・リード |
未コミット読み取り(Uncommitted Read) | 有 | 有 | 有 |
コミット読み取り(Read Committed) | - | 有 | 有 |
繰り返し可能読み取り(Repeatable Read) | - | - | 有 |
直列可能(Serializable) | - | - | - |
本節では、OracleとSQL Serverの分離レベルの実現方式ついて説明する。
ANSI/ISO標準トランザクション分離レベル | Oracle | SQL Server |
未コミット読み取り(Uncommitted Read) | 更新中のデータは、一時領域を参照することで、ダーティーデータを読まないように制御している。 | トランザクション内のSELECTステートメントで、共有ロックを使用しない。このため、ダーティーデータを参照してしまう可能性が有る。 |
コミット読み取り(Read Committed) | データの読み取りに、一時的に共有ロックを使用し、ダーティーデータを読まないように制御している。ただし、共有ロックを保持しないため、トランザクションが終了する前に、データが変更される可能性がある。 | |
繰り返し可能読み取り(Repeatable Read) | トランザクション終了まで、共有ロックを保持し、繰り返し可能読み取りを実現する。ただし、キー範囲ロックが適用されないため、トランザクションが終了する前に、参照データに行を挿入される可能性がある。 | |
直列可能(Serializable) | 自トランザクションの開始以降に開始されたトランザクションが挿入した行に関しては、参照データに含めないように制御している。 | トランザクション内のSELECTステートメントで、トランザクション終了まで共有ロック・キー範囲ロックを保持し、直列可能を実現する。 |
次に、『更新データの消失』の対処法と、分離レベルとの関係を説明する。
2つのトランザクションが行を読み取り、それぞれ、行を更新してしまったとき、
『更新データの消失』は、以下の方法で回避できる。
という制御を使用する。
本章では、ロックの互換性と、更新ロックについて詳しく説明する。
基本的なロックの互換性については以下の表に示す通りである。 Oracleに関しては、参照時にロックを掛けることが無いため、
と言うことになる。
/ | 既にかけられているモード | ||
要求されたモード | 共有(S) | 更新(U) | 排他(X) |
共有(S) | ○ | ○ | × |
更新(U) | ○ | × | × |
排他(X) | × | × | × |
※ SQL Serverには、インテントロック等、特殊なロックも存在するが、
ユーザが制御するものでは無いため、ここでは割愛する。
OracleとSQL Serverの更新ロックは、双方『更新データの消失』を防ぐ役割で使用される。
SQL Serverの場合、『更新データの消失』の防止は、共有ロックでも事足りる。
このため、SQL Serverの更新ロックは『デッドロック』防止のためと言われる。
前述の、「多バージョン法」・「ロック法」の、
トランザクション設計 ~ 実装方式の決定フローチャートを以下に示す。
※「多バージョン法」の方がディシジョン(判断)が浅く、設計しやすいと言える。
WEBのような、DBMSトランザクションを使用できない方式で、UPを実装する? ┃ ┣ YES ⇒ タイムスタンプを使用せざるを得ない(楽観方式)。 ┃ ┗ NO ⇒ DBMSトランザクションを使用することができる。 ┃ ┗ ⇒ 排他方式に、楽観方式を採用する? ┃ ┣ YES ⇒ 分離レベルを直列化(Serializable)を設定し、トランザクションのコミット時に競合エラーを検出する(楽観方式)。 ┃ ┗ NO ⇒ 更新前提の読み取りに、明示的に更新ロックを使用することで、事前に競合検出する(悲観方式)。
※ 更新ロックの競合時、待機するか、待機しないかを設定することができる(NOWAITオプション)。
WEBのような、DBMSトランザクションを使用できない方式で、UPを実装する? ┃ ┣ YES ⇒ タイムスタンプを使用せざるを得ない。 ┗ NO ⇒ DBMSトランザクションを使用することができる。 ┃ ┗ ⇒ DBMSトランザクションを使用できるがDBに負荷をかけたくない? ┃ ┣ YES ⇒ コミット済み読み取り(Read Committed)に設定し、タイムスタンプを使用し、UP側で制御する(楽観方式)。 ┗ NO ⇒ 特に問題が無い場合は、DBMSトランザクションを使用する。 ┃ ┗ ⇒ 排他方式に、楽観方式を採用する? ┃ ┣ YES ⇒ コミット済み読み取り(Read Committed)で設定し、 ┃ タイムスタンプを使用し、UP側で同時実行制御をする(楽観方式)。 ┃ ┗ NO ⇒ 悲観方式を、簡単に実装する? ┃ ┣ YES ⇒ 繰り返し可能読み取り(Repeatable Read)以上の分離レベルを設定することで、 ┃ 参照処理の結果セットに共有ロックのホールドロックを掛け、事前に競合検出する。 ┗ NO ⇒ コミット済み読み取り(Read Committed)以上の分離レベルを設定し、 更新前提の読み取りに、明示的に更新ロックを使用することで、事前に競合検出する。
SQL Server では、繰り返し可能読み取り(Repeatable Read)以上の分離レベルで、対象データに共有ロックをホールドで掛けるため『更新データの消失』を防ぐことができるだけの排他制御を実現できる。更新ロックには、このとき問題として発生するデッド・ロックを防ぐために使用するという観点があるが、システムのユーザプログラムを開発する場合には、同時実行性を高めるために、分離レベルに、コミット済み読み取り(Read Committed)の設定で一覧を読み取り(一覧画面表示)、更新対象データの読み取り(詳細画面表示)時に、明示的に更新ロックを掛けデータを読み取るパターンが多い。
SQL Server2005からは、多バージョン法を使用した、同時実行制御(MVCC)がサポートされるようになっている。
多バージョン法を使用した、同時実行制御(MVCC)を使用するには、次の2つの方法がある。
READ_COMMITTED_SNAPSHOT は、Oracleでのデフォルトの動作 とほぼ同じであり、
ステートメント発行時点での正しいデータを参照できることを保証する。
スナップショット分離レベルは、OracleのSerializableの分離レベルを選択した場合とほぼ同じ動作であり、
トランザクション発行時点での正しいデータを参照できることを保証する。
上記の多バージョン法を使用した、同時実行制御(MVCC)はデフォルトでは利用できない。
利用の際は、DBMSへ下記の設定が必要になる。
以下のようにデータベースに対して READ_COMMITTED_SNAPSHOT を ON に設定する。
ALTER DATABASE データベース名 SET READ_COMMITTED_SNAPSHOT ON
以下のようにデータベースに対して ALLOW_SNAPSHOT_ISOLATION を ON に設定する。
ALTER DATABASE データベース名 SET ALLOW_SNAPSHOT_ISOLATION ON
詳しくは、以下のドキュメントを参照のこと。
調べてみましたが、以下のトレード・オフを考慮して使い分ければイイだけのようです。
ということのようです。
だそうです。
あまり情報がありませんでしたが、以下の情報が非常に参考になりました。
Tags: :データアクセス, :SQL Server, :ADO.NET