TiDBとMySQLの違い：外部キー制約（とTiDBでのロック確認の仕方を少々）
この記事は、TiDB Advent Calendar 2023 18日目の記事です。
はじめに
TiDBはMySQLと互換性を持ったNewSQLデータベースということで、MySQLユーザーで気になっている方はいるんじゃないかと思います。
ただ、100%互換ではないこともあり、機能面の差分についてはDocsにまとまっていますが、サポートされている機能であってもMySQLと挙動が異なる部分もあったりします。
その中で今回はTiDBの外部キー制約について、MySQLとの挙動の違いにフォーカスして確認していきたいと思います。
具体的なところは以下のDocsを確認してください。
まずこれまでの経緯について。TiDBは外部キー制約が未サポートでしたが、v6.6よりサポートされました。
TiDBユーザー界隈からは「外部キー制約がサポートされたーー！！」という声を聞く一方で、いざ使ってみたらパフォーマンス観点で「およよ？」となった（ロック動作の違いにより）という話しも何度か耳にしたので、その点について見ていきます。
TiDBで外部キー制約を使うのはやめよう！ということが言いたいのではなく、（慣れ親しんでいるであろう）MySQLとは挙動が異なる部分があるから、それを理解したうえで使いましょうということを意図した記事です。
外部キー制約：MySQLとTiDBの違い
結論から言うと、
TiDBで外部キー制約を使う場合、現状はLOCK IN SHARE MODEが未サポートのため、以下のようなパフォーマンス上の問題となり得ることがあります。
※TiDBのDocsにおいても外部キー制約のロックの段で触れられている内容となります。
具体的には、TiDBで以下のような親であるparentテーブルのid列と、子であるchildテーブルで親を示すpid列（外部キー）とが関連しているような場合で考えてみます。
このとき、親テーブルのレコードと親子関係にあるレコードを子テーブルに"大量に書き込み"（Insert,Update,Delete）があるようなワークロードでは、親レコードのロック獲得待ちで子テーブルへの書き込みのパフォーマンスが出ないことがあります。
以下のような物理的なイメージに置き換えてみます。
左側のように異なる親それぞれにひとりの子が同時に追加される（子テーブルに書き込まれる）のは問題ないですが、
右側のように同じ親に多数の子が同時に追加されるのはTiDBでは子の追加時に親レコードのロック獲得待ちになり性能影響を受ける、となります。
伝わりますかね？（笑）
ここからは、具体的な挙動について確認していきます。
MySQLとTiDBの環境準備
MySQLとTiDB、それぞれローカル環境で試していきます。
環境：MySQL
以下のバージョンでMySQLの動作を確認します。
% mysql --version
mysql Ver 8.0.32 for macos13.0 on arm64 (Homebrew)
環境：TiDB
TiDB（ローカル ※tiup playground）も準備します。
執筆時点でちょうどTiDB 7.5(LTS)がGAとなりましたのでそちらを使います。
※tiup playgroundの使い方についてはこちらの記事が参考になります。
% tiup playground v7.5.0
準備：MySQL/TiDB共通
双方ともに分離レベルはデフォルトのRepeatable Readで確認します。
TiDBではRepeatable Readの他にRead Commitedがサポートされています。
※厳密に言うとTiDBはSnapshot Isolationになります。詳しくは以下を参考にしてください。
mysql> SELECT @@GLOBAL.transaction_isolation, @@transaction_isolation;
+--------------------------------+-------------------------+
| @@GLOBAL.transaction_isolation | @@transaction_isolation |
+--------------------------------+-------------------------+
| REPEATABLE-READ | REPEATABLE-READ |
+--------------------------------+-------------------------+
1 row in set (0.00 sec)
本題です。双方で以下のようなテーブルを作成します。
親テーブルparent
CREATE TABLE parent (
id BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(64) NOT NULL);
子テーブルchild
※ON DELETE CASCADEをReferenceOptionで指定していますが、このあと確認する挙動はこれに依存しません。（なくても同様の挙動です）
CREATE TABLE child (
id BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT,
pid BIGINT NOT NULL,
name VARCHAR(64) NOT NULL,
INDEX idx_pid (pid),
FOREIGN KEY (pid) REFERENCES parent(id) ON DELETE CASCADE);
確認。
mysql> DESC parent;
+-------+-------------+------+------+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+------+---------+----------------+
| id | bigint(20) | NO | PRI | NULL | auto_increment |
| name | varchar(64) | NO | | NULL | |
+-------+-------------+------+------+---------+----------------+
2 rows in set (0.00 sec)
mysql> DESC child;
+-------+-------------+------+------+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+------+---------+----------------+
| id | bigint(20) | NO | PRI | NULL | auto_increment |
| pid | bigint(20) | NO | MUL | NULL | |
| name | varchar(64) | NO | | NULL | |
+-------+-------------+------+------+---------+----------------+
3 rows in set (0.00 sec)
続いて、親テーブルparantにレコードを登録しておきます。
INSERT INTO parent (name) VALUES ("やまだ ちちろう");
ここから、MySQL、TiDB双方で上記レコードと関連のあるレコードを子テーブルにINSERTし、その時の挙動を確認します。
では早速見ていきましょう。
動作確認：MySQL
まずは子テーブルへデータ（pid = 1のデータ）を挿入します。※COMMITはまだしません
# Transaction 1：子テーブルへの挿入1
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO child (pid, name) VALUES (1, "やまだ いちろう");
Query OK, 1 row affected (0.01 sec)
# COMMITはまだしません
続いて、別のトランザクションで同じようなレコードを子テーブルに挿入します。
# Transaction 2：子テーブルへの挿入2
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO child (pid, name) VALUES (1, "やまだ じろう");
Query OK, 1 row affected (0.00 sec)
２つ目のトランザクションのINSERTも完了しています。テーブルやレコードのロック状態を確認します。
# Transaction 3：ロック状態を確認
# trx_idを確認
mysql> SELECT * FROM information_schema.INNODB_TRX ORDER BY trx_id;
+--------+-----------+---------------------+-----------------------+------------------+------------+---------------------+-----------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+---------------------+
| trx_id | trx_state | trx_started | trx_requested_lock_id | trx_wait_started | trx_weight | trx_mysql_thread_id | trx_query | trx_operation_state | trx_tables_in_use | trx_tables_locked | trx_lock_structs | trx_lock_memory_bytes | trx_rows_locked | trx_rows_modified | trx_concurrency_tickets | trx_isolation_level | trx_unique_checks | trx_foreign_key_checks | trx_last_foreign_key_error | trx_adaptive_hash_latched | trx_adaptive_hash_timeout | trx_is_read_only | trx_autocommit_non_locking | trx_schedule_weight |
+--------+-----------+---------------------+-----------------------+------------------+------------+---------------------+-----------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+---------------------+
| 6669 | RUNNING | 2023-12-13 15:39:49 | NULL | NULL | 4 | 11 | NULL | NULL | 0 | 2 | 3 | 1128 | 1 | 1 | 0 | REPEATABLE READ | 1 | 1 | NULL | 0 | 0 | 0 | 0 | NULL |
| 6670 | RUNNING | 2023-12-13 15:40:04 | NULL | NULL | 4 | 13 | NULL | NULL | 0 | 2 | 3 | 1128 | 1 | 1 | 0 | REPEATABLE READ | 1 | 1 | NULL | 0 | 0 | 0 | 0 | NULL |
+--------+-----------+---------------------+-----------------------+------------------+------------+---------------------+-----------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+---------------------+
2 rows in set (0.00 sec)
# ロック状態を確認
mysql> SELECT * FROM performance_schema.data_locks ORDER BY ENGINE_TRANSACTION_ID;
+--------+-----------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
| ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+--------+-----------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
| INNODB | 5031496832:1163:4420838832 | 6669 | 52 | 22 | test | parent | NULL | NULL | NULL | 4420838832 | TABLE | IS | GRANTED | NULL |
| INNODB | 5031496832:1164:4420838744 | 6669 | 52 | 22 | test | child | NULL | NULL | NULL | 4420838744 | TABLE | IX | GRANTED | NULL |
| INNODB | 5031496832:3:4:2:4848876568 | 6669 | 52 | 22 | test | parent | NULL | NULL | PRIMARY | 4848876568 | RECORD | S,REC_NOT_GAP | GRANTED | 1 |
| INNODB | 5031497624:1163:4420839856 | 6670 | 54 | 14 | test | parent | NULL | NULL | NULL | 4420839856 | TABLE | IS | GRANTED | NULL |
| INNODB | 5031497624:1164:4420839768 | 6670 | 54 | 14 | test | child | NULL | NULL | NULL | 4420839768 | TABLE | IX | GRANTED | NULL |
| INNODB | 5031497624:3:4:2:4848881176 | 6670 | 54 | 14 | test | parent | NULL | NULL | PRIMARY | 4848881176 | RECORD | S,REC_NOT_GAP | GRANTED | 1 |
+--------+-----------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
6 rows in set (0.00 sec)
ここからわかることとして、各トランザクションでparentテーブルのレコードに対して行（LOCK_TYPE = RECORD)に対する共有ロック（LOCK_MODE = S,REC_NOT_GAP）を取っています。
したがって、子テーブルのINSERT時に親テーブルを参照可能となり、無事INSERTが出来ることになります。
ちなみに（当たり前ですが）、このタイミングで他のトランザクションから親テーブルのレコードを更新することは出来ません。
# Transaction 3：親テーブルのレコード更新を試みる
mysql> UPDATE parent SET name = "やまだ はは" WHERE id = 1;
# しばらくすると以下が出る
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
一応ロック状況を確認。
mysql> SELECT * FROM performance_schema.data_locks ORDER BY ENGINE_TRANSACTION_ID;
+--------+-----------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
| ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+--------+-----------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
| INNODB | 5031496832:1163:4420838832 | 6669 | 52 | 22 | test | parent | NULL | NULL | NULL | 4420838832 | TABLE | IS | GRANTED | NULL |
| INNODB | 5031496832:1164:4420838744 | 6669 | 52 | 22 | test | child | NULL | NULL | NULL | 4420838744 | TABLE | IX | GRANTED | NULL |
| INNODB | 5031496832:3:4:2:4848876568 | 6669 | 52 | 22 | test | parent | NULL | NULL | PRIMARY | 4848876568 | RECORD | S,REC_NOT_GAP | GRANTED | 1 |
| INNODB | 5031497624:1163:4420839856 | 6670 | 54 | 14 | test | parent | NULL | NULL | NULL | 4420839856 | TABLE | IS | GRANTED | NULL |
| INNODB | 5031497624:1164:4420839768 | 6670 | 54 | 14 | test | child | NULL | NULL | NULL | 4420839768 | TABLE | IX | GRANTED | NULL |
| INNODB | 5031497624:3:4:2:4848881176 | 6670 | 54 | 14 | test | parent | NULL | NULL | PRIMARY | 4848881176 | RECORD | S,REC_NOT_GAP | GRANTED | 1 |
| INNODB | 5031498416:1163:4420840792 | 6675 | 55 | 13 | test | parent | NULL | NULL | NULL | 4420840792 | TABLE | IX | GRANTED | NULL |
| INNODB | 5031498416:3:4:2:4848885784 | 6675 | 55 | 13 | test | parent | NULL | NULL | PRIMARY | 4848885784 | RECORD | X,REC_NOT_GAP | WAITING | 1 |
+--------+-----------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
8 rows in set (0.00 sec)
ざっとこのような動きになります。
動作確認：TiDB
続いてTiDBでの動作をMySQLと同様の流れで確認していきます。
# Transaction 1：子テーブルへの挿入
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO child (pid, name) VALUES (1, "やまだ いちろう");
Query OK, 1 row affected (0.00 sec)
# COMMITはまだしません
現在のトランザクションの情報を確認します。
mysql> SELECT * FROM information_schema.cluster_tidb_trx where db ="test" ORDER BY ID\G
*************************** 1. row ***************************
INSTANCE: 127.0.0.1:10080
ID: 446289819744337921
START_TIME: 2023-12-13 18:42:35.055000
CURRENT_SQL_DIGEST: NULL
CURRENT_SQL_DIGEST_TEXT: NULL
STATE: Idle
WAITING_START_TIME: NULL
MEM_BUFFER_KEYS: 3
MEM_BUFFER_BYTES: 0
SESSION_ID: 2579496972
USER: root
DB: test
ALL_SQL_DIGESTS: ["e6f07d43b5c21db0fbb9a31feac2dc599787763393dd5acbfad80e247eb02ad5","389fffa62c6e76b27c5cd2178ab2045c6d2302e5ab39314e91f60a2369beed0f"]
RELATED_TABLE_IDS: 104
WAITING_TIME: NULL
1 row in set (0.00 sec)
１つ目のトランザクションID: 446289819744337921は、STATE: Idle（クエリの実行待ち）状態であることが分かります。
続いて、別のトランザクションで同じようなレコードを子テーブルに挿入します。
# Transaction 2：子テーブルへの挿入2
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO child (pid, name) VALUES (1, "やまだ じろう");
2つ目のトランザクションは書き込みが完了せず（待ってるっぽい）、MySQLと挙動が異なっていることが分かります。
現在実行されているトランザクションに関する情報を再度確認します。
mysql> SELECT * FROM information_schema.cluster_tidb_trx where db ="test" ORDER BY ID\G
*************************** 1. row ***************************
INSTANCE: 127.0.0.1:10080
ID: 446289819744337921
START_TIME: 2023-12-13 18:42:35.055000
CURRENT_SQL_DIGEST: NULL
CURRENT_SQL_DIGEST_TEXT: NULL
STATE: Idle
WAITING_START_TIME: NULL
MEM_BUFFER_KEYS: 3
MEM_BUFFER_BYTES: 0
SESSION_ID: 2579496972
USER: root
DB: test
ALL_SQL_DIGESTS: ["e6f07d43b5c21db0fbb9a31feac2dc599787763393dd5acbfad80e247eb02ad5","389fffa62c6e76b27c5cd2178ab2045c6d2302e5ab39314e91f60a2369beed0f"]
RELATED_TABLE_IDS: 104
WAITING_TIME: NULL
*************************** 2. row ***************************
INSTANCE: 127.0.0.1:10080
ID: 446289914652262401
START_TIME: 2023-12-13 18:48:37.100000
CURRENT_SQL_DIGEST: 389fffa62c6e76b27c5cd2178ab2045c6d2302e5ab39314e91f60a2369beed0f
CURRENT_SQL_DIGEST_TEXT: insert into `child` ( `pid` , `name` ) values ( ... )
STATE: LockWaiting
WAITING_START_TIME: 2023-12-13 18:48:39.808320
MEM_BUFFER_KEYS: 0
MEM_BUFFER_BYTES: 0
SESSION_ID: 2579496974
USER: root
DB: test
ALL_SQL_DIGESTS: ["e6f07d43b5c21db0fbb9a31feac2dc599787763393dd5acbfad80e247eb02ad5","389fffa62c6e76b27c5cd2178ab2045c6d2302e5ab39314e91f60a2369beed0f"]
RELATED_TABLE_IDS: 104
WAITING_TIME: 3.942498334
2 rows in set (0.01 sec)
２つ目のトランザクションであるID: 446289914652262401の情報が追加され、STATE: LockWaitingの状態であることが分かります。
ロック状態を確認するテーブルであるinformation_schema.data_lock_waitsを確認してみます。
# Transaction 3：ロック状態を確認
mysql> SELECT * FROM information_schema.data_lock_waits\G
*************************** 1. row ***************************
KEY: 7480000000000000665F728000000000000001
KEY_INFO: {"db_name":"test","table_name":"parent","handle_type":"int","handle_value":"1","db_id":2,"table_id":102}
TRX_ID: 446289914652262401
CURRENT_HOLDING_TRX_ID: 446289819744337921
SQL_DIGEST: 389fffa62c6e76b27c5cd2178ab2045c6d2302e5ab39314e91f60a2369beed0f
SQL_DIGEST_TEXT: insert into `child` ( `pid` , `name` ) values ( ... )
*************************** 2. row ***************************
KEY: 7480000000000000665F728000000000000001
KEY_INFO: {"db_name":"test","table_name":"parent","handle_type":"int","handle_value":"1","db_id":2,"table_id":102}
TRX_ID: 446289914652262401
CURRENT_HOLDING_TRX_ID: 446289819744337921
SQL_DIGEST: NULL
SQL_DIGEST_TEXT: NULL
2 rows in set (0.00 sec)
この１レコード目の結果が示す意味についてですが、２つ目のトランザクションであるTRX_ID: 446289914652262401がロックを取得しようとしているが、
1つ目のトランザクションCURRENT_HOLDING_TRX_ID: 446289819744337921がKEY_INFOに示すようにparentテーブルのid = 1のキーをロックしていることから、ロック獲得待ちをしている状態を表しています。
上記タイミングでMySQL同様に親レコードの変更を試みると、当然ですがこちらも以下のようにロック獲得待ちの状態になります。
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> UPDATE parent SET name = "やまだ はは" WHERE id = 1;
# しばらくすると以下のエラーが出る
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
当然、１つ目のトランザクションをCOMMITしたら２つ目のトランザクションのINSERTも完了し、、、、という挙動になっていきます。
以上から分かるように、外部キー制約下において整合性を担保するうえでの表向きの振る舞いはTiDBはMySQLと同様ですが、
それを実現するうえでのロックの仕組みが異なることから、当該ケースのように親テーブルの特定のレコードに関連するデータを子テーブルに大量に書き込もうとすると、TiDBは性能面のビハインドが生じる、ということになります。
Docs上ではこの振る舞いの違いはTiDBがLOCK IN SHARE MODEが未サポートだからと記載がありますが、
本質的には上書き型ゆえロックを取ることで整合性担保を試みるMySQLと、
追記型ゆえSnapshot Isolationで整合性担保を試みるTiDBとの違いから来ているように思います。
TiDBでの対策
上で示した例もそうですが、TiDBでの外部キー制約の利用は実ワークロードに沿ってよく検証してから利用するほうが現時点においては良さそうです。
※Docsにもその旨の記載があります。
ただ、検討した結果、外部キー制約そのものは利用したい（MySQLからTiDBへ移行するため改修するのは嫌だから利用したい、など）場合、以下も検討することをおすすめします。
- System Variablesの
foreign_key_checksを無効化する（デフォルトは有効化されています）
https://docs.pingcap.com/tidb/stable/system-variables#foreign_key_checks
このパラメータをOFFにした場合、いわゆる外部キー制約によるチェック処理をスキップすることにより、ボトルネックは解消されます。
このパラメータのスコープはGLOBAL/SESSIONが指定可能です。
例えば、データ移行時に子テーブルにレコードを大量挿入する場合なども、このパラメータ利用を検討して良いかもしれません。
スキップされる具体的な動作についての詳細は以下の項をご参照ください。
(独り言：PostgreSQLのVALIDATE CONSTRAINT的なのがサポートされるとイイのかな)
試してみます。
まずは値をチェック。
mysql> SELECT @@GLOBAL.foreign_key_checks, @@foreign_key_checks;
+-----------------------------+----------------------+
| @@GLOBAL.foreign_key_checks | @@foreign_key_checks |
+-----------------------------+----------------------+
| 1 | 1 |
+-----------------------------+----------------------+
1 row in set (0.00 sec)
設定を変更。（この例では参考のためGLOBAL/SESSIONともに無効化してます）
mysql> SET @@GLOBAL.foreign_key_checks = 0, @@foreign_key_checks = 0;
Query OK, 0 rows affected (0.01 sec)
mysql> SELECT @@GLOBAL.foreign_key_checks, @@foreign_key_checks;
+-----------------------------+----------------------+
| @@GLOBAL.foreign_key_checks | @@foreign_key_checks |
+-----------------------------+----------------------+
| 0 | 0 |
+-----------------------------+----------------------+
1 row in set (0.00 sec)
新規セッションにて、同様の確認をします。
# Transaction 1：子テーブルへの挿入
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO child (pid, name) VALUES (1, "やまだ いちろう");
Query OK, 1 row affected (0.00 sec)
# COMMITはまだしません
さらに別のセッションを開いて確認します。
# Transaction 2：子テーブルへの挿入2
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO child (pid, name) VALUES (1, "やまだ じろう");
Query OK, 1 row affected (0.00 sec)
今度は2つ目のセッションでもロック獲得待ちにならずINSERT自体は完了していることが分かります。
補足：TiDBでロックの状態を確認する方法
「動作確認：TiDB」
１．CLUSTER_TIDB_TRX
TiDB ノード上で現在実行されているトランザクションに関する情報が表示されます。
SELECT * FROM information_schema.cluster_tidb_trx where db = <database_name> ORDER BY ID\G
２．DATA_LOCK_WAITS
クラスター内のすべての TiKV ノードに関する進行中のロック待機情報を表示します。
SELECT * FROM information_schema.data_lock_waits\G
以下の資料でも言及されています。（PingCAPによるウェビナー資料）
Discussion