MySQL と互換 DB のロック入門、MySQL 編の最終回となる第10回の今回は、ロック読取りが READ COMMITTED と似た挙動となる件について解説します。
MySQL に限らず DB を使ってアプリケーションを作成する場合、「自分の処理が終わるまでは他のトランザクションにはデータを変更してほしくない」という状況が発生します。これを実現するには 第１回の記事 で解説したトランザクション分離レベルだけでは不十分で、必要な領域に共有や排他の指示が付いた SELECT によってロックをかける「ロック読取り」が必要となります。
MySQL のデフォルトは REPEATABLE READ の分離レベルで
ー ロック読取りや更新を範囲に対して指定すると、ギャップロックを含んだネクストキーロック
ー 対象の行がある場合は、READ COMMITTED で使われているのと同じ単純なレコードロック
ー 対象行が無い場合は、隙間へのギャップロックで他のトランザクションからの挿入をブロック
します。実戦ではこうしたロック読取りが必須となりますが、ロック読取りは分離レベルとの関係で重要なポイントがありますので解説します。
★ 第１回 トランザクション分離レベル
★ 第２回 ロックモニターの読み方
★ 第３回 ロック読取りも SELECT は止められない
★ 第４回 INSERT を止めるインテンションロック
★ 第５回 WHERE 条件と違うロック読取り
★ 第６回 performance_schema
★ 第７回ギャップロックと消えるロック
★ 第８回 ネクストキーロックと降順インデックス
★ 第９回 共有ロックでデッドロック
★ 第10回 ロック読み取りは READ COMMITTED
★ 第11回 Aurora と MySQL の違い
★ 第12回 TiDB と MySQL の違い
ロック読取りがファジーリードになる
READ COMMITTED において他のトランザクションでコミット済みの最新値がトランザクションの途中で見えてしまう現象があります。これはノンリピータブルリードまたはファジーリードと呼ばれます。第１回 で解説したように、MySQL デフォルトの REPEATABLE READ はこれを防いでくれます。
以下の t1 テーブルでトランザクションを開始し全行を SELECT します。
[1]> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
[1]> SELECT * FROM t1;
+-----+------+
| k | v |
+-----+------+
| 1 | 0 |
| 10 | 0 |
| 100 | 0 |
+-----+------+
3 rows in set (0.00 sec)
この状態で２つ目のトランザクションで全行を v = 2 に変更します。
[2]> UPDATE t1 SET v = 2;
Query OK, 3 rows affected (0.02 sec)
Rows matched: 3 Changed: 3 Warnings: 0
１つ目のトランザクションで k = 1 の行を排他ロックありで SELECT すると
[1]> SELECT * FROM t1 WHERE k = 1 FOR UPDATE;
+---+------+
| k | v |
+---+------+
| 1 | 2 | <== REPEATABLE READ でも新しい値！
+---+------+
1 row in set (0.01 sec)
REPEATABLE READ の分離レベルは他のトラザクションがコミットした最新の値をすぐ得てしまうという ノンリピータブルリード／ファジーリード は発生しないはずですが、見えてしまいました。
このように LOCK IN SHARE MODE や FOR UPDATE が明示されているロック読取りを使うと、REPEATABLE READ の分離レベルであっても READ COMMITTED 同様のファジーリードが発生します。
ギャップロックや挿入は？
第７回ギャップロックと消えるロック でも解説していますが、前述のようにロック読取りで READ COMMITTED 相当の挙動を見せたあとでも、その後の他のクエリは REPEATABLE READ の分離レベルとして想定どおりギャップロックが付与されます。
REPEATABLE READ / READ COMMITTED と違うところ
前述したようにロック読み取りは READ COMMITTED と同様のファジーリードになりますが、ではロック読み取り後、READ COMMITTED 相当の挙動になるのはどのあたりまでになるでしょうか？ いくつか試してみます。
最初の例と同じ条件で t1 テーブルでトランザクションを開始し全行 SELECT します。
[1]> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
[1]> SELECT * FROM t1;
+-----+------+
| k | v |
+-----+------+
| 1 | 0 |
| 10 | 0 |
| 100 | 0 |
+-----+------+
3 rows in set (0.00 sec)
その後２枚目の端末で k = 1 の行を v = 1 に更新します。
[2]> UPDATE t1 SET v = 1;
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3 Changed: 3 Warnings: 0
この状態で最初の端末で k = 1 行でロック読取りを実行すると
[1]> SELECT * FROM t1 WHERE k = 1 LOCK IN SHARE MODE;
+---+------+
| k | v |
+---+------+
| 1 | 1 |
+---+------+
1 row in set (0.00 sec)
[1]> SELECT * FROM t1;
+-----+------+
| k | v |
+-----+------+
| 1 | 0 |
| 10 | 0 |
| 100 | 0 |
+-----+------+
3 rows in set (0.00 sec)
このように ロック読み取りのクエリはコミットされている最新の値、通常読み取りのクエリは REPEATABLE READ 通りトランザクションで最初に読んだ値 となります。
この現象は値をリストするだけではなく、当然 SUM(col) などの集約であってもロック読み取りが指定されているかどうかで影響を受けます。このままの状態で実行すると
[1]> SELECT SUM(v) FROM t1 LOCK IN SHARE MODE;
+--------+
| SUM(v) |
+--------+
| 3 |
+--------+
1 row in set (0.00 sec)
[1]> SELECT SUM(v) FROM t1;
+--------+
| SUM(v) |
+--------+
| 0 |
+--------+
1 row in set (0.00 sec)
全行ロック読取りの SELECT で SUM したことで SUM(v) が 3 になるときと、0 のままの２種になっていることが確認できます。
こうして結果的に一つのトランザクション内でロック読取りと通常読取りで２種の値を恒常的に返すという REPEATABE READ / READ COMMITTED の純粋な定義とも異なる挙動となり、トランザクション内部での値の一貫性の維持の責務をアプリケーション側に委ねた状態となります。
第10回まとめ
ー LOCK IN SHARE MODE / FOR UPDATE が付いたロック読取りの値は REPEATABLE READ ではなく READ COMMITED 相当のファジーリードになる。
ー かつ通常読取りは REPEATABLE READ で動作し続けるため、ロック指示あるなしで「新旧２種の値を得る」という挙動になる。
いかがでしたでしょうか？ 今回お届けしたロック読取りの解説で一連の MySQL ロック入門シリーズの MySQL 編は終了となります。いよいよ次回第11回から AWS Aurora や PingCAP TiDB との挙動の違いを解説していきますのでご期待ください！
