MySQLのMVCC

MVCCとは

MultiVersion Concurrency Controllの略で，RDBのisolation levelがREAD COMMITTEDやREPEATBLE READなんかの時のために採用しているシステムのことです．
簡単に言うと「テーブルの過去の情報を持っておく」です．

isolation level

DBでは同時に複数トランザクションが一つのテーブルを操作したりします．
例えばあるトランザクションがテーブルAを参照中に，Aの内容を書き換えるような別のトランザクションが存在すると，取ってきたデータに不整合が生じます．
この不整合をどの程度許容するかがisolation levelです．通常4種類あります．

1. READ UNCOMMITTED ... COMMITされていないトランザクションAの変更をトランザクションBが参照できます
2. READ COMMITTED ... COMMITされたトランザクションAの変更をトランザクションBが参照できます
3. REPEATBLE READ ... COMMITされたトランザクションAの追加をトランザクションBが参照できます
4. SERIALIZE ... 同時に最大1つまでのトランザクションしか存在しないのと等価です

2以降を実現するためにはSELECTで共有ロックを，UPDATEとINSERTで専有ロックを取得すれば良いのですが
パフォーマンスを考えるとなるべくロックの取得はしたくないです．そこで考え出されたのがMVCCです．
MVCC実装はRDBやストレージエンジンによって違いますがここではMySQLのinnodbを考えます．

MVCC実装

全てのテーブルの全てのレコードにはMySQLが自動的に以下の3つのカラムを追加します(ユーザからは見えません)

• DB_ROW_ID ... その行のID
• DB_TRX_ID ... 最後にそのレコードを追加・更新したトランザクションID
• DB_ROLL_PTR ... そのレコードの過去の値を持つundo log recordへのポインタ

トランザクションIDが100のトランザクションがあるレコードを挿入すると
DB_TRX_IDに100が代入されます．DB_ROLL_PTRはNULLです．
トランザクションIDが102のトランザクションでレコードの値を書き換えると，undo log recordに過去の値が保存され，DB_ROLL_PTRにそのrecordへのポインタが代入され，DB_TRX_IDに102が入ります．

もしこれらの変更がCOMMITされたとして，isolation levelがREPEATABLE READの場合，
トランザクションIDが99のトランザクションはこのレコードを参照できません．
トランザクションIDが101のトランザクションはこのレコードの変更前の値をundo log recordから探して参照します．
トランザクションIDが103のトランザクションは変更後の値を見ることが出来ます．
ざっくり「自分のトランザクションIDより未来の追加と変更は取得されない」と思っておけばそこまで困りません．

実験

シェルを2つ起動して両方共mysqlに接続します．
データベースはなんでもいいですが適当にsakila-databaseを利用します．
片方のシェルでTRX 1に書かれた命令を，もう片方のシェルでTRX 2の命令を実行してください．ただし，コマンドの実行順序は上の行から順番に．
同じ行であればどちらを先に実行しても構いません．

TRX 1	TRX 2
USE sakila;	USE sakila;
BEGIN;	BEGIN;
SELECT * FROM language;
	INSERT INTO language (name) VALUE ('Spanish');
SELECT * FROM language;	SELECT * FROM language;
	COMMIT;
SELECT * FROM language;
SELECT * FROM language LOCK IN SHARE MODE;

TRX 1のSELECTは最後以外はSpanishは見られませんがTRX 2の最後のSELECTでは存在します．
MVCCが働いてTRX 1ではDB_ROW_IDが未来のトランザクションであるレコードは読み取られなかったのです．
TRX 1の最後のSELECTではSpanishが存在するのはSELECT ... LOCK IN SHARE MODEとSELECT ... FROM UPDATEの2つが例外的にMVCCを無視して行ロックを取得して最新レコードを取得する命令だからです．
この状態になるとTRX 1をCOMMITかROLLBACKするまでロックされたままです．よくデッドロックの原因やパフォーマンス低下に繋がるので可能な限りロックを取得するSELECTはやめましょう．

ちなみに

上とよく似た実験ですが

TRX 1	TRX 2
USE sakila;	USE sakila;
BEGIN;
	BEGIN;
	INSERT INTO language (name) VALUE ('Spanish');
	COMMIT;
SELECT * FROM language;

この状態だと一見，TRX 2の方がトランザクションIDが大きい値なのでTRX 1ではCOMMITされたレコードを見ることが出来ない気がしますが，実際は見られます．
これは実はトランザクションIDはBEGIN;で割り振られるわけでなく，SELECT等のSQLで初めて割り振られるからです．TRX 1のトランザクションIDは最後のSELECTで初めて割り振られます．
現在のトランザクションのIDは

トランザクションIDの取得

SELECT trx_id FROM information_schema.innodb_trx WHERE trx_mysql_thread_id = CONNECTION_ID();

で見ることが出来ますが，BEGIN直後にこのSQLを実行してもempty setが返ってきます(ということはこのinformation_schemaに対するSELECT文もトランザクションIDが発行されなかったってことです)
ちなみにSELECT 1 FROM dualのようなSQLでもトランザクションIDは割り振られません．