GA

2020/08/20

MySQL 8.0 vs 外部キー制約 vs ALTER TABLEでメタデータロック待ちになったら疑うこと

TL;DR


論より証拠。
サンプルスキーマはこんなかんじ。

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 件のコメント :

コメントを投稿