MySQL database gets stuck while adding a foreign key during frequent write activity

[Tayler Sokalski]

I am encountering a strange issue wherein MySQL 8.4 appears to get stuck when adding a new foreign key to a table. I've found a workaround where I can kill some INSERT statements to unstick things, but I don't believe the database should be getting stuck in the first place.


The schema of the impacted database has a bunch of tables; 3 of them are relevant to the issue in question. For the sake of example, let's imagine the database is tracking health information, and let's call these tables `person`, `heart_rate`, and `health_target` (this example is entirely made up, but the same impact applies). The `health_target` table does not yet exist in the database; the new table definition contains a foreign key pointing to the `id` column on the `person` table. The existing `heart_rate` table also has a foreign key pointing to the `id` column on the `person` table, and receives frequent writes, as it is tracking heart rate values over time.


Adding the new `health_target` table without the foreign key works successfully as follows:

mysql> CREATE TABLE `health_target` (`id` varchar(36) NOT NULL, `person_id` varchar(36) NOT NULL, `target_data` json NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.06 sec)


Trying to add the foreign key as below causes the database to lock up completely:

> ALTER TABLE health_target ADD CONSTRAINT fk_health_target_person_id FOREIGN KEY (person_id) REFERENCES person(id);
(never completes)


Examining the contents of `information_schema.processlist` after executing the ALTER TABLE statement, I can see that the ALTER TABLE is stuck in a "Waiting for table metadata lock" state, as are a great number of INSERT and SELECT statements that reference the `person` table (which is a lot of statements, since this table has many references and the system is pretty busy servicing requests).

mysql> SELECT * FROM information_schema.processlist WHERE INFO IS NOT NULL ORDER BY time DESC;
+-------+------+--------+------+---------+-------+---------------------------------+-------------------------------------------------------------------------------------------------------------------+
| ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO |
+-------+------+--------+------+---------+-------+---------------------------------+-------------------------------------------------------------------------------------------------------------------+
| 29362 | root | <HOST> | NULL | Query | 99 | Waiting for table metadata lock | ALTER TABLE health_target ADD CONSTRAINT fk_health_target_person_id FOREIGN KEY (person_id) REFERENCES person(id) |
| 28866 | root | <HOST> | NULL | Query | 89 | Waiting for table metadata lock | SELECT person.id, person.name, ... |
| 29590 | root | <HOST> | NULL | Query | 89 | Waiting for table metadata lock | INSERT INTO heart_rate (id, person_id, timestamp, value) VALUES ... |
| 29295 | root | <HOST> | NULL | Query | 88 | Waiting for table metadata lock | SELECT person.id, person.name, ... |
| 29713 | root | <HOST> | NULL | Query | 86 | Waiting for table metadata lock | SELECT person.id, person.name, ... |
| 29571 | root | <HOST> | NULL | Query | 84 | Waiting for table metadata lock | INSERT INTO heart_rate (id, person_id, timestamp, value) VALUES ... |
| 29625 | root | <HOST> | NULL | Query | 84 | Waiting for table metadata lock | INSERT INTO heart_rate (id, person_id, timestamp, value) VALUES ... |
| 29745 | root | <HOST> | NULL | Query | 81 | Waiting for table metadata lock | SELECT person.id, person.name, ... |
+-------+------+--------+------+---------+-------+---------------------------------+-------------------------------------------------------------------------------------------------------------------+
(output truncated for brevity)


After some investigation, I've discovered that I can un-stick the system by killing off a number of INSERT statements that appear to be in lock contention with the ALTER TABLE statement. When the system gets into the stuck state, the `performance_schema.metadata_locks` has data similar to the following:

mysql > SELECT OBJECT_TYPE, OBJECT_NAME, LOCK_TYPE, LOCK_DURATION, LOCK_STATUS, PROCESSLIST_STATE, PROCESSLIST_INFO FROM performance_schema.metadata_locks ml LEFT JOIN performance_schema.threads t ON ml.owner_thread_id = t.thread_id ORDER BY lock_type DESC;
+-------------+----------------------------+---------------------+---------------+-------------+---------------------------------+-------------------------------------------------------------------------------------------------------------------+
| OBJECT_TYPE | OBJECT_NAME | LOCK_TYPE | LOCK_DURATION | LOCK_STATUS | PROCESSLIST_STATE | PROCESSLIST_INFO |
+-------------+----------------------------+---------------------+---------------+-------------+---------------------------------+-------------------------------------------------------------------------------------------------------------------+
| TABLE | heart_rate | SHARED_WRITE | TRANSACTION | GRANTED | Waiting for table metadata lock | INSERT INTO heart_rate (id, person_id, timestamp, value) VALUES ... |
| TABLE | heart_rate | SHARED_WRITE | TRANSACTION | GRANTED | Waiting for table metadata lock | INSERT INTO heart_rate (id, person_id, timestamp, value) VALUES ... |
| TABLE | heart_rate | SHARED_WRITE | TRANSACTION | GRANTED | Waiting for table metadata lock | INSERT INTO heart_rate (id, person_id, timestamp, value) VALUES ... |
| TABLE | person | SHARED_UPGRADABLE | STATEMENT | GRANTED | Waiting for table metadata lock | ALTER TABLE health_target ADD CONSTRAINT fk_health_target_person_id FOREIGN KEY (person_id) REFERENCES person(id) |
| TABLE | person | SHARED_READ_ONLY | STATEMENT | GRANTED | Waiting for table metadata lock | ALTER TABLE health_target ADD CONSTRAINT fk_health_target_person_id FOREIGN KEY (person_id) REFERENCES person(id) |
| TABLE | person | SHARED_READ | TRANSACTION | PENDING | Waiting for table metadata lock | SELECT person.id, person.name, ... |
| TABLE | person | SHARED_READ | TRANSACTION | PENDING | Waiting for table metadata lock | INSERT INTO heart_rate (id, person_id, timestamp, value) VALUES ... |
| TABLE | person | SHARED_READ | TRANSACTION | PENDING | Waiting for table metadata lock | SELECT person.id, person.name, ... |
| TABLE | person | SHARED_READ | TRANSACTION | PENDING | Waiting for table metadata lock | SELECT person.id, person.name, ... |
| TABLE | person | SHARED_READ | TRANSACTION | PENDING | Waiting for table metadata lock | SELECT person.id, person.name, ... |
| TABLE | person | SHARED_READ | TRANSACTION | PENDING | Waiting for table metadata lock | INSERT INTO heart_rate (id, person_id, timestamp, value) VALUES ... |
| TABLE | person | SHARED_READ | TRANSACTION | PENDING | Waiting for table metadata lock | INSERT INTO heart_rate (id, person_id, timestamp, value) VALUES ... |
| GLOBAL | NULL | INTENTION_EXCLUSIVE | STATEMENT | GRANTED | Waiting for table metadata lock | INSERT INTO heart_rate (id, person_id, timestamp, value) VALUES ... |
| GLOBAL | NULL | INTENTION_EXCLUSIVE | STATEMENT | GRANTED | Waiting for table metadata lock | INSERT INTO heart_rate (id, person_id, timestamp, value) VALUES ... |
| TABLESPACE | db/health_target | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED | Waiting for table metadata lock | ALTER TABLE health_target ADD CONSTRAINT fk_health_target_person_id FOREIGN KEY (person_id) REFERENCES person(id) |
| SCHEMA | NULL | INTENTION_EXCLUSIVE | STATEMENT | GRANTED | Waiting for table metadata lock | ALTER TABLE health_target ADD CONSTRAINT fk_health_target_person_id FOREIGN KEY (person_id) REFERENCES person(id) |
| GLOBAL | NULL | INTENTION_EXCLUSIVE | STATEMENT | GRANTED | Waiting for table metadata lock | INSERT INTO heart_rate (id, person_id, timestamp, value) VALUES ... |
| GLOBAL | NULL | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED | Waiting for table metadata lock | ALTER TABLE health_target ADD CONSTRAINT fk_health_target_person_id FOREIGN KEY (person_id) REFERENCES person(id) |
| GLOBAL | NULL | INTENTION_EXCLUSIVE | STATEMENT | GRANTED | Waiting for table metadata lock | ALTER TABLE health_target ADD CONSTRAINT fk_health_target_person_id FOREIGN KEY (person_id) REFERENCES person(id) |
| BACKUP LOCK | NULL | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED | Waiting for table metadata lock | ALTER TABLE health_target ADD CONSTRAINT fk_health_target_person_id FOREIGN KEY (person_id) REFERENCES person(id) |
| SCHEMA | NULL | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED | Waiting for table metadata lock | ALTER TABLE health_target ADD CONSTRAINT fk_health_target_person_id FOREIGN KEY (person_id) REFERENCES person(id) |
| TABLE | #sql-7_e71d | EXCLUSIVE | STATEMENT | GRANTED | Waiting for table metadata lock | ALTER TABLE health_target ADD CONSTRAINT fk_health_target_person_id FOREIGN KEY (person_id) REFERENCES person(id) |
| FOREIGN KEY | fk_health_target_person_id | EXCLUSIVE | STATEMENT | GRANTED | Waiting for table metadata lock | ALTER TABLE health_target ADD CONSTRAINT fk_health_target_person_id FOREIGN KEY (person_id) REFERENCES person(id) |
| TABLESPACE | db/#sql-7_e71d | EXCLUSIVE | TRANSACTION | GRANTED | Waiting for table metadata lock | ALTER TABLE health_target ADD CONSTRAINT fk_health_target_person_id FOREIGN KEY (person_id) REFERENCES person(id) |
| TABLE | health_target | EXCLUSIVE | TRANSACTION | GRANTED | Waiting for table metadata lock | ALTER TABLE health_target ADD CONSTRAINT fk_health_target_person_id FOREIGN KEY (person_id) REFERENCES person(id) |
| TABLE | person | EXCLUSIVE | STATEMENT | PENDING | Waiting for table metadata lock | ALTER TABLE health_target ADD CONSTRAINT fk_health_target_person_id FOREIGN KEY (person_id) REFERENCES person(id) |
+-------------+----------------------------+---------------------+---------------+-------------+---------------------------------+-------------------------------------------------------------------------------------------------------------------+
(output truncated for brevity)


If I issue a `kill <PROCESS_ID>` command for each of the entries in the above table marked as LOCK_TYPE=SHARED_WRITE and LOCK_STATUS=GRANTED (which are trying to INSERT into the `heart_rate` table), the ALTER TABLE command completes immediately and the system unlocks itself. Alternatively, killing the process for the ALTER TABLE statement will unlock the system as well, but that doesn't help with adding the foreign key.


After running some experiments to reproduce this scenario and taking frequent dumps of the process list (I can provide samples if useful), it appears that:
- Some INSERT statements on the `heart_rate` table that were running when the ALTER TABLE was initially issued complete successfully before the system gets stuck
- Other INSERT statements on the `heart_rate` table that were running when the ALTER TABLE was initially issued end up competing for a lock on the `person` table and must be killed in order for the ALTER TABLE to complete
- INSERT statements on the `heart_rate` table that are issued after the system gets stuck complete successfully once the system is un-stuck


Based on what I've observed, I would guess that I might be encountering an issue somewhere in the lock acquisition handling for the ALTER TABLE and INSERT statements. It seems like there is a brief period where a write lock on the `heart_rate` table is being granted to one or more INSERT statements, which may include some sort of implicit lock on the `person` table due to the foreign key that is also being granted (I'm guessing at this part). The ALTER TABLE statement is subsequently attempting to acquire an exclusive metadata lock on the `person` table, which it needs in order to create the new foreign key, but it's unable to acquire this lock because the INSERT statements are blocking it. Meanwhile, the INSERT statements are unable to move forward because they are blocked by the ALTER TABLE's acquisition of the metadata lock on the `person` table, so the system gets stuck. There's some nuance here in that the `person` table that appears to be causing the lock contention isn't directly involved in the ALTER TABLE or the INSERT statements; rather, it's referenced by foreign keys on each of the tables in those statements.


I'm not sure if this is relevant, but I managed to capture a process list that indicated the ALTER TABLE statement initially had a STATE of "After create" shortly after the statement was issued. Maybe the INSERT statements that are issued during this initial stage are granted write locks, and then get deadlocked once the ALTER TABLE tries to acquire a metadata lock?


I've tried dumping the contents of `SHOW ENGINE INNODB STATUS;`, and there's no mention of a deadlock being detected in there. It has indication that the ALTER TABLE statement is attempting to acquire a lock, but no mention of a deadlock.


Similar ALTER TABLE statements were working just fine using MySQL 5.7; we upgraded to 8.0 and then to 8.4, and have started encountering this issue. This seems like it might align with the metadata locking on both tables involved in a foreign key that was introduced in MySQL 8.0.3.


While there is some form of workaround here, it involves data loss (as valid INSERT statements need to be killed), so it's not particularly viable. It also seems problematic that the database would effectively deadlock itself on a table without identifying it as a deadlock, so I'm tempted to call this a bug, but maybe there's something else going on here. I'd be curious on hearing any thoughts or feedback on this issue, and can submit a bug report if appropriate.