InnoDBのREPEATABLE READでハマった話
MySQL5系で、REPEATABLE READな環境があったのですが、ん?と思うことがあったので、備忘録。 結論から言うと、MySQL5.5 + InnoDBでファジーリードやファントムリードと思われる挙動が確認できた(気がする)。
テスト用のテーブル定義は以下。
mysql>
CREATE TABLE `tx_test` (
`id` int(11) NOT NULL,
`num` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
;
テストデータ入れる。
mysql>
INSERT INTO tx_test (id, num) VALUES
(1,1),
(2,5),
(3,10)
;
こんな感じでテータ入ってる。
mysql> SELECT * FROM tx_test;
+----+-----+
| id | num |
+----+-----+
| 1 | 1 |
| 2 | 5 |
| 3 | 10 |
+----+-----+
で、これから2つのトランザクションを始める。AとBということにしておこう。 まず、Aから。
mysql> BEGIN;
mysql> SELECT @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
mysql> SELECT * FROM tx_test WHERE id = 3;
+----+-----+
| id | num |
+----+-----+
| 3 | 10 |
+----+-----+
何も難しいことはない。 次にトランザクションBを開始。
mysql> BEGIN;
mysql> SELECT @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
mysql> SELECT * FROM tx_test WHERE id = 3;
+----+-----+
| id | num |
+----+-----+
| 3 | 10 |
+----+-----+
まぁ、当然だろう。次にトランザクションAでUPDATE走らせる。
mysql> UPDATE tx_test SET num = num + 1 WHERE id = 3;
mysql> SELECT * FROM tx_test WHERE id = 3;
+----+-----+
| id | num |
+----+-----+
| 3 | 11 |
+----+-----+
numがインクリメントされた。この状態でトランザクションBで同じSELECT文を走らせる。
mysql> SELECT * FROM tx_test WHERE id = 3;
+----+-----+
| id | num |
+----+-----+
| 3 | 10 |
+----+-----+
ファジーリードは発生しないので、トランザクションBはトランザクションAの未コミットなSQLの影響を受けない。 ので、numは10のまま。
んで、次にトランザクションBで同じUPDATE文を走らせてみる。
mysql> UPDATE tx_test SET num = num + 1 WHERE id = 3;
トランザクションAがコミットをまだしてないので、ここで行ロック待ちとなる。 なので、トランザクションAでコミットする。
mysql> COMMIT;
んで、トランザクションBでSELECT文走らせる。この段階ではまだトランザクションBはcommitしてない。
mysql> SELECT * FROM tx_test WHERE id = 3;
+----+-----+
| id | num |
+----+-----+
| 3 | 12 |
+----+-----+
numが10から12になった。REPEATABLE READだから、ロストアップデート以外他のトランザクションの影響を受けないと思ってたんだけど、影響受けるのね。 こういうもんなんだろうか。
ちなみにトランザクションBではcommitしてないので、この状態でトランザクションAでcommitすると面白い結果となる。
mysql> SELECT * FROM tx_test WHERE id = 3;
+----+-----+
| id | num |
+----+-----+
| 3 | 11 |
+----+-----+
12にはならない。要はトランザクションBのUPDATEはトランザクションAにはまだ反映されてない。 ので、トランザクションBでcommitしよう。
mysql> COMMIT;
んで、その後トランザクションAでcommitする。
mysql> SELECT * FROM tx_test WHERE id = 3;
+----+-----+
| id | num |
+----+-----+
| 3 | 12 |
+----+-----+
これで12になった。なんか不思議な感じで違和感あるんだけど、こういうものらしい。
もう一つ、実験してみる。 まず、トランザクションAとBを同じように開始させる。
mysql> BEGIN;
AとBが開始したので、まずSELECTで値見ておく。同じ行を使おう。 ただし、今回はWHERE区を変える。「numが12の行」にしよう。
mysql> SELECT * FROM tx_test WHERE num = 12;
+----+-----+
| id | num |
+----+-----+
| 3 | 12 |
+----+-----+
んで、トランザクションAで以下のUPDATEを走らせる。
mysql> UPDATE tx_test SET num = num + 1 WHERE num = 12;
mysql> SELECT * FROM tx_test WHERE num = 12;
Empty set (0.00 sec)
mysql> SELECT * FROM tx_test WHERE num = 13;
+----+-----+
| id | num |
+----+-----+
| 3 | 13 |
+----+-----+
結果、こうなる。12のものをインクリメントしてるんだから、当然numが12のものはなく、13のものでないとフェッチできない。
この状態でトランザクションBでSELECTするが、未コミットなトランザクションの影響は受けない(ファジーリードは発生しない)ので、numが12のものでフェッチできる。 13ではフェッチできない。
mysql> SELECT * FROM tx_test WHERE num = 12;
+----+-----+
| id | num |
+----+-----+
| 3 | 12 |
+----+-----+
mysql> SELECT * FROM tx_test WHERE num = 13;
Empty set (0.00 sec)
じゃ、この状態でnumが13の行をUPDATEしたらどうなるのだろうか。トランザクションBで以下のUPDATE発行させる。
mysql> UPDATE tx_test SET num = num + 1 WHERE num = 13;
まず、行ロック待ちとなる。InnoDBでは広めのロックを取ってると認識してるけど、間違ってたらすいません。 トランザクションAでコミットする。
mysql> COMMIT;
まず、トランザクションAでSELECTする。
mysql> SELECT * FROM tx_test WHERE num = 12;
Empty set (0.00 sec)
mysql> SELECT * FROM tx_test WHERE num = 13;
+----+-----+
| id | num |
+----+-----+
| 3 | 13 |
+----+-----+
commitしたので、まぁ当然こうなる。 次に、トランザクションBでSELECTする。
mysql> SELECT * FROM tx_test WHERE num = 12;
+----+-----+
| id | num |
+----+-----+
| 3 | 12 |
+----+-----+
トランザクションAの影響は受けず、フェッチできる。 トランザクションBでコミットする。
mysql> COMMIT;
んで、トランザクションBでフェッチしてみる。
mysql> SELECT * FROM tx_test WHERE num = 12;
Empty set (0.00 sec)
mysql> SELECT * FROM tx_test WHERE num = 13;
Empty set (0.00 sec)
mysql> SELECT * FROM tx_test WHERE num = 14;
+----+-----+
| id | num |
+----+-----+
| 3 | 14 |
+----+-----+
SELECT文でファジーリードは起きてないけど、UPDATE文では、num = num + 1した他のトランザクションの影響を受け、ファントムリードが起きている。 ファントムリードが起きない実装じゃなくて、こういうときはInnoDBでもファントムリード起きちゃうのかな。
間違い・勘違い等あったら、@masudaK までご指摘お願いします。