MySQL/MariaDB 知っておきたい REPEATABLE-READ の不思議
昨日のブログ記事のアクセスが想像以上に多く、とても驚いています。
この中で、REPEATABLE-READのトランザクションで、「ロックなし読み取りした値を元に更新してはいけない」と書いたのですが、具体的なサンプルを入れていなかったので、ここで紹介したいと思います。
REPEATABLE-READにならないトランザクションを試す
更新 (update)よりも、Insertの方がより驚きがありますので、Insertで示します。
まずは前準備です。MySQLのコマンドプロンプトを開いて、以下のSQLでデータベースとテーブルを用意します。
CREATE DATABASE test1; USE test1; CREATE TABLE `test1`.`test1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `field2` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB; INSERT INTO `test1`.`test1` (`field2`) VALUES (11); SET SESSION TRANSACTION ISOLATION LEVEL repeatable read; SELECT @@tx_isolation;
準備ができたら、トランザクションを開始BEGIN;
して、SELECT * FROM test1;
でINSERTの確認をします。
BEGIN; /* <--- Trx id = N */ SELECT * FROM test1; /* <-- Consistent nonlocking reads, */ +----+--------+ /* reads version N */ | id | field2 | +----+--------+ | 1 | 11 | +----+--------+
BEGIN;
でこのトランザクションのidが決まります。ここではNとします。SELECT * FROM test1;
はロックなしリードなのでConsistent nonlocking readsです。ここで読み取ったバージョンはNです。
ここで、別のクライアントからINSERT INTO `test1`(`field2`) VALUES (12);
を実行します。
/* execute following command FROM ANOTHER CONNECTION. */ USE test1; INSERT INTO `test1`(`field2`) VALUES (12); /* <--- Trx id = N+1 */ /* (auto transaction N+1) */
ここで実行したINSERTは別のトランザクション id=N+1 (自動トランザクション) で処理されます。
次に、元のクライアントに戻って、再びSELECT * FROM test1;
を見てみます。
SELECT * FROM test1; /* <-- Consistent nonlocking reads, */ +----+--------+ /* reads version N */ | id | field2 | +----+--------+ | 1 | 11 | +----+--------+ 1 row in set (0.02 sec)
ちゃんとさっきと同じ結果が表示されます。REPEATABLE-READですね。
では次に、LOCK IN SHARE MODEを加えて、SELECT * FROM test1 LOCK IN SHARE MODE;
を実行します。
SELECT * FROM test1 LOCK IN SHARE MODE; /* <-- Locking reads, */ +----+--------+ /* reads latest version */ | id | field2 | +----+--------+ | 1 | 11 | | 2 | 12 | +----+--------+
あれれ!!! 別のクライアントでINSERTしたものまで見えてしまっています。
ロックを要求すると、スナップショットはロックできないので、最新バージョンを読み取ってロックします。言い換えると、ロック要求は「最新をロックしてね」と言っているのと同義です。(SELECT * FROM test1 LOCK IN LATEST VERSION SHARE MODEってコマンドだったら判りやすいかも。ロックなしは、SELECT * FROM test1 READ OWN TRX VERSIONですね。)
更新処理(UPDATE,DELETE)は、MySQLが自動でロック付読み取りをします。なので「ロックなし読み取りした値を元に更新してはいけない」です。
面白いので、ここで、元のクライアントからINSERTしてみます。
INSERT INTO `test1`(`field2`) VALUES (13); /* <--- Trx id = N */
そしてやはりロックなしとロック付で読み取ってみます。
SELECT * FROM test1; /* <-- Consistent nonlocking reads, */ +----+--------+ /* reads version N */ | id | field2 | +----+--------+ | 1 | 11 | | 3 | 13 | +----+--------+ SELECT * FROM test1 LOCK IN SHARE MODE; /* <-- Locking reads, */ +----+--------+ /* reads latest version */ | id | field2 | +----+--------+ | 1 | 11 | | 2 | 12 | | 3 | 13 | +----+--------+
予想通りというか要求どおり、ロックなし読み取りはバージョンNのスナップショットを返し、ロック付は最新バージョンを返します。
(ロックなしはOWN TRX VERSIONを要求し、ロック付はLATEST VERSIONを要求)
COMMIT; SELECT * FROM test1; /* <--- Consistent nonlocking reads, */ +----+--------+ /* reads latest version */ | id | field2 | /* auto transaction trx id > N+1 */ +----+--------+ | 1 | 11 | | 2 | 12 | | 3 | 13 | +----+--------+
コミットしてからロックなし読み取りをすると、この読み取りにも(自動)トランザクションのidが割り振られて、N+1よりも大きい値になります。読み取りにはそのバージョン(N+1+x)のスナップショットが使われ、それはほぼ最新バージョンです。