Help us understand the problem. What is going on with this article?

MySQLのMVCC

MVCCとは

MultiVersion Concurrency Controllの略で,RDBのisolation levelがREAD COMMITTEDREPEATBLE 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で共有ロックを,UPDATEINSERTで専有ロックを取得すれば良いのですが
パフォーマンスを考えるとなるべくロックの取得はしたくないです.そこで考え出されたのが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 1SELECTは最後以外はSpanishは見られませんがTRX 2の最後のSELECTでは存在します.
MVCCが働いてTRX 1ではDB_ROW_IDが未来のトランザクションであるレコードは読み取られなかったのです.
TRX 1の最後のSELECTではSpanishが存在するのはSELECT ... LOCK IN SHARE MODESELECT ... FROM UPDATEの2つが例外的にMVCCを無視して行ロックを取得して最新レコードを取得する命令だからです.
この状態になるとTRX 1COMMITROLLBACKするまでロックされたままです.よくデッドロックの原因やパフォーマンス低下に繋がるので可能な限りロックを取得する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は割り振られません.

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした