BizStationブログ

ビズステーション株式会社の公式ブログです。

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)のスナップショットが使われ、それはほぼ最新バージョンです。

まとめ

InnoDBのMVCCとロックを理解すると、一見すると不思議に思えるこれらの動作も予想通りです。また、この動作(振る舞い)は仕方がないものかと思います。(判りづらいのは確かで、改善の余地はあるかとは思いますが。)

InnoDBのMVCCとロックの詳細は以下の記事で。