インフィニットループ技術ブログ

2024年08月15日 (木)

著者： nob

MySQL インフラ #MySQL #インフラ #チューニング #データベース #技術記事

MySQL/Aurora/TiDBロック入門 – 第11回Aurora とMySQLの違い【解説動画付】

MySQL および MySQL 互換 DB のロックに関して解説するシリーズ、第１１回の今回はいよいよ AWS Aurora と MySQL の違いについて解説します。

★ 第１回トランザクション分離レベル
★ 第２回ロックモニターの読み方
★ 第３回ロック読取りも SELECT は止められない
★ 第４回 INSERT を止めるインテンションロック
★ 第５回 WHERE 条件と違うロック読取り
★ 第６回 performance_schema
★ 第７回ギャップロックと消えるロック
★ 第８回ネクストキーロックと降順インデックス
★ 第９回共有ロックでデッドロック
★ 第１０回ロック読み取りは READ COMMITTED
★ 第１１回 Aurora と MySQL の違い
★ 第１２回 TiDB と MySQL の違い

第11回 Aurora と MySQL の違い解説動画

前提となる Aurora の構成

Aurora は多様な構成や設定がありますが今回は「Aurora Serverless」、「Aurora Global Database」、「リードレプリカのローカル書き込み転送」などは使わずオーソドックスなライター (Writer) インスタンスとリードレプリカとも呼ばれるリーダー (Reader) インスタンスそれぞれ１台づつのクラスタを想定しました。

+--------+    replication     +--------+ 
| Writer +------------------->| Reader | 
+--------+                    +--------+ 
  - transaction                  - select
  - insert                               
  - update                               
  - select (locking read)                
      ^                            ^     
      |                            |     
      +--------------+-------------+     
                     |                   
              +------+------+            
              |  Proxy/App  |            
              +-------------+

パラメーターグループはデフォルトをベースに、performance_schema のみ有効に変更してあります。

ライター／リーダーでの READ COMMITTED 設定

今回はトランザクションやそのロックの挙動における MySQL との違いを明確化するため、まずは READ COMMITTED でどのような違いがあるか確認します。

Aurora はデフォルトの REPEATABLE READ のトランザクション分離レベルで使い、プロキシあるいはアプリケーションにてトランザクションを伴う更新はライターへ、SELECT だけのクエリをリーダーへと Read Write スプリット してアクセス先を使わけるのが通常の使い方ですが、今回は検証のためリーダーでもトランザクションを使ってその挙動を確認することにします。

Aurora や RDS では admin という準特権なユーザーが付与されますが、root は付与されません。その admin で設定可能な範囲も MySQL とは異なり、グローバルなトランザクション分離レベルは設定出来ません。

今回は WRITER1, READER の2端末を用意し、それぞれの MySQL クライアントでセッションレベルのトランザクション分離レベルで READ COMMITTED を設定します。

[ WRITER1 ]> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

Aurora のリードレプリカでは transaction_isolation の設定は無視され REPEATABLE READ で動作しますが、aurora_read_replica_read_committed を設定することでリーダーで READ COMMITTED 動作をさせることが可能です。

[ READER ]> SET SESSION aurora_read_replica_read_committed = ON;
[ READER ]> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

使うテーブルは k = 1,10,100 の３行が格納されている t1 テーブルです。

+-----+------+
| k   | v    |
+-----+------+
|   1 |    0 |
|  10 |    0 |
| 100 |    0 |
+-----+------+

リーダーでのトランザクションの制約

リーダーで更新をしてみます

[ READER ]> UPDATE t1 SET v = 2;
ERROR 1792 (25006): Cannot execute statement in a READ ONLY transaction.

リーダーでは Read Only のトランザクションとなり、UPDATE などを実行することは出来ません。トランザクションを開始して排他ロック読取りしてみます。

[ READER ]> START TRANSACTION;
Query OK, 0 rows affected (0.08 sec)

[ READER ]> SELECT * FROM t1 FOR UPDATE;
ERROR 1792 (25006): Cannot execute statement in a READ ONLY transaction.

トランザクションの中での FOR UPDATE による排他ロック読取りも実行できませんでした。LOCK IN SHARE MODE の共有ロックは実行出来ます。

[ READER ]> SELECT * FROM t1 LOCK IN SHARE MODE;
+-----+------+
| k   | v    |
+-----+------+
|   1 |    0 |
|  10 |    0 |
| 100 |    0 |
+-----+------+

ライター／リーダーでの READ COMMITED 動作

リーダーでトランザクションを開始、全行 SELECT します。

[ READER ]> START TRANSACTION;
Query OK, 0 rows affected (0.09 sec)

[ READER ]> SELECT * FROM t1;
+-----+------+
| k   | v    |
+-----+------+
|   1 |    0 |
|  10 |    0 |
| 100 |    0 |
+-----+------+
3 rows in set (0.10 sec)

ライターで v = 1 に UPDATE します。

[ WRITER1 ]> UPDATE t1 SET v = 1;
Query OK, 3 rows affected (0.08 sec)
Rows matched: 3  Changed: 3  Warnings: 0

リーダーで再度 SELECT します、これでファジーリードになるか確認出来ます。

[ READER ]> SELECT * FROM t1;
+-----+------+
| k   | v    |
+-----+------+
|   1 |    1 |
|  10 |    1 |
| 100 |    1 |
+-----+------+
3 rows in set (0.07 sec)

ファジリードが再現しました。次にライターで INSERT します。

[ WRITER1 ]> INSERT INTO t1 VALUES (200,0);
Query OK, 1 row affected (0.21 sec)

リーダーで SELECT すると行が出現していますので、ファントムリードも確認出来ました。

[ READER ]> SELECT * FROM t1;
+-----+------+
| k   | v    |
+-----+------+
|   1 |    1 |
|  10 |    1 |
| 100 |    1 |
| 200 |    0 |
+-----+------+

このように Aurora でも特殊な設定を採用することで基本的な READ COMMITED 動作が確認出来ましたが、Aurora の READ COMMITTED は大量データを処理し厳密性を要求されていない場合を想定した性能重視の設定で、同じクエリでもライターや普通の MySQL の READ COMMITED とは違った結果を返す場合があります。

詳しくは AWS の Aurora レプリカでの READ COMMITTED の動作の違いで詳細が解説されていますので本番環境で採用する際には事前に一読してください。

REPEATABLE READ でのロック読取り

次にトランザクション分離レベルの設定をデフォルトの REPEATABLE READ に戻してリーダーでトランザクションを開始し全行 SELECT します。

[ READER ]> START TRANSACTION;
Query OK, 0 rows affected (0.12 sec)

[ READER ]> SELECT * FROM t1;
+-----+------+
| k   | v    |
+-----+------+
|   1 |    1 |
|  10 |    1 |
| 100 |    1 |
| 200 |    0 |
+-----+------+
4 rows in set (0.57 sec)

ライターで k = 200 の行を v = 200 にします。

[ WRITER1 ]> UPDATE t1 SET v = 200 WHERE k = 200;
Query OK, 1 row affected (0.12 sec)
Rows matched: 1  Changed: 1  Warnings: 0

リーダーで再度全行読んでみます。

[ READER ]> SELECT * FROM t1;
+-----+------+
| k   | v    |
+-----+------+
|   1 |    1 |
|  10 |    1 |
| 100 |    1 |
| 200 |    0 |
+-----+------+
4 rows in set (0.10 sec)

200 に変わっていないためファージーリードが発生しない REPEATABLE READ が確認出来ました。次にライターで全行 v = 0 に変更します。

[ WRITER1 ]> UPDATE t1 SET v = 0;
Query OK, 4 rows affected (0.25 sec)
Rows matched: 4  Changed: 4  Warnings: 0

リーダーで k = 1 行を共有ロック読取りしてみます。

[ READER ]> SELECT * FROM t1 WHERE k = 1 LOCK IN SHARE MODE;
+---+------+
| k | v    |
+---+------+
| 1 |    0 |
+---+------+
1 row in set (0.13 sec)

REPEATABLE READ ですがロック読取りにより v = 0 の最新値が見えてしまっています。このまま全行 SELECT すると

[ READER ]> SELECT * FROM t1;
+-----+------+
| k   | v    |
+-----+------+
|   1 |    1 |
|  10 |    1 |
| 100 |    1 |
| 200 |    0 |
+-----+------+
4 rows in set (0.08 sec)

1 に戻りました。第10回ロック読み取りは READ COMMITTED で解説した MySQL 固有の挙動です。

performance_schema でロックを確認

この状態でリーダーは共有ロックしか持っていませんが、performance_schema の data_locks テーブルで確認してみます。

[ READER ]> SELECT * FROM performance_schema.data_locks\G
Empty set (0.13 sec)

残念ながら持っている筈のロックは直接確認することが出来ませんでした。ライターで同様にトランザクションを開始し、k = 1 で共有ロックを取得し performance_schema で確認してみます。

[ WRITER1 ]> START TRANSACTION;
Query OK, 0 rows affected (0.47 sec)

[ WRITER1 ]> SELECT * FROM t1 WHERE k = 1 LOCK IN SHARE MODE;
+---+------+
| k | v    |
+---+------+
| 1 |    0 |
+---+------+
1 row in set (0.17 sec)

[ WRITER1 ]> SELECT * FROM performance_schema.data_locks WHERE LOCK_TYPE = 'RECORD' \G
*************************** 1. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 23076404210640:21:4:2:23076435686432
ENGINE_TRANSACTION_ID: 304551380921296 <=== トランザクションID
            THREAD_ID: 706
             EVENT_ID: 29
        OBJECT_SCHEMA: learning_mysql
          OBJECT_NAME: t1
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 23076435686432
            LOCK_TYPE: RECORD
            LOCK_MODE: S,REC_NOT_GAP  <=== 共有レコードロック
          LOCK_STATUS: GRANTED
            LOCK_DATA: 1 <=== k = 1 行
1 row in set (0.16 sec)

トランザクションID が一時的な巨大な 64ビット値になっているなど MySQL InnoDB のときと全く同じ挙動で、S,REC_NOT_GAP の共有の単純なレコードロックを保持していることがわかります。

第11回まとめ

– Aurora ではクライアントで transaction_isolation をグローバル変更出来ない、セッションのみ、恒久的ならパラメーターグループで
– Aurora のリーダーでは更新および SELECT … FOR UPDATE は実行出来ない
– リーダーで transaction_isolation 設定は無視されるが、aurora_read_replica_read_committed 設定と組み合わせると READ COMMITTED を実現出来る
– ただしリーダーの READ COMMITTED は MySQL と挙動が異なるので Aurora レプリカでの READ COMMITTED の動作の違いは必ず参照
– デフォルト REPEATABLE READ での MySQL との互換性は高く、ロック読取りによる READ COMMITTED 的ファジーリードまで同じ