TL;DR
- MySQL 8.0(細かくは8.0.4っぽい)とそれ以降は「外部キー制約を持っているテーブルにSELECTするとそのテーブルの親テーブルにもメタデータロック(MDL)を置くようになった」
- MDLであるがゆえに foreign_key_checks をOFFにしようが 無効化はできない
- MySQL :: WL#6049: Meta-data locking for FOREIGN KEY tables
- WL#6049 “Meta-data locking for FOREIGN KEY tables” and WL#11059 · mysql/mysql-server@6626f76
- これ以降にもいくつかコミットが続いている
論より証拠。
サンプルスキーマはこんなかんじ。
CREATE TABLE `item` (
`item_id` int NOT NULL,
`registered` datetime NOT NULL,
PRIMARY KEY (`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE `user` (
`user_id` int NOT NULL,
`registered` datetime NOT NULL,
PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE `item_box` (
`user_id` int NOT NULL,
`item_id` int NOT NULL,
`item_amount` int NOT NULL,
`last_updated` datetime NOT NULL,
PRIMARY KEY (`user_id`,`item_id`),
KEY `idx_itemid` (`item_id`),
CONSTRAINT `item_box_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`),
CONSTRAINT `item_box_ibfk_2` FOREIGN KEY (`item_id`) REFERENCES `item` (`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE `item_log` (
`item_log_seq` bigint unsigned NOT NULL,
`user_id` int NOT NULL,
`item_id` int NOT NULL,
`changed_amount` int NOT NULL,
`recorded` datetime NOT NULL,
PRIMARY KEY (`item_log_seq`),
KEY `idx_userid` (`user_id`),
KEY `idx_itemid` (`item_id`),
CONSTRAINT `item_log_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `item_box` (`user_id`),
CONSTRAINT `item_log_ibfk_2` FOREIGN KEY (`item_id`) REFERENCES `item_box` (`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
item, user テーブルがあって、 item_box がそれぞれに外部キー制約を持ち、 item_box の履歴用に item_log テーブルがある、みたいなテーブル。
2段外部キー制約を張って試したかったので、 item_log の親は item_box になっている（本筋とは関係ないけど、こういうアレにするということは item_box には残数がゼロになったアイテムもDELETEされずに行としては残す感じになるね）
この時、
item,userテーブルに対するDDLは自分自身に対するMDLだけを欲しがる。これは従前からの動作といっしょ
mysql80 31> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql80 31> SELECT * FROM item; -- 行ロックは取らないけど共有MDLは取る
Empty set (0.00 sec)
mysql80 34> SELECT * FROM performance_schema.metadata_locks\G
..
*************************** 2. row ***************************
OBJECT_TYPE: TABLE
OBJECT_SCHEMA: d1
OBJECT_NAME: item
COLUMN_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140136396228576
LOCK_TYPE: SHARED_READ
LOCK_DURATION: TRANSACTION
LOCK_STATUS: GRANTED
SOURCE: sql_parse.cc:6161
OWNER_THREAD_ID: 66
OWNER_EVENT_ID: 5
2 rows in set (0.00 sec)
mysql80 34> SET SESSION lock_wait_timeout = 1;
Query OK, 0 rows affected (0.00 sec)
mysql80 34> ALTER TABLE item Engine = InnoDB; -- MDL待ちがタイムアウト
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
item_boxは自分自身とその親であるitem,userに対してMDLを置こうとする。
mysql80 31> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql80 31> SELECT * FROM item_box;
Empty set (0.00 sec)
mysql80 34> ALTER TABLE item_box Engine = InnoDB;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql80 34> ALTER TABLE item Engine = InnoDB;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql80 34> ALTER TABLE user Engine = InnoDB;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql80 34> ALTER TABLE item_log Engine = InnoDB; -- 子であるitem_logには関係ない
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
item_logは自信と親であるitem_boxには共有MDLを置くが、祖父母の代(?)であるitem,userには置かない
mysql80 31> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql80 31> SELECT * FROM item_log;
Empty set (0.00 sec)
mysql80 34> ALTER TABLE item_log Engine = InnoDB; -- 自分自身
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql80 34> ALTER TABLE item_box Engine = InnoDB; -- 親テーブル
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql80 34> ALTER TABLE item Engine = InnoDB; -- 祖父母世代(?)
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql80 34> ALTER TABLE user Engine = InnoDB; -- 祖父母世代(?)
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
実際食らったのは単なる親子関係のテーブルだけど、「あんまり更新されないsbtest1にインデックス追加したい」(都道府県マスタ的な役割だと思いねぇ)「子テーブルのsbtest2はバンバン参照される」みたいな時にハマった。
mysql80 42> ALTER TABLE sbtest.sbtest1 Engine = InnoDB; -- MDL待ちでブロックされなければこれくらいかかるALTER TABLEがあるじゃろ？
Query OK, 0 rows affected (8.96 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql80 42> ALTER TABLE sbtest.sbtest1 Engine = InnoDB; -- ALTER TABLEを開始してから
mysql80 31> BEGIN; SELECT * FROM sbtest.sbtest2 LIMIT 1; -- 他のコネクションで共有MDLを置くじゃろ？
Query OK, 0 rows affected (0.00 sec)
+----+--------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| id | k | c | pad |
+----+--------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| 1 | 499284 | 83868641912-28773972837-60736120486-75162659906-27563526494-20381887404-41576422241-93426793964-56405065102-33518432330 | 67847967377-48000963322-62604785301-91415491898-96926520291 |
+----+--------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
1 row in set (0.00 sec)
mysql80 31> SHOW PROCESSLIST; -- State: altering table でフツーに進むんだけど
+----+-----------------+-----------------+--------+---------+------+------------------------+--------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------------+--------+---------+------+------------------------+--------------------------------------------+
| 42 | root | localhost | NULL | Query | 7 | altering table | ALTER TABLE sbtest.sbtest1 Engine = InnoDB |
+----+-----------------+-----------------+--------+---------+------+------------------------+--------------------------------------------+
mysql80 31> SHOW PROCESSLIST; -- しばらくするとMDL待ちで詰まる
+----+-----------------+-----------------+--------+---------+------+---------------------------------+--------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------------+--------+---------+------+---------------------------------+--------------------------------------------+
| 42 | root | localhost | NULL | Query | 13 | Waiting for table metadata lock | ALTER TABLE sbtest.sbtest1 Engine = InnoDB |
+----+-----------------+-----------------+--------+---------+------+---------------------------------+--------------------------------------------+
mysql80 31> COMMIT; -- COMMITして共有MDLをリリースしたタイミングで
Query OK, 0 rows affected (0.00 sec)
mysql80 42> ALTER TABLE sbtest.sbtest1 Engine = InnoDB; -- MDL取れたのでALTER TABLEが終われる
Query OK, 0 rows affected (2 min 23.94 sec)
Records: 0 Duplicates: 0 Warnings: 0
オンラインALTER TABLEは終了時にMDLを取るのでこんな風になった。
更に、sbtest1へのALTER TABLEが排他MDL待ちになってしまうので、sbtest1への全てのDMLもMDL待ちのキューを追い越せなくて共有MDLが取れなくなって待たされる。
もしもっと早くこれを知っていたら、「sbtest2を掴んでるトランザクションをKILLすればいい」と、ALTER TABLEを完走させることができたかもしれない。
俺基本的に「ヘビーなMySQLでもちゃんと正規化してれば外部キー制約は張っても大丈夫」って主張してたけど、これを食らってから危なっかしいかなと思うようになりました。
ちなみに回避策はレプリカにALTER TABLEしてからマスター切り替え。これで取り敢えず問題は出ていない。
0 件のコメント :
コメントを投稿