はじめに
トランザクションといえば知ってる人は多いと思います。一言いうと「分けることのできない一連操作」。一番よくある例は振り込みです。
A口座からB口座へ１万円を振り込み場合、DBを使えば少なくとも下記２ステップは必要
①A口座の残高レコード１万を引く
②B口座の残高レコード１万を加える
もし①を終わる瞬間にシステム障害になって②を実行できなくなってしまうと大変な事になります。故に①と②は分けることのできない二つ操作です。
全て成功するか失敗するしかできません。
トランザクションはすごく難しいくて面白い話題だと思います。
今回はMySQLのトランザクションにサッポートする機能を踏まえて簡単な紹介及びタイトルについた面白そうな問題を解いてみましょう。
InnoDBのみ
MySQLの使用でトランザクションをサッポートされているが、全てのストレージエンジングではく、現時点までInnoDBのみサッポートされています。これもデフォルトストレージエンジングはMyISAMからInnoDBに変更する最も重要な理由である。
ACID
トランザクションの話になるとACIDも自然にでる。ACID（Atomicity、Consistency、Isolation、Durability）は信頼性のあるトランザクションシステムの持つべき性質です。具体的な説明はここに展開しないので、ウィキペディアに割愛します。今の例はAtomicity（原子性）です。
分離レベル
ACIDのACDは紛れもなく満足しないといけないが、I（分離性）は厳しく従うとシステムの並行処理もできなくなってパフォーマンスは大幅減になってしまいます。故に、一般的にデータベースのトランザクション機能は分離性を一部犠牲してパフォーマンスを増える選択も提供されている。MySQLの場合は分離レベルというものです。
分離レベルはRU(Read Uncommitted)、RC(Read Committed)、RR(Repeatable Read)及びSI (Serializable)四つがあって、違うレベルによりダーティリード、ファジーリードやファントムリードという問題が出てきます。具体的説明な記事は一杯あるのではここも展開しませんが、もし興味があればあるいは試したければや@PruneMazuiの記事と@song_ssさんの記事はオススメです。
何故MySQLのデフォルト分離レベルはRR？
そろそろ本番に入りましょう。知っている通り、PostgreSQL、Oracle及びSQL Serverのデフォルト分離レベルはRCである、何故MySQLのデフォルト分離レベルはRCにしませんか？
RUはデータの分離性に対して悪くすぎ、SIはパーフォマンスに対して悪くすぎのでまず話から外す。
RRを使わない理由は前回に紹介したバイナリログに関わっています。正確的には昔のバイナリログに関わっています。
バイナリログのフォーマットは下記３種類があります
・STATEMENT ー実行されたSQL文をそのまま記録する。
・ROWS ー実行されたSQL文によるデータの変化を記録する。
・MIXED ーSTATEMENTとROWを組み合わせたもの。
が、MySQL5.0以前はSTATEMENTしかサポートされていなかった。
で、バイナリログには何か問題がありますか。
MySQLのスレーブレプリケーションはダンプ＋バイナリログの実行で実装されています。
トランザクションの分離レベルをRead Committedにすれば、STATEMENTフォーマットのバイナリログでレプリケーションをする時マスターとスレーブデータ不一致な不具合が出て来る。
下記の例を見てください。(上からは時間順)
もしMySQLはマスタースレーブ構造であって、binlog_format=STATEMENTであれば、
マスターとスレーブにそれぞれ
mysql> select * from t;を実行すれば
マスターは
+---+---+
| c1 |c2
+---+---+
| 2 | 2
+---+---+
1 row in set
スレーブは
Empty set
になってしまいます。
セッション2の挿入データは分離レベルはRCのためセッション１が見えない（詳しいは後程紹介）のでセッション1の削除範囲外です。しかし、マスターのコミット順はセッション2→セッション１ので、バイナリログはスレーブに同じ順番で実行されます。
故に、セッション2に挿入されたレコードはセッション1に削除されしまう。
この問題を避ける方法は２つある、
1.バイナリログのフォーマットをROWやMIXEDにする。SQL文の代わりにデータの変化でレプリケーションすれば上記データ不一致の問題はなくなる。
2.トランザクションの分離レベルをRepeatable Readにする。
Repeatable Readにある(GAPロック)という機能でセッション2の挿入操作をブロックして、コミット順をセッション1→セッション2。GAPロックについては後ほど展開しますので、今回はスルー。
歴史的にはMySQL(5.0以前)のバイナリログフォーマットはSTATEMENTしかなかったので、2番目の方法で問題を避けます。
まとめ
今回はトランザクション及び分離レベルについて紹介しました。
分離レベルはRU(Read Uncommitted)、RC(Read Committed)、RR(Repeatable Read)及びSI (Serializable)四つがあります。MySQLのデフォルト分離レベルはRepeatable Readです。
原因としてはRead Committedとbinlog_format=STATEMENT組み合わせてスレーブレプリケーションによりデータ不一致な不具合が出てしまうです。解決する方法はRepeatable Readにある(GAPロック)あるいはバイナリログのフォーマットをROWやMIXEDにします(MySQL5.1以降サッポート)。