セッション、コネクション、トランザクション
- コネクション アプリケーションとSQL実行のための通信路
- セッション コネクションを確立してから切断するまでの一連の処理
- トランザクション 検索や更新等を含む一連の処理の単位
ACID特性
トランザクションが満たすべき特性
- 原子性(Atomicity)
- 一貫性(Consistency)
- 独立性(Isolation)
- 耐久性(Durability)
原子性(Atomicity)
トランザクション全体が永続的であるか、もしくは全体が永続的ではないかの２つの状態しか取らないこと。(COMMIT, ROLLBACKのどちらか)
完全な原子性を担保しようとすると、パフォーマンスが犠牲になる
ex. １億行のデータを更新する場合、１億行目でエラーが発生したら
SAVEPOINT
↑を解消するために → SAVEPOINT
トランザクション中にエラーが発生した場合、全体をロールバックするのではなく、SAVEPOINTまでロールバックする。
SAVEPOINTで途中結果をコミットすべきか全体をロールバックすべきか、エラー行を除いて再度処理を実行するか判断する。
一貫性(Consistency)
トランザクションの実行前後で、データの整合性が保たれ、矛盾していないこと。
データ整合性制約、参照整合性制約（外部キー制約）などの全ての状態が満たされた状態であること。
独立性(Isolation)
ある１つのトランザクションは、他のいかなるトランザクションからも独立していること。
つまり、１つのトランザクションは他のトランザクションに影響を与えてはいけないということ。
完全に実現するには、全ての処理が参照に限定されているか、全ての処理が直列で実行されている(後述のSERIALIABLE)かのどちらかであれば担保されるが、現実のアプリケーションでは難しい。
そのため、アプリケーションはどのようにして独立性を担保するか考える必要がある。
UPDATEやINSERTしたデータが他のトランザクションから参照すべきかどうかは検討する必要がある。
耐久性(Durability)
プログラムに異常が発生してもデータそのものがきちんと保全されていること。
さらに、復元可能であるということ。
この性質を満たすために、データベースはロギングとバックアップの機能を持っている。
同時実行制御
5つの現象
１つのトランザクションが他のトランザクションに影響を与えるパターンには５種類ある。
ダーティライト
別のトランザクションでコミット前のデータが更新できてしまう。
トランザクションT1, T2があるとする。
T1がデータを更新する。コミットもしくはロールバック前にT2がデータを更新する。
その後、どちらかのトランザクションがロールバックした場合、どの値に戻していいかがわからない。
一貫性が損なわれている状態。
ダーティリード
別のトランザクションでコミット前のデータが見えてしまう。
T1がデータを更新する。T1のコミットもしくはロールバック前にT2が更新されたデータを読み出す。次に、T1がロールバックを実行すると、T2が読み出した値はどこにもいないことになる。
ファジーリード（ノンリピータブルリード）
T1が行R1を読む。次に、T1がコミットもしくはロールバック前にT2がその行を変更もしくは削除してしまう。T1が再度同じ行を読み出そうとしても、T2による変更後の値もしくは削除されているので読み取れない。一見何が問題なのかわかりにくい。単一の行で考えると問題ないケースが多いが、複数行のケースを考えるとわかるかも。
ファントムリード
T1が複数行を参照する。次に、T1がコミットもしくはロールバック前にT2が新しい行を追加する。その後、T1がもう一度同一条件で参照すると、前回参照時と結果が違う。
ロストアップデート
T1がデータを読む。次にT2がデータを更新する。最後に、T1がデータを更新する。
T2の更新が消えてしまう。
分離レベル
５つの現象を避けるため、セッション毎に分離レベルを設定することができる。
| トランザクション分離レベル | ダーティリード | ファジーリード | ファントムリード | ロストアップデート |
|---|---|---|---|---|
| READ UNCOMITTED | ◯ | ◯ | ◯ | ◯ |
| READ COMMITED | ◯ | ◯ | ◯ | |
| REPEATABLE READ | ◯ | ◯ | ||
| SERIALIZABLE |
なお、RDBMSによってデフォルトのトランザクション分離レベルが違う。
また、対応している分離レベルの種類も違う。
InnoDBはREAD COMMITEDがデフォ。なお、InnoDBのREAD COMMITEDはファントムリードは起きないらしい。
悲観的ロックと楽観的ロック
悲観的ロック
更新対象のリソースに排他ロック（他のトランザクションからの参照、更新どちらもブロックする）をかける。
デッドロックが発生する。
※ トランザクションの分離レベルが READ UNCOMITTED のときは参照はロックしない。
select for update ~
楽観的ロック
参照した時点の状態を記録し、コミット時に同じ状態であればコミットする。
実装例として、更新条件にバージョン番号もしくはタイムスタンプを付加し、他のトランザクションから更新されているかどうかを検知できるようにする。(VERSION PATTERN)
デッドロックが発生しない。
update foo set name = 'bar' where id = 1 and updated_datetime = ~
どっちを使うか
更新の衝突が多い場合は悲観的ロック、衝突が頻発しない場合は楽観的ロック。
ロールバックする際のコストと、参照待ちのコストを比較して検討すること。
（楽観的ロックはロールバックのコストが小さいときに利用する）
どの分離レベルを選ぶべきか
READ UNCOMITTED
悲観的ロック時は他の操作から参照はできるが（ダーティリード）、書き込みは順序が保たれる（ダーティライトはされない）。
楽観的ロックは全ての操作が認められる。ロールバック時は同一のデータに対して更新した他のトランザクションも全てロールバックされる。
この分離レベルは、トランザクションが不要な状況やデータベースへの排他的なアクセスによってのみ更新される場合にしか適していない。
例： オフラインのデータベースや、トランザクションに含まれない監査ログのテーブル等。
READ COMMITTED
悲観的ロック時、他のトランザクションは参照も待ち状態になる。
楽観的ロック時、全ての変更は自身のトランザクション内でのみ参照可能となる。また、書き込み時には検証はない。（ただしDBの実装によって挙動が異なることがあるため、要確認。）
この分離レベルは、古い値が返ることが許容され、かつ書き込み操作しかない場合に適している。
例：最新の投稿が反映されていなくても構わず、競合しないオンラインフォーラム等。（SNSとか掲示板とか？）
REPEATABLE READ
悲観的ロック時、全ての行のロックを取得し、変更及び削除されないことが保証されている。
楽観的ロック時、全てのレコードやエンティティを追跡し、コミット時にそれらが更新されていないことを検証する。
この分離レベルは、トランザクション内で読み書き両方の操作がある場合に適している。
例：ECサイト等。
SERIALIZABLE
悲観的ロック時は全ての参照に対して範囲ロックをかける。
楽観的ロック時は全ての参照を追跡し、トランザクションの最後に同時実行されている書き込み処理がないかを検証し、あればそれ以外のトランザクションを全てロールバックする。
例: 新しい値を算出するために範囲指定問い合わせが必要となる会計システム。
まとめ
- トランザクションのACID特性は、完全に満たそうとすると並列実行時のパフォーマンスが犠牲になることがある
- トランザクションへの影響のパターンと、分離レベルによる各ロックの挙動の違いを把握し、システムによって最適な分離レベルを選択すべきである
参考
プログラマのためのSQL 第4版 すべてを知り尽くしたいあなたに
間違い等あればご指摘願いますm(_ _)m
終わり