MVCC - Multi Version Concurrency Control
ご存知の通り、InnoDBはMVCCを実装している。そのため、分離レベルがREPEATABLE READの場合には、行にロックをかけることなく、一貫した読み取りが可能になっている。もし、あるトランザクションT1開始後に、別のトランザクションT2によって同じ行が書き換えられてしまった場合には、T1はロールバックセグメントにある古いバージョンの値を読み取ることができるので、T1内で実行したSELECTは常にT1開始時点のデータを参照することができるのである。大事なのでもう一度言うが、REPEATABLE READにおける単純なSELECTでは行ロックは必要ない。
Lost Update
素晴らしきかなMVCC。だがMVCCとて欠点がないわけではない。それがLost Updateだ。Lost Updateを最もシンプルに表現すると、後から書いたほうが勝つという性質だ。例えば次のようなトランザクションを考えて見て欲しい。
T1: | T2:
mysql> CREATE TABLE t ( |
-> a INT UNSIGNED NOT NULL PRIMARY KEY, |
-> b INT NOT NULL |
-> ) ENGINE INNODB; |
mysql> INSERT INTO t VALUES(1, 100); |
mysql> COMMIT; |
|
mysql> BEGIN; |
mysql> SELECT b INTO @x FROM t WHERE a = 1; |
|
| mysql> BEGIN;
| mysql> SELECT b INTO @x FROM t WHERE a = 1;
| mysql> UPDATE t SET b = @x + 1 WHERE a = 1;
| mysql> COMMIT;
|
mysql> UPDATE t SET b = @x + 10 WHERE a = 1; |
mysql> COMMIT; |
この2つのトランザクションを実行するとbの値は最終的に何になるかお分かりだろうか。答えは110である。T1とT2を順に実行すると111となるはずだが、T2による更新は上書きされてしまう。もしこの例とは反対にT1が先にコミットしていれば、T1による更新はT1によって上書きされるだろう。これがロストアップデートである。
この例では解説を簡単化するためにSQL上のユーザー変数に値を代入しているが、実際のアプリケーションではそのようなことはしないだろう。なので実際にはアプリケーション側の変数に代入し、加工し、UPDATEするという操作に相当すると考えて欲しい。
Locking Read
Lost Updateを防ぐにはどうすれば良いだろうか。そう、皆さんよくご存知のLocking Readである。Locking Readとは、自らが更新したい行、あるいは他のトランザクションからの更新をブロックしたい行に対してロックをかけるようなSELECTのことである。排他ロックならFOR UPDATE、共有ロックならLOCK IN SHARE MODEを使う。そうすることで他のトランザクションをブロックし、Lost Updateを防ぐことができる。次の例はFOR UPDATを使った例である。
T1: | T2:
mysql> CREATE TABLE t ( |
-> a INT UNSIGNED NOT NULL PRIMARY KEY, |
-> b INT NOT NULL |
-> ) ENGINE INNODB; |
mysql> INSERT INTO t VALUES(1, 100); |
mysql> COMMIT; |
|
mysql> BEGIN; |
mysql> SELECT b INTO @x FROM t WHERE a = 1 FOR UPDATE; |
|
| mysql> BEGIN;
| mysql> SELECT b INTO @x FROM t WHERE a = 1 FOR UPDATE;
| ※ T1がコミットするまでブロックされる
|
mysql> UPDATE t SET b = @x + 10 WHERE a = 1; |
mysql> COMMIT; |
|
| mysql> UPDATE t SET b = @x + 1 WHERE a = 1;
| ※ @x = 110
| mysql> COMMIT;
この例では、T2によって読み取られた値は110となり、最終的なbの値は111となる。
問題点：100% 一貫性読み取りではない
勘の良い読者の方は「はて？」と思われたかも知れない。先ほどの例では何故T2のSELECT ... FOR UPDATEによって読み取られたbの値は110だったのだろうか。T2が開始したのはT1がコミットする前なので、REPEATABLE READならbの値は100であるべきではないか？と。何を隠そう、InnoDBのREPEATABLE READではその時の最新の値、つまり最も新しいバージョンが読み取られるという仕様になっている。だから他のトランザクションがロックしている行を参照しようとすると待たされるが、その後コミットされたホヤホヤの新しい値が得られるのである。
これはLost Updateを防ぐという点では妥当な仕様であるが、注意すべき点がある。それは、Locking ReadとNon-Locking Readでは値が異なるということだ。どういうことか具体的に見てみよう。
T1: | T2:
mysql> CREATE TABLE t ( |
-> a INT UNSIGNED NOT NULL PRIMARY KEY, |
-> b INT NOT NULL |
-> ) ENGINE INNODB; |
mysql> INSERT INTO t VALUES(1, 100); |
mysql> COMMIT; |
|
mysql> BEGIN; |
mysql> SELECT b INTO @x FROM t WHERE a = 1 FOR UPDATE; |
|
| mysql> BEGIN;
| mysql> SELECT b FROM t;
| +------+
| | b |
| +------+
| | 100 |
| +------+
| 1 row in set (0.00 sec)
|
mysql> UPDATE t SET b = @x + 10 WHERE a = 1; |
mysql> INSERT INTO t VALUES (2, 200); |
mysql> COMMIT; |
|
| mysql> SELECT b FROM t;
| +------+
| | b |
| +------+
| | 100 |
| +------+
| 1 row in set (0.00 sec)
|
| mysql> SELECT b FROM t FOR UPDATE;
| +------+
| | b |
| +------+
| | 110 |
| | 200 |
| +------+
| 2 rows in set (0.00 sec)
|
| mysql> COMMIT;
なお、InnoDBのNon-Locking Read/REPEATABLE READではネクストキーロックによって防いでくれるファントムも、Locking Readでは発生してしまう。Locking ReadはREAD COMMITTED相当の挙動になると考えて貰えれば差し支えない。これは普段何気なく使っていると気づかない点なので注意して欲しい。
また、UPDATEやDELETEはデフォルトでLocking Readの挙動となる。特に範囲指定でUPDATEやDELETEをする場合には、意図しない行までが更新または削除されないよう気をつけよう。いずれの場合も事前にSELECT ... FOR UPDATEをしておけば安全だと言える。
デッドロック
ところで、FOR UPDATEを使うとひとつの行に対して最大ひとつのトランザクションしかアクセスすることはできない。そのため、共有ロックを使おうという考えに至るかも知れないが、これも若干注意が必要である。何故ならばデッドロックの原因になるからだ。次の例はデッドロックになる様子を示している。T1: | T2:
mysql> CREATE TABLE t ( |
-> a INT UNSIGNED NOT NULL PRIMARY KEY, |
-> b INT NOT NULL |
-> ) ENGINE INNODB; |
mysql> INSERT INTO t VALUES(1, 100); |
mysql> COMMIT; |
|
mysql> BEGIN; |
mysql> SELECT b INTO @x FROM t WHERE a = 1 LOCK IN SHARE MODE; |
|
| mysql> BEGIN;
| mysql> SELECT b INTO @x FROM t WHERE a = 1 LOCK IN SHARE MODE;
| ※ T1によってブロックされない。
|
mysql> UPDATE t SET b = @x + 10 WHERE a = 1; |
※ このUPDATEはT2の共有ロックによってブロックされる。 |
|
| mysql> UPDATE t SET b = @x + 1 WHERE a = 1;
| ※ デッドロック発生！
ファントムを防ぐ
ファントムを防ぐのは極めて簡単だ。分離レベルをSERIALIZABLEにすれば良い。もともとSQLの仕様としては、REPEATABLE READではファントムは防げないことになっている。InnoDBのNon-Locking Readでファントムが起きないのはオマケのようなものだ。ファントムをきっちり防ぎたければ分離レベルをSERIALIZABLEにすれば良い。SERIALIZABLEでファントムがなぜ起きないかというと、INSERTあるいはファントムを読む可能性のあるSELECTがブロックされるからだ。次の例はその様子を示したものである。
T1: | T2:
mysql> CREATE TABLE t ( |
-> a INT UNSIGNED NOT NULL PRIMARY KEY, |
-> b INT NOT NULL |
-> ) ENGINE INNODB; |
mysql> INSERT INTO t VALUES(1, 100); |
mysql> COMMIT; |
|
mysql> SET tx_isolation = SERIALIZABLE; |
mysql> BEGIN; |
mysql> SELECT b FROM t WHERE a = 1; |
+-----+ |
| b | |
+-----+ |
| 100 | |
+-----+ |
1 row in set (0.00 sec) |
|
| mysql> SET tx_isolation = SERIALIZABLE;
| mysql> BEGIN;
| mysql> SELECT b FROM t;
| +------+
| | b |
| +------+
| | 100 |
| +------+
| 1 row in set (0.00 sec)
|
mysql> INSERT INTO t VALUES (2, 200); |
※ T2がコミットするまでブロックされる |
| 以下略
T2が実行しているSELECTにはWHERE句がない。そのため、T1が行をINSERTしてしまうと、もう一度T2が同じSELECTを実行したときに結果が異なってしまう。（＝ファントム）そのため、T2の結果が変わらないよう、T2が完了するまでT1をブロックする必要があるというわけである。
今度は反対にSELECTがブロックされる例を見てみよう。
T1: | T2:
mysql> CREATE TABLE t ( |
-> a INT UNSIGNED NOT NULL PRIMARY KEY, |
-> b INT NOT NULL |
-> ) ENGINE INNODB; |
mysql> INSERT INTO t VALUES(1, 100); |
mysql> COMMIT; |
|
mysql> SET tx_isolation = SERIALIZABLE; |
mysql> BEGIN; |
mysql> SELECT b FROM t WHERE a = 1; |
+-----+ |
| b | |
+-----+ |
| 100 | |
+-----+ |
1 row in set (0.00 sec) |
|
| mysql> SET tx_isolation = SERIALIZABLE;
| mysql> BEGIN;
| mysql> SELECT b FROM t WHERE a = 1;
| +------+
| | b |
| +------+
| | 100 |
| +------+
| 1 row in set (0.00 sec)
|
mysql> INSERT INTO t VALUES (2, 200); |
|
| mysql> SELECT b FROM t;
| ※ T1がコミットするまでブロックされる。
|
mysql> COMMIT; |
|
| +------+
| | b |
| +------+
| | 100 |
| | 200 |
| +------+
| 2 rows in set (2.81 sec)
| 以下略
2つ目の例ではT1がコミットした行がT2から見えているが、これはファントムではない。T2はまだこのクエリの結果を得ていなかったからだ。一貫性読み取りとは過去のSELECTと同じ結果になることなのだから、SELECTが完了していないうちは過去のSELECTの結果は未知のままである。従って、この場合はSELECTの結果の一貫性が崩れたわけではないと言える。だからT2のSELECTは、T1の結果が確定するまでブロックされる必要があるというわけである。
SERIALIZABLEは便利だが、ロックモードの指定がない通常のSELECTは、LOCK IN SHARE MODE相当になるという点に注意が必要だ。（ちなみに、全てのSELECTをLocking ReadにすればSERIALIZABLE相当になる。）従ってREPEATABLE READよりもデッドロックが発生する確率が高くなってしまうだろう。SERIALIZABLE分離レベルででは、トランザクション開始時にSELECT ... FOR UPDATEを使って行を排他ロックし、他のトランザクションの実行をブロックすることで、デッドロックの発生頻度を若干下げることが可能である。
まとめ
というわけで、今回はInnoDBのREPEATABLE READ分離レベルにおけるLocking Read、Non-Locking Read、さらにSERIALIZABLE分離レベルの挙動について説明した。REPEATABLE READの場合は、ロックモードの指定有無で結果が異なる可能性があるという点に注意して頂きたい。アプリケーションが完全な一貫性読み取りを必要としている場合には、SERIALIZABLEを使うべきである。ただし、SERIALIZABLEはロックが多くなり、デッドロックの可能性も高まるので、更新系トランザクションの並列度が落ちてしまう恐れがある。もしトランザクションが参照のみであれば、REPEATABLE READで全く問題がないので、トランザクションにおける更新の有無で分離レベルを使い分けるようにしよう。
分離レベルとロックの挙動を正しく理解し、幸せなInnoDBライフを送って頂きたい。
明日は@n0tsさんである。
0 コメント:
コメントを投稿