MySQLにおけるMVCCについて調べてみる
先日、翔泳社で電子書籍のセールがあって、奥野さんの詳解MySQL5.7が販売されていました。すでに紙の書籍としては持っていたのですが、持ち運びに便利な電子版もこの度購入しました。
※すでにセールは終わっています
MySQL5.7の機能をここまで詳しくまとめられているものは他にないのではと思うレベルで詳しく書かれていますのでお勧めです。
さて今回はMySQLにおけるMVCCについて考えてみようと思います。 上記書籍を読んでいて知らなかった部分もあってこの機に改めて調べてみようと思った際のメモです。
何気に初の1日2記事投稿です。（3連休スゴイ！）
そもそもMVCCとは
MVCCとはMultiVersion Concurrency Controlの略で日本語で訳すと多版型同時実行制御です。（まんまですが・・・）
MySQL特有の機能というわけではなく、DBMSに一般的なものです。 OracleはもちろんPostgreSQL、DB2など広く採用されている技術です。
これは複数のユーザ（MySQLではスレッド）が同時にトランザクションを実行しても同時並行性を犠牲にすることなく、なおかつ一貫性を保証するというものです。 簡単に言うと、MVCCはあるトランザクションに対してそのデータの過去のバージョンを参照させることで一貫性を保証しています。
MySQLでは後述するトランザクション分離レベルがREPEATABLE-READ(およびREADREAD-COMMITTED)時に利用されます。
トランザクション分離レベルとは
MVCCを調べるために避けて通れないものがトランザクション分離レベルです。 これは簡単に言うと複数のトランザクションが実行されうる環境でどれだけの一貫性、正確性を保証するのかを定義したものです。
一般的なトランザクション分離レベルを下記にまとめます。下に行くほど分離レベルは高くなります。
- READ-UNCOMMITTED
最も低い分離レベルです。
COMMITされていないトランザクションの情報も他のトランザクションから参照できてしまうという特徴があります。
T1 >SELECT @@session.transaction_isolation; +---------------------------------+ | @@session.transaction_isolation | +---------------------------------+ | READ-UNCOMMITTED | +---------------------------------+ 1 row in set (0.00 sec) T1 >BEGIN; T1 >SELECT * FROM t1; +---+------+ | a | b | +---+------+ | 1 | hoge | | 2 | piyo | | 3 | var | +---+------+ 3 rows in set (0.00 sec) 異なるセッションから T2 >SELECT @@session.transaction_isolation; +---------------------------------+ | @@session.transaction_isolation | +---------------------------------+ | READ-UNCOMMITTED | +---------------------------------+ 1 row in set (0.00 sec) T2 >BEGIN; T2 >UPDATE t1 SET b = 'hogehoge' WHERE a = 1; Query OK, 1 row affected (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 0 T2はまだCOMMITしていない 再び元のセッションに T1 >SELECT * FROM t1; +---+----------+ | a | b | +---+----------+ | 1 | hogehoge | | 2 | piyo | | 3 | var | +---+----------+ 3 rows in set (0.00 sec) T2の変更が見えてしまった
(この現象をDIRTY READと言います)
- READ-COMMITTED
DIRTY READは発生しませんが、COMMITされたトランザクションは他のトランザクションから参照できてしまいます。
T1 >SELECT @@session.transaction_isolation; +---------------------------------+ | @@session.transaction_isolation | +---------------------------------+ | READ-COMMITTED | +---------------------------------+ 1 row in set (0.00 sec) T1 >BEGIN; Query OK, 0 rows affected (0.00 sec) T1 >SELECT * FROM t1; +---+------+ | a | b | +---+------+ | 1 | hoge | | 2 | piyo | | 3 | var | +---+------+ 3 rows in set (0.00 sec) 異なるセッションから T2 >SELECT @@session.transaction_isolation; +---------------------------------+ | @@session.transaction_isolation | +---------------------------------+ | READ-COMMITTED | +---------------------------------+ 1 row in set (0.00 sec) T2 >BEGIN; Query OK, 0 rows affected (0.00 sec) T2 >UPDATE t1 SET b = 'hogehoge' WHERE a = 1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 再び元のセッションに T1 >SELECT * FROM t1; +---+------+ | a | b | +---+------+ | 1 | hoge | | 2 | piyo | | 3 | var | +---+------+ 3 rows in set (0.00 sec) まだCOMMITしていなのでT2の変更は見えない 再度異なるセッションから T2 >COMMIT; Query OK, 0 rows affected (0.01 sec) T2をCOMMIT！！ 元のセッション T1 >SELECT * FROM t1; +---+----------+ | a | b | +---+----------+ | 1 | hogehoge | | 2 | piyo | | 3 | var | +---+----------+ 3 rows in set (0.00 sec) T2がCOMMITした内容が見えてしまう
(この現象をNON-REPEATABLE-READといいます)
OracleやPostgreSQLではこのREAD-COMMITTEDがデフォルトの分離レベルとなっているようです。
- REPEATABLE-READ
MySQL(InnoDB)でデフォルトの分離レベルとなっています。
上記のDIRTY READやNON REPEATABLE READが起きないことを保証しますが、あるトランザクションに対して、他のトランザクションが挿入や削除を行ったことによって読み込んだデータが異なってしまうというPHANTOM READと呼ばれる現象が起きます。
T1 >SELECT @@session.transaction_isolation; +---------------------------------+ | @@session.transaction_isolation | +---------------------------------+ | READ-COMMITTED | +---------------------------------+ 1 row in set (0.00 sec) T1 >BEGIN; Query OK, 0 rows affected (0.00 sec) T1 >SELECT * FROM t1; +---+----------+ | a | b | +---+----------+ | 1 | hogehoge | | 2 | piyo | | 3 | var | +---+----------+ 3 rows in set (0.00 sec) 異なるセッションから T2 >SELECT @@session.transaction_isolation; +---------------------------------+ | @@session.transaction_isolation | +---------------------------------+ | READ-COMMITTED | +---------------------------------+ 1 row in set (0.00 sec) T2 >BEGIN; Query OK, 0 rows affected (0.00 sec) T2 >INSERT INTO t1 VALUES (4, 'boo'); Query OK, 1 row affected (0.00 sec) T2 >COMMIT; Query OK, 0 rows affected (0.02 sec) 再び元のセッションに T1 >SELECT * FROM t1; +---+----------+ | a | b | +---+----------+ | 1 | hogehoge | | 2 | piyo | | 3 | var | | 4 | boo | +---+----------+ 4 rows in set (0.00 sec) T2に挿入された行が見えてしまった(見えなかったものが見えるためPHANTOM READという)
※ただしInnoDBではネクストキーロックと呼ばれるロックを使ってPHANTOM READが起きないような設計となっています。(そのため上記はREAD COMMITTEDで行っています)
- SERIALIZABLE 最も高い分離レベルです。
すべての操作に対してロックを取得するため、高い一貫性を保証しますが、その代わりに同時実行性を犠牲にしています。
一般的にはREAD COMMITTEDやREPEATABLE READが採用されているケースが多いように感じます。
MVCCの仕組み
MVCCはInnoDBにおいてREPEATABLE-READを実現するための仕組みです。 (実際にはREAD-COMMITTEDでも採用されているのですが、今回は対象外として考えます)
これは先ほども述べたように、あるデータの過去のバージョンを見せることで一貫した参照をトランザクションに提供する仕組みです。これにより、一貫性を保証するために行に対してロックをかける必要がなくなるため同時実行性が改善されます。
実際に例をお見せします。
T1 >BEGIN; Query OK, 0 rows affected (0.00 sec) T1 >SELECT * FROM t1; +---+------+ | a | b | +---+------+ | 1 | hoge | | 2 | piyo | | 3 | var | +---+------+ 3 rows in set (0.00 sec) T1 >UPDATE t1 SET b = 'piyopiyo' WHERE a = 2; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 T1 >SELECT * FROM t1; +---+----------+ | a | b | +---+----------+ | 1 | hoge | | 2 | piyopiyo | | 3 | var | +---+----------+ 3 rows in set (0.00 sec) 異なるセッション T2 >BEGIN; Query OK, 0 rows affected (0.00 sec) T2 >SELECT * FROM t1; +---+------+ | a | b | +---+------+ | 1 | hoge | | 2 | piyo | | 3 | var | +---+------+ 3 rows in set (0.00 sec) この時点ではT1のトランザクションがCOMMITされていないため、T1による変更がされるまえのデータが見える 再びT1に戻る T1 >COMMIT; Query OK, 0 rows affected (0.01 sec) 再びT2に戻る T2 >SELECT * FROM t1; +---+------+ | a | b | +---+------+ | 1 | hoge | | 2 | piyo | | 3 | var | +---+------+ 3 rows in set (0.01 sec) T1はCOMMITしたがMVCCによって古いバージョンを参照しているためT1の変更は見えない T2 >UPDATE t1 SET b = 'hogehoge' WHERE a = 1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 T2 >SELECT * FROM t1; +---+----------+ | a | b | +---+----------+ | 1 | hogehoge | | 2 | piyo | | 3 | var | +---+----------+ 3 rows in set (0.00 sec)
このようにMVCCによってそのトランザクションが始まった瞬間のデータを一貫して参照できるようになっています。
ちなみに過去のデータはUNDOログと呼ばれる領域にて管理されています。
MVCCとロック
見てきたようにMVCCは同時実行性を向上させる素晴らしい技術であることは間違いないのですが、もちろん万能ではありません。 なぜなら、InnoDBでMVCCが使われるのはノンロッキングリードに対してのみなだからです。ノンロッキングリードとはその名の通り行にロックをかけない読み取りのことを指します。
ノンロッキングリードであればあるトランザクションが読み取ろうとした行が他のトランザクションによって更新中であっても（つまりロックを取得していても）、MVCCによって古いバージョンのデータを行ロックをせずに読み取れます。新たに行にロックをかけないため高い同時実行性が実現できるのです。
逆にロッキングリードではMVCCを用いることはできません。例えばある行に対して更新をしたければ最新のデータを読み取る必要がありますし、更新の際にはそのトランザクションが完了するまでロックをしておく必要があります。
具体的なロッキングリードは例えばSELECT ... FROM ... FOR UPDATEやSELECT ... FROM ... LOCK IN SHARE MODEのようなものです。また、もちろん更新(INSERT, UPDATE, DELETE)もロックが必要な操作です。
こちらも具体例を見てみましょう。
T1 >BEGIN; Query OK, 0 rows affected (0.01 sec) T1 >SELECT * FROM t1; +---+------+ | a | b | +---+------+ | 1 | hoge | | 2 | piyo | | 3 | var | +---+------+ 3 rows in set (0.00 sec) T1 >UPDATE t1 SET b = 'piyopiyo' WHERE a = 2; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 T1 >SELECT * FROM t1; +---+----------+ | a | b | +---+----------+ | 1 | hoge | | 2 | piyopiyo | | 3 | var | +---+----------+ 3 rows in set (0.00 sec) 異なるセッションから T2 >BEGIN; Query OK, 0 rows affected (0.00 sec) T2 >SELECT * FROM t1; +---+------+ | a | b | +---+------+ | 1 | hoge | | 2 | piyo | | 3 | var | +---+------+ 3 rows in set (0.00 sec) T2 >SELECT * FROM t1 FOR UPDATE; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
とこのようにただのSELECTであればMVCCのおかげで古いバージョンを見ることで実行できていますが、ロッキングリードであるFOR UPDATEをつけて実行するとタイムアウトとなってしまいました。
これはInnoDBのトランザクションにおいてinnodb_lock_wait_timeoutを超えてもロックが取得できなかったためタイムアウトとなっている様子です。
もう一つ面白い例を挙げましょう。
これは詳解MySQL5.7でも言及されている問題で、書籍を読む前はこのことを知りませんでした。
T1 >CREATE TABLE t2 (id int primary key); Query OK, 0 rows affected (0.04 sec) T1 >INSERT INTO t2 VALUES (100), (300); Query OK, 2 rows affected (0.02 sec) Records: 2 Duplicates: 0 Warnings: 0 T1 >BEGIN; Query OK, 0 rows affected (0.00 sec) T1 >SELECT * FROM t2; +-----+ | id | +-----+ | 100 | | 300 | +-----+ 2 rows in set (0.00 sec) 異なるセッションから T2 >BEGIN; Query OK, 0 rows affected (0.00 sec) T2 >INSERT INTO t2 VALUES (200); Query OK, 1 row affected (0.00 sec) T2 >COMMIT; Query OK, 0 rows affected (0.01 sec) 再び元のセッションに T1 >UPDATE t2 SET id = id + 1; Query OK, 3 rows affected (0.00 sec) Rows matched: 3 Changed: 3 Warnings: 0 2行しかないはずのテーブルで3行に対して変更があったといわれている T1 >SELECT * FROM t2; +-----+ | id | +-----+ | 101 | | 201 | | 301 | +-----+ 3 rows in set (0.00 sec) 実際にSELECTしてみると3行のデータが見える
これはまさにノンロッキングリードとロッキングリードが混在することで起きてしまう問題です。
T1がt2に対してUPDATEをすると、これは更新操作であり最新のデータを読みに行く必要があります。この時の最新のデータとはT2がCOMMITした後の3行ある状態を指します。そのためUPDATEをした際に3行のデータを読みその後のSELECTでも3行のデータが見えてしまったことになります。
これを防ぐためには分離レベルをSERIALIZABLEする必要があります。
T1 >SELECT @@session.transaction_isolation; +---------------------------------+ | @@session.transaction_isolation | +---------------------------------+ | SERIALIZABLE | +---------------------------------+ 1 row in set (0.00 sec) T1 >BEGIN; Query OK, 0 rows affected (0.00 sec) T1 >SELECT * FROM t2; +-----+ | id | +-----+ | 100 | | 300 | +-----+ 2 rows in set (0.00 sec) 異なるセッションから T2 >SELECT @@session.transaction_isolation; +---------------------------------+ | @@session.transaction_isolation | +---------------------------------+ | SERIALIZABLE | +---------------------------------+ 1 row in set (0.00 sec) T2 >BEGIN; Query OK, 0 rows affected (0.00 sec) T2 >INSERT INTO t2 VALUES (200); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction そもそもINSERTがロック待ちとなる
SERIALIZABLEなのですべての操作がロックをかけます。ゆえにT1がSELECTをした時点でロックがかかってしまい、後続のT2による挿入がロック待ちとなるのです。すべての操作がロックをかけるため非常に高い一貫性を提供します。その代わりに本来不要だと思われる単一のリードですらロックをかけるため、非常に同時実行性を犠牲にしています。
最後に
今回は復習がてらMVCCを調べ実際に試してみました。
今回は詳しく触れませんでしたが、InnoDBには他にもレコードロックやギャップロック、そして(一部で悪名高い)ネクストキーロックと呼ばれるロックでトランザクションの同時実行を制御しています。
仕組みを知らなくてもある程度うまく扱うことはできますが、ブラックボックスにするのではなく中身をそして仕組みを（さらにはその設計思想でさえ）時には考えることも必要なのだと感じます。
そして何よりMySQLについて考えるのは楽しいですからね ; )
