ようこそ。睡眠不足なプログラマのチラ裏です。

トランザクション同時実行時の問題とトランザクション分離レベル

データベースの同時実行性の定義

データベースにおける同時実行性は、同時に共有データにアクセスしたり、
共有データを変更したりする複数プロセスの機能性として定義することができる。
互いにブロックすることなく同時に実行できるユーザプロセス数が多いほど、
データベースシステムの同時実行性は高いといい、データの変更プロセスによって、
他のプロセスがその変更データを読み取りできなかったり、
データの読み取りプロセスによって、他のプロセスがそのデータを更新できない場合、
同時実行性が低いという。また、複数プロセスが同じデータを同時に変更しようとすると
常にデータの整合性が損なわれるような場合も、同時実行性が低いと言える。


同時実行性が低くなる状況に対処する方法
データベース システムで同時実行性が低くなる状況に対処する方法は、
使用している同時実行制御がオプティミスティック(楽観的)*1かペシミスティック(悲観的)*2かによって決まる。
ペシミスティック同時実行制御は、読み取り操作に影響が及ぶ可能性のあるデータに対する
変更操作の頻度が高いという想定で機能する。
言い換えるなら、システムがペシミスティックであれば、操作の競合を想定することになる。
ペシミスティック同時実行制御を使用する場合の既定動作では、ロックを使用して
別のプロセスが使用中のデータに対するアクセスをブロックすることとなるため、同時実効性が低くなる。
オプティミスティック同時実行制御は、別のプロセスが読み取り中のデータを
任意のプロセスが変更する可能性が (あるとしても) 低いという想定で機能するため、同時実効性が高くなる。
オプティミスティック同時実行制御を使用する場合の既定動作では、
行のバージョン管理を使用して、変更を実行する前にデータの状態を確認できる。*3


トランザクション同時実行の問題


マルチユーザ環境で、複数の異なるトランザクション
同時にデータベースにアクセスするような場合、次のような問題が発生する可能性がある。

更新データ損失(Lost Updates)


複数のトランザクションが同じ行を選択し、その行の元の値を更新すると発生する。
トランザクションは互いに相手を意識することがないので、
ある更新処理による更新データが確定される前に、他の更新処理により変更されてしまうと、
前者のトランザクションで更新したデータが失われてしまうことになる。

ダーティ リード(Dirty Read) (非コミット依存)


別のトランザクションにより更新されたが、まだコミットされていないデータを読んでしまえる。
後者のトランザクションが読み取るデータは、まだコミットされていないので、
行を更新中のトランザクションによって変更される可能性がある。
これはトランザクション処理における機能の条件である「ACID特性」のうち
「C」の文字で表されている「一貫性」(Consistency)と矛盾する。
ダーティーリードを禁止することは比較的容易に実現できるが、
多くの場合、禁止を実行するとオーバーヘッドを生じさせてしまうことになり、
アプリケーションの実行速度を低下させてしまう。同様に、システムの平行性を低下させてしまうことも多い。

反復不能読み取り(不一致分析)


トランザクション中に、繰り返し同じデータを参照するとき、そのつど値が変わってしまう状態。
同一トランザクション内で、同じSELECT文が実行された場合に、同じ照会結果が保証されないことを言う。
反復不能読み取りは、トランザクションがデータを変更中に別のトランザクションがそのデータを読み取るという点で
ダーティリードと似ている。ただし反復不能読み取りの場合、別のトランザクションが読み取るデータは
変更を行ったトランザクションによってコミットされている。また、同じ行が複数回読み取られ、
そのたびにトランザクションによって情報が変更される。そのため、反復不能読み取りと呼ばれる。

ファントム


トランザクション中に、突然現れたり消えたりしてしまうデータができてしまう。
その様子から、この現象はファントムと呼ばれる。
同一トランザクション内で、同じSELECT文を実行した場合に、
別のトランザクションで削除が行われた場合、2回目以降の読み取りにおいて、
最初の読み取りで得られた行のデータが得られなくなったり、
別のトランザクションで挿入が行われた場合、2回目以降の読み取りにおいて、
最初の読み取りで得られなかった行のデータが読み取られてしまう。


トランザクションの分離レベル (Isolation Level)

分離レベルを指定すると、別の分離レベルを設定しない限り、セッションの終了時まで変更されない。
他のセッションの分離レベルは、SELECT ステートメントでテーブルレベルのロック ヒントを指定することによって無効化することができるが、
テーブルレベルのロック ヒントを指定しても、セッション内のほかのステートメントには影響を与えない。


■各分離レベルの同時実行時の副作用

分離レベル 分離レベル ダーティリード 反復不可能読み取り ファントム
READ UNCOMMITTED
READ COMMITTED 不可
REPEATABLE READ 不可 不可
SERIALIZABLE 不可 不可 不可


トランザクションの分離レベルの指定方法の例

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED


ANSI 標準と SQL Server および Oracle の分離レベル

ANSI 標準 SQL Server 2005 Oracle10g
READ UNCOMMITTED READ UNCOMMITTED 未サポート
READ COMMITTED READ COMMITTED(READ_COMMITTED_SNAPSHOT データベースオプションの ON,OFF によって 2 種類の動作が可能) READ COMMITTED
REPEATABLE READ REPEATBLE READ 未サポート
- SNAPSHOT READ ONLY
SERIALIZABLE SERIALIZABLE SERIALIZABLE


Oracleでは、ANSI 標準の READ UNCOMMITTED および REPEATABLE READ はサポートされていない。
また、SQL Server 2005 ではデータベース単位で、
READ_COMMITTED_SNAPSHOTデータベース オプションを設定することにより、
従来からの共有ロックを使用したデータ読み取り時の方法と、
Oracleの読み取り一貫性と同等の行バージョン管理を利用する方法のどちらかを選択することができる。

共有ロックを使用する READ COMMITTED分離レベル


READ_COMMITTED_SNAPSHOT データベース オプションを OFF に設定している場合、
READ COMMITTED 分離レベルは、SQL Server 2005 の既定の分離レベルであり、
SQL Server 2000 の READ COMMITTED 分離レベルと同等となる。
つまり、データ参照時に、共有ロックを使用し、参照が完了した段階で共有ロックを解放します。
参照すべきデータが、他のトランザクションで変更中の場合、ロック待機状態 (ロックによるブロック) が発生する。


データベースでスナップショット分離レベルを許可しない場合、下記を実行する。

ALTER DATABASE [DB名] SET ALLOW_SNAPSHOT_ISOLATION OFF

行バージョン管理を使用する READ COMMITTED分離レベル


READ_COMMITTED_SNAPSHOT データベース オプションが ON の場合、
READ COMMITTED 分離レベルは、Oracle の既定の分離レベルである READ COMMITTED 分離レベルと同等となる。
データ変更時には、変更前のデータ (最新のコミット済みデータ) を tempdb にコピーし、その後、データの変更を行う。
SELECT ステートメント開始時点でのタイムスタンプが確認され、その後にコミットされたデータは
そのステートメント内では、たとえコミット済みでも参照されません (文レベルの読み取り一貫性)。
同一のトランザクション内で同じ SELECT ステートメントを実行した場合、2 回目に実行した時点でのタイムスタンプが記録され、
その時点での最新のコミット済みデータを常に返します。したがって、1 回目と 2 回目の参照の間で
別のトランザクションによって変更、コミットが行われていた場合、それぞれの参照では異なる結果を返すこととなる。
また、他のトランザクションで変更中のデータを参照する際には、
変更途中の排他ロックされたデータを読み取る代わりに、tempdb 上の変更前のデータが参照される。
この動作により、ロック待機状態が発生せず、同時実行性を向上させることができます。
Oracle では変更前のバージョンを UNDO セグメントに格納するが、
SQL Server 2005 では、前述のとおり tempdb に格納される。*4


データベースでスナップショット分離レベルを許可するには、下記を実行する。

ALTER DATABASE [DB名] SET ALLOW_SNAPSHOT_ISOLATION ON

SNAPSHOT分離レベル


SQL Server2005で提供されているスナップショット分離レベルは、
Oracleトランザクション レベルの読み取り一貫性の機能と同等である。
データ変更時は tempdb に変更前のデータをコピーした後で、データの変更を行います。
スナップショット分離レベルに切り替えた時点でのタイムスタンプを記録し、
トランザクション終了まで、そのタイムスタンプ時点のデータを常に参照することができます。
その後、他のトランザクションで変更されたデータは、コミット済のデータも含め、そのトランザクションが終了するまで
tempdb に保持されます。行バージョン管理を使用した READ COMMITTED との違いは、
他のトランザクションで変更、コミットが完了した時点でも、同一トランザクション内で同じ SELECT 文を実行した場合、
常に同じtempdbに保持されている値を返す点。繰り返し時点のデータを参照し、
複数のレポートを作成するような場合などに便利と言える。

*1:オプティミスティック同時実行制御とは、「自分が操作している情報は他の人が操作する可能性が少ないはずだ。」という視点に立った同時実行制御

*2:ペシミスティック同時実行制御は、「自分が操作している情報は,他の人も操作する可能性があるはずだ。」という視点に立った同時実行制御。

*3:行のバージョン管理を使用する Read Committed 分離は、READ_COMMITTED_SNAPSHOT データベース オプションを ON に設定することで有効となる。

*4:SQL Server2005のtempdb 上のデータは、1 分ごとにチェックされ参照される可能性がなくなった時点で、ガベージ コレクションによって自動的に削除される仕組みとなっている。