カイワレの大冒険 Third

技術的なことや他愛もないことをたまに書いてます

どうして「InnoDBのREPEATABLE READでハマった話」という記事を書いたのか ver2

以下のような記事を書いたのですが、@sh2ndさんに指摘されてひと通り整理できたので、まとめ。

簡単に言うと、MySQL5.5系のInnoDBかつREPEATABLE READなトランザクションは、以下の様なクエリを発行したときに他のトランザクションの影響を受けるかどうかという話です。

mysql> UPDATE tx_test SET num = num + 1 WHERE id = 4;

テーブル定義は以下の通りです。

mysql> desc tx_test;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   |     | NULL    |       |
| num   | int(11) | NO   |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

まず、SELECT文で見てみましょう。

トランザクションA トランザクションB
INSERT INTO tx_test VALUES (4,100);
BEGIN;
// BEGIN;
UPDATE tx_test SET num = num + 1 WHERE id = 4;
SELECT * FROM tx_test WHERE id = 4; //numは101
// SELECT * FROM tx_test WHERE id = 4; //numは100
COMMIT;
// SELECT * FROM tx_test WHERE id = 4; //numは100

こんな感じでトランザクションBはトランザクションAの影響を受けません。 同一行の値が他のトランザクションの影響を受けないので、ファジーリードは発生していないという状態です。

次にUPDATEを見てみましょう。同じレコードを使います。

トランザクションA トランザクションB
BEGIN;
// BEGIN;
UPDATE tx_test SET num = num + 1 WHERE id = 4;
SELECT * FROM tx_test WHERE id = 4; //numは102
// SELECT * FROM tx_test WHERE id = 4; //numは101
// UPDATE tx_test SET num = num + 1 WHERE id = 4; //ここでロック発生
COMMIT;
// SELECT * FROM tx_test WHERE id = 4; //numは103

トランザクションBに着目すると、numの値は101だったのに、UPDATE文のあとは103になっています。 ということで、他のトランザクションの影響を受けているということになります。

この点について、奥野さんの言葉を借りるとするなら、

何を隠そう、InnoDBのREPEATABLE READではその時の最新の値、つまり最も新しいバージョンが読み取られるという仕様になっている。だから他のトランザクションがロックしている行を参照しようとすると待たされるが、その後コミットされたホヤホヤの新しい値が得られるのである。(http://nippondanji.blogspot.jp/2013/12/innodbrepeatable-readlocking-read.html より)

ということなので、誤解したままだと値にズレが生じて危険な香りがするので、理解しておきましょうという話です。