トランザクション
ACID 特性
原子性 (Atomicity)
一貫性 (Consistency)
独立性 (Isolation)
耐久性 (Durability)
原子性
トランザクション全体が、永続的であるか、永続的でないか、の2つの状態しかとらないこと。
標準 SQL では、データは COMMIT すると永続化され、ROLLBACK するとトランザクション開始前の状態にもどる。 ここにはトレードオフがある。トランザクションがとても長く、たくさんのクエリを実行する時、そのうちの１つでも失敗したら、そこまでの全ての処理も全て巻き戻されてしまう。一方、トランザクションを細かく分割しすぎると、トランザクション間に外部からデータの操作が入り込み、予期せぬ結果が起こり得る。
SQL:2006 では、SAVEPOINT が導入された。トランザクションはこの SAVEPOINT までの限定的なロールバックが実行できるようになる。 code:セーブポイントの構文.bnf
<savepoint statement> ::= SAVEPOINT <savepoint specifier>
<savepoint specifier> ::= <savepoint name>
<release savepoint statement> ::= RELEASE SAVEPOINT <savepoint specifier>
code:ロールバック.bnf
<savepoint clause> ::= TO SAVEPOINT <savepoint specifier>
一貫性
一貫した状態 とは、全てのデータの整合性制約や参照性制約等の制約が満たされている状態 のことを指す。
そして、一貫性 とは、データベースは常に一貫した状態にあり、トランザクションが永続化された瞬間も変わらず一貫した状態にある ことを示している。しかし、トランザクション実行中 のことはいっておらず、あくまで永続化後に一貫性が保たれていれば良い。
標準 SQL では、遅延制約 の機能がある。DEFERRABLE や NOT DEFERRABLE オプションを制約に付加することでこれを有効/無効化できる。 独立性
あるトランザクションは、他のどのトランザクションからも独立している。
トランザクションがあたかも直列に実行されたかのようにする。ただし、本当に直列に実行するとコストがかかるため、いかにトランザクション同士の干渉を防ぎ、同様の効果を得られるかが課題となる。
あるトランザクションが他のトランザクションが操作しようとしているデータを操作しようとしているか？は一般に決まっていないため、これは非常に難しい問題となる。トランザクション分離レベル を考える必要がある。 耐久性
データベースは耐久性のある媒体に保存され、システムが壊れ復旧した際にも一貫した状態に復元できる。
これを満たすために、ロギングとバックアップを行なっている。この２つのメカニズムは、トランザクションの実行中にも動作している。
同時実行制御
同時実行制御 とは、複数人が同時に DB を操作する中で、いかにトランザクション同士が干渉せずにデータを操作できるようにするか、ということを考える分野のこと。
不都合な現象
1 つのトランザクションが他に影響を与えるパターンが 5 種類あり、microsoft の Jim Gray らによってドキュメント化されている。
table:不都合な現象
記号 名称 説明
P0 ダーティライト T1, T2 が同時に同一データを更新しようとするために、反映順によって一貫性が保てなくなる
P1 ダーティリード T1 がある行を更新し、T2 がそれを読んだ後に T1 がロールバックする
P2 ファジーリード (反復不能読み取り) T1 がある行を読んだ後に、T2 がそれを変更もしくは削除する
P3 ファントムリード T1 が条件に合致する N 行を読んだ後に、T2 が合致する M 行を追加する
P4 失われた更新 T1 が行を読み、T2 がそれを更新する。しかし T1 が読んだ行に基づき更新する。T2 の変更が無視される
これらのうちとくに重要なのが、P1~P3 の不都合な読み込み処理で、後に説明する トランザクション分離レベル に関わってくる。
P1: ダーティリード
これの問題点は、他のトランザクションによって INSERT された時点でその値を読めてしまっている点。図を見ればわかるが、T1 のトランザクションがまだ生きていて、ROLLBACK もしくは COMMIT するまえにその値を読んでしまっている。
よって、トランザクション分離レベルでは READ COMMITTED、すなわち、他のトランザクションがコミット済みのデータのみを参照するようにすれば回避できる。逆に、READ UNCOMMITTED、すなわち他のトランザクションがコミット前のデータを参照できるようにしてしまうと、これが発生する。
https://gyazo.com/66e09079128610a936aa283114a5f5bd
P2: ファジーリード
これの問題は、他のトランザクションの COMMIT した変更が読めてしまっている点。
よって、トランザクション分離レベルでは REPEATBLE READ、すなわち同じトランザクション開始から終了まで、同じ行は同じ読み込み結果になることが保証される状態であれば発生しない。逆に、READ COMMITTED で他のトランザクションによるデータ変更を許してしまうと、これが発生する。
https://gyazo.com/867f50b7099af60cf3b0e792c1bb21f5
P3: ファントムリード
これの問題は、他のトランザクションの COMMIT した追加レコードが読めてしまっている点。
ファジーリードと似ているが、ファジーリードは UPDATE に対し、ファントムリードは INSERT である点が異なる。
トランザクション分離レベルだと、REPEATABLE READ のみでは、「同一のレコードが同一の値を返す」までしか保証されず、「ほかのトランザクションに新しくレコードが追加されてもそれを無視する」といったことはできない。よって、これを保証するための分離レベルとしては SERIALIZABLE を選択することで、これを防ぐことができる。
https://gyazo.com/5616fd9e908943f0295370cbeb530446
分離レベル
標準 SQL では、以下のようにして分離レベルを設定できる。 diagnostics size: 1 つのステートメントが出力するエラーリストの要素数
transaction access mode: トランザクションが読み込み専用か、読み書き可能か設定する
isolation level: トランザクションが同時実行されている別のトランザクションからどの程度干渉を受けるかの度合い
code:分離レベル.bnf
<set transaction statement> ::= SET TRANSACTION <transaction mode list>
<transaction mode list> ::= <isolation labale> | <transaction access mode> | <diagnosics size>
<diagnostics size> ::= DIAGNOSTICS SIZE <number of confitions>
<transaction access mode> ::= READ ONLY | READ WRITE
<isolation level> ::= ISOLATION LEVEL <level of isolation>
<level of isolation> ::= READ UNCOMMITTED | READ COMMITTED | REPEATABALE READ | SERIALIZABLE
分離レベルとその意味は以下のようになる。
SERIALIZABLE: 複数のトランザクションが並列実行された場合でも、直列実行された時と同様の結果が保証される
REPEATABLE READ: 同じセッション中は、同じ読み取り結果が保証される
READ COMMITTED: 同じセッション中でも、他のトランザクションが変更 & コミットしたデータが参照できる
READ UNCOMMITTED: 同じセッション中でも、他のトランザクションの変更はコミットされなくても参照できる
table:分離レベルと発生し得る不都合な読み込み処理
分離レベル P1 P2 P3
SERIALIZABLE 発生しない 発生しない 発生しない
REPEATBLE READ 発生しない 発生しない 発生
READ COMMITTED 発生しない 発生 発生
READ UNCOMMITTED 発生 発生 発生
同時実行制御
主に悲観的/楽観的の二種類であり、ベンダーごとに実装が異なる。
悲観的な同時実行制御
衝突が起こる前提で、「問題は起こる前に回避する」という方針
ロック を使う
テーブルロック、ページロック、行ロック
楽観的な同時実行制御
衝突がそこまで起きない前提で、「問題は起きてから対処する」という方針
スナップショット分離 を使う
トランザクション開始時に元のデータのスナップショットをとり、それを参照する
スナップショットをとる際にはタイムスタンプをとり、コミットする際には自分より後のタイムスタンプでスナップショットをとったトランザクションがいればロールバックする
Joe Celko (2013-05-23). プログラマのためのSQL 第4版. 794p.
奥野 幹也 (2016-09-01). 詳解MySQL 5.7 止まらぬ進化に乗り遅れないためのテクニカルガイド. 376p.