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

2024年10月17日 (木)

著者： nob

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

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

MySQL ファンの皆様おまたせしました！ MySQL および MySQL 互換 DB のロックに関して解説するシリーズ、最後となる第１２回の今回は PingCAP 社の TiDB について紹介します！

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

第１２回 TiDB と MySQL の違い解説動画

TiDB とは

TiDB は Google Spanner にインスパイアされ開発されたオープンソースの分散 SQL データベースです。

TiDB はトランザクション処理可能なキーバリューストア (KVS) の RocksDB をベースに、 Raft の合意形成によって多数のサーバーで動く分散 KVS として作られた TiKV をストレージレイヤーとして使い、その上で SQL レイヤーの TiDB が動作することで、分散 RDB を実現しています。

アプリケーションからは MySQL 互換の単一な巨大データベースとして使うことができるため国内でも採用する事例が増えており、今注目のデータベースと言えます。

無料の Serverless を活用

オープンソースで提供されている TiDB は公式・非公式で多様な形態での利用が可能です。

各サーバーをプロセスとして、ローカル環境に TiDB すべてのサーバーを簡単に構築して試せる tiup playground
（非公式で）playground を docker compose 環境で作れる ti-click/docker-tidb-playground
各ユーザー専用のクラウド VPC にマネージドサービスを展開する TiDB Dedicated https://pingcap.co.jp/tidb-dedicated/
一定量まで無料利用できるフルマネージドサービスの TiDB Serverless https://pingcap.co.jp/tidb-serverless-pricing-details

Serverless はストレージ容量 25GB、リクエストは 250 百万回まで無料で使えて検証や研究などの用途には十分な量があります。今回はこの Serverless を使ってみました！

TiDB Cloud のサイトで Start Free ボタンを押してサインアップします。

サインアップ後作成したクラスタを選択し

Connect ボタンを押すと接続情報を教えてくれる画面になります。

この画面で提示される接続情報は以下になり

接続先エンドポイント
自動生成された root 役割のユーザー名
そのユーザーのパスワードのリセット
--ssl-ca で指定する pem 形式の CA certification ファイル
ポートは 4000

付与された MySQL ユーザーのパスワードをリセットしたり、SSL で接続するための pem 形式の鍵ファイルをダウンロードしたりすることができます。これらを使って mysql クライアントで接続するコマンド例がこちら。

(例) mysql --ssl-ca=isrgrootx1.pem -h gateway01.eu-central-1.prod.aws.tidbcloud.com -P 4000 -uXXXXXXXXXXXX.root -pXXXXXXXXXX mydatabase

REPEATABLE READ のギャップロック

MySQL InnoDB REPEATABLE READ の特徴として、範囲に対してのロック読み取りでギャップロックを取得し、そのギャップへの INSERT を防止する機能があります。TiDB も REPEATABLE READ がデフォルトの分離レベルですが、ギャップロックの動作を検証してみます。

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

k = 1,10,100 の 3行が格納された t1 テーブルがあります。端末１でトランザクションを開始し、k > 10 の範囲で FOR UPDATE の SELECT による排他ロックを設置します。

[1]> BEGIN;
Query OK, 0 rows affected (0.80 sec)

[1]> SELECT * FROM t1 WHERE k > 10 FOR UPDATE;
+-----+------+
| k   | v    |
+-----+------+
| 100 |    0 |
+-----+------+
1 row in set (0.25 sec)

この状態で端末２でもトランザクションを開始し、k = 50 に INSERT してみます。

[2]> BEGIN;
Query OK, 0 rows affected (0.25 sec)

[2]> INSERT INTO t1 VALUES (50, 2);
Query OK, 1 row affected (0.25 sec)

INSERT 出来てしまいました！

TiDB の REPEATABLE READ も MySQL InnoDB と同じくファントムリードを防ぐ機能はあり、MySQL との互換性は高いですが、　残念ながらギャップロックは備わっていないため 範囲に対して FOR UPDATE のロックを設置しても隙間への INSERT を防ぐことが出来ません。

端末２つとも rollback でトランザクションを終了させ、端末１でトランザクションを開始し、k = 100 の行で LOCK IN SHARE MODE をかけ変更を禁止してみます。

[1]> BEGIN;
Query OK, 0 rows affected (0.63 sec)

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

端末２でオートコミットの１クエリで FOR UPDATE を実行してみます。

[2]> SELECT * FROM t1 WHERE k = 100 FOR UPDATE;
+-----+------+
| k   | v    |
+-----+------+
| 100 |    0 |
+-----+------+
1 row in set (0.25 sec)

ブロックされずに通ってしまいました！これは後述しますが、 オートコミットのクエリは自動的に後述する「楽観的トランザクションモード」で動く ため、通常の RDBMS と近い挙動のデフォルト「悲観的トランザクションモード」とは異なる動作になります。

あらためて端末２でも端末１と同様、明示的にトランザクションを開始し、FOR UPDATE を試してみます。

[2]> BEGIN;
Query OK, 0 rows affected (0.25 sec)

[2]> SELECT * FROM t1 WHERE k = 100 FOR UPDATE;

ブロックされました。インターネット経由のサーバーレス環境のためか、CTRL-C ではトランザクションがアボートされないことがあるため、端末１から kill コマンドでクエリは停止します。

[2]> SELECT * FROM t1 WHERE k = 100 FOR UPDATE;

ERROR 1105 (HY000): context canceled

共有ロックに対して排他ロックがブロックされるのは想定どおりです。次に同じ LOCK IN SHARE MODE も試します。

[2]> BEGIN;
Query OK, 0 rows affected (0.24 sec)

[2]> SELECT * FROM t1 WHERE k = 100 LOCK IN SHARE MODE;

ERROR 1105 (HY000): context canceled

FOR UPDATE と同様にブロックされました！ TiDB では LOCK IN SHARE MODE を存在する行に対して実行すると、FOR UPDATE と同じ排他ロックの働きとなるようです。

次に先程の FOR UPDATE と同じように k > 10 の範囲に LOCK IN SHARE MODE をかけてみましょう

[1]> BEGIN;
Query OK, 0 rows affected (0.24 sec)

[1]> SELECT * FROM t1 WHERE k > 10 LOCK IN SHARE MODE;
ERROR 1235 (42000): function LOCK IN SHARE MODE has only noop implementation in tidb now, use tidb_enable_noop_functions to enable these functions

範囲に対して LOCK IN SHARE MODE のロック読み取りを実行すると、効果の無いクエリとしてエラーになりました！ tidb_enable_noop_functions を有効にすればエラーにはならずスルーされるようにはなりますが、効果が無いのがわかる方がよいか、単純にクエリの修正が不要な方がよいかは運用に合わせてお選びいただければと思います！

悲観的トランザクションと楽観的トランザクション

TiDB は悲観的トランザクションと楽観的トランザクションという２つのモードがあり、デフォルトは tidb_txn_mode 変数で確認出来るように悲観的トランザクションモードで動いています。

[1]> SELECT @@tidb_txn_mode;
+-----------------+
| @@tidb_txn_mode |
+-----------------+
| pessimistic     |
+-----------------+
1 row in set (0.27 sec)

BEGIN に OPTIMISITC パラメーターを追加して端末１でトランザクションを楽観的モードで開始し、k = 1 の行を v = 1 にします。

[1]> BEGIN OPTIMISTIC;
Query OK, 0 rows affected (0.24 sec)

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

次に端末２でも k = 1 の行を v = 2 にします。

[2]> BEGIN OPTIMISTIC;
Query OK, 0 rows affected (0.24 sec)

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

両方のトランザクションが COMMIT していない状態では、ブロックされずに両方の変更が通った形になっています。ここで両方のトランザクションを COMMIT します。

[1]> COMMIT;
Query OK, 0 rows affected (0.25 sec)

[2]> COMMIT;
ERROR 9007 (HY000): Write conflict, txnStartTS=452493005972045832, conflictStartTS=452492983047553030, conflictCommitTS=452493040771661830, key=????, reason=Optimistic [try again later]

あとで COMMIT した端末２の方がエラーになりました。

MySQL や悲観的トランザクションモードでのデッドロック時と同じように、 楽観的トランザクションはロックを使用せず一方のトランザクションを失敗にすることで同時更新の衝突を解決します。

楽観的トランザクションでのデッドロック確認

楽観的トランザクションでの UPDATE におけるデッドロックはどうなるか見てみましょう。端末１は k = 1 、端末２は k = 100 でそれぞれ変更します。

[1]> BEGIN OPTIMISTIC;
Query OK, 0 rows affected (0.24 sec)

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

[2]> BEGIN OPTIMISTIC;
Query OK, 0 rows affected (0.24 sec)

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

次に互いの行に対して更新をかけてみます。

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

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

クエリ自体は両者デッドロックせず更新出来てしまいました！次に COMMIT すると

[1]> COMMIT;
Query OK, 0 rows affected (0.24 sec)

[2]> COMMIT;
ERROR 9007 (HY000): Write conflict, txnStartTS=452494708322336783, conflictStartTS=452494699409178625, conflictCommitTS=452494752952090629, key=????, reason=Optimistic [try again later]

２つ目のトランザクションはエラーになりロールバックされます。このように楽観的トランザクションモードでは、同時更新の衝突と同じく、デッドロックも同様に COMMIT のタイミングで一方の失敗として処理されます。

まとめ

TiDB の REPEATABLE READ は MySQL と同じくファントムリードも防ぐが、ギャップロックは無いため INSERT はブロック出来ない
デフォルトは悲観的トランザクションモードだが楽観的トランザクションモードもあり、オートコミットのクエリは常に楽観的トランザクションに
悲観的トランザクションモード
- 排他ロックの挙動、デッドロックの処理など MySQL と同じ挙動
- LOCK IN SHARE MODE の対象行が存在していると実行出来るが、その効果は FOR UPDATE と同じ排他 で動作する。
- 範囲に対しての LOCK IN SHARE MODE は無効果でエラーになる（オプションによってスルー化は可能）
楽観的トランザクションモード
- 更新やロック読み取りによるロック等、ロックは作用しない
- デッドロックや同時更新の衝突は COMMIT 時に一方のロールバックで処理される

分散 SQL データベースの TiDB の MySQL InnoDB とは異なるロックまわりの挙動について解説しました。

MySQL の挙動とは違ってはいる所はあるものの、ロック待ちが無い楽観的トランザクションモードは性能を考えると魅力的で、深く理解するとこで大規模データベースとしての性能をより発揮出来そうです。

１２回にわたり解説してきました MySQL のロック入門シリーズ、今回の TiDB の紹介で終了となります！解説しきれなかったパターンもまだまだありますが、ここまでの例で示したような調査方法を身に着けていただければ、皆さんも十分 MySQL に立ち向かっていけると思います！　ここまでご覧いただきありがとうございました！

弊社では MySQL を使ったサービス／システム構築に関心がある方の応募をお待ちしております！ご興味のある方は是非ご連絡ください！⇒ 採用情報